I have a SQL stored procedure that is a data source for the SSRS 2005 report. I use two servers with identical hardware, let’s call them Server1 and Server2.
When I run SP directly on the Server1(SQL 2005) and Server2(SQL 2005) within SQL Server Management Studio, SP runs for 10-11 seconds on each server. When I run my SSRS report that is using the procedure within BIDS or SharePoint sites (each server has its
own SharePoint site with supposedly identical setup), it runs for 20 seconds on Server1 SharePoint site and for about a minute on Server2 SharePoint site as wel as witin BIDS. There is no network traffic.
When I traced performance on both servers with SQL profiler while running report within BIDS or SharePoint, I didn’t notice anything unusual; it just takes
more time to process statements on Server2. I also tested several other reports and they all perform the same on both servers with maybe only a few seconds difference (Server1 is a bit faster). I suspect that SSRS for Server2 has a different setup as the performance
View Complete Post