Hello, 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 declines only when report services get involved. Is there anything I can do to script SSRS setup properties or is there anything else I can do to compare? Any ideas? Thanks in advance! Lana

Hi Lana, We had kind of same issue yesterday, when we run dataset query against SSMS it executes in 8-10 secods but when report is running through Report Server it was taking 10-12 minutes. After trouble shooting down we came up that report server is using data-set query like this:- exec sp_executesql N'--HERE IS THE DATASET QUERY' After googling down we came up that it is not good to use "EXEC sp_executesql" SP but it is Reporting Services uses as his own. Finally to get this down we add option hint something like to executes within seconds:- option (hash join, hash group) Please run your query against DTA you can try optimizing queries appropriately. Thanks KumarKG, MCTS

Thank you for your quick reply Kumar! I will try out your solution and will let you know how it goes. Thanks, Lana