SSRS Reports URL - Performance issue on 64 bit with SQL Server 2005
Something I recently came across on an Dynamics AX 2009 instance, was a SSRS reports URL, performance issue.
This is also referred to as the reports manager URL, it's the http://server/reports URL.
What I saw was that the reports URL would be working fine, and then after some time of being idle, the worker process that runs that URL in IIS 7.0 would of course get destroyed to free that RAM up.
On start back up, of that worker process, the reports URL would enver return. Closer details show, this is a thread error taking place.
After much search, and working with the Microsoft SSRS Performance Development group, a solution was found. We had to modify the machine.config, found in the .Net 2.0 Framework, to set the min. thread workers.
After seeing this work, turns out this is now a know bug by the Microsoft SSRS development group, with performance. This has only been seen on SQL Server 2005 64-bit Edt. running on Windows Server 2003 or 2008 64 bit edt. with *greater* than 4 processors. (4 processors or lower this does not show up.)
Turns out the code, for the SSRS service handles this fine, the processor affinity masking. However the code for the reports URL, does not.
So if you run into this, there is a fix, and Microsoft should be coming up with a fix for this in SQL Server 2005 SP4 and I am sure a hot fix before then.
This has not been seen in SQL Server 2008 code base.
Well check back later, more great post to come!
"Visit the Dynamics AX Community Page today!"
This is also referred to as the reports manager URL, it's the http://server/reports URL.
What I saw was that the reports URL would be working fine, and then after some time of being idle, the worker process that runs that URL in IIS 7.0 would of course get destroyed to free that RAM up.
On start back up, of that worker process, the reports URL would enver return. Closer details show, this is a thread error taking place.
After much search, and working with the Microsoft SSRS Performance Development group, a solution was found. We had to modify the machine.config, found in the .Net 2.0 Framework, to set the min. thread workers.
After seeing this work, turns out this is now a know bug by the Microsoft SSRS development group, with performance. This has only been seen on SQL Server 2005 64-bit Edt. running on Windows Server 2003 or 2008 64 bit edt. with *greater* than 4 processors. (4 processors or lower this does not show up.)
Turns out the code, for the SSRS service handles this fine, the processor affinity masking. However the code for the reports URL, does not.
So if you run into this, there is a fix, and Microsoft should be coming up with a fix for this in SQL Server 2005 SP4 and I am sure a hot fix before then.
This has not been seen in SQL Server 2008 code base.
Well check back later, more great post to come!
"Visit the Dynamics AX Community Page today!"
Labels: 64 bit, Microsoft, Performance, Performance Issuses, Report Manager, Reporting, SQL Server 2005, SSRS, Threads, Worker Process, x64
posted by brandon | 4:37 PM
3 Comments:
Is this a SQL 2005 only problem or is 2008 affected also?
Grant,
This has only been seen in SQL Server 2005 x64 bit.
thanks,
Brandon
Please note that SQL Server 2005 SP4 is not yet tested or officially supported for Dynamics AX 2009. When considering upgrade of SQL Server 2009 database to SQL server 2005 SP4 for more up-to-date supportability information visit the Partner and Customer source sites or check with your Microsoft contact.
Many Thanks,
Nelly
Post a Comment
<< Home