asked
SSRS Reports running slow in sql server 2005
Hello, We have few of our SSRS reports running very slow. These SSRS reports fetch data from a reporting server database which is acting as subscriber for the replication process. i.e. we have an OLTP DB server where Database 'A' is replicating on reporting sever as Database 'B' from where the SSRS reports fetch their data. The reports are running very slow when we try executing their SQL query manually. I am new to reading executions plans, so as highlighted its pointing clustered index seek as high operating cost, which I am not sure on how to look upon: Also, the DB is 350 GB in size and sql server is 2005, SP4. Moreover, I made sure my reindexing and update stats jobs are running properly and yes they are.(Using OLA's Index script). Please refer to below execution plans for the SQL query executed from one of the SSRS reports. ![alt text][1] ![alt text][2] [1]: /storage/temp/2261-screen1.jpg [2]: /storage/temp/2262-untitled.png Kindly help or provide any inputs that may help in resolving this slowness! Thanks!
Hi MAXKA Looking at the information provided, the estimates and actuals are very different e.g estimated executions 15298 actual 9311176. I'd suggest that you could start by updating the statistics on the tables and see if that helps. There will generally be somewhere in a plan that appears as the bottle neck and there will generally be something that has a high number, that doesn't mean it's bad - the plan has to come to 100% after all. How long did it used to take and how long does it take now? Mart
Would you mind checking the stats on the index in question to confirm they are very recent - sometimes, depending on the stats update script used, updates are missed. Have you got any monitoring to see what's what with the query? Has anything else changed between the 3 minute and 15-20 minute times? What are the parameters that are stored in the plan, and what are the parameters passed to the procedure?
I don't think the estimated rows is actually as bad as it first looks. I think the plan is saying it expects 15298 rows for each unique value in the other side of the join (there are 3968 rows coming into that join - if each row has a different value then that gives an expect of around 60 million rows). There's an article by Gail Shaw here that explains this:
http://sqlinthewild.co.za/index.php/2009/09/22/estimated-rows-actual-rows-and-execution-count/comment-page-1/ My question would be can you limit the data at all - there's 90 million records getting processed - that's a lot of data to be dealing with.
93K rows through a nested loops join and index seeks? That's a horror show. In fact, I wouldn't be at all surprised to find that you have query hints forcing those choices. It may be statistics related as @Mart and @mjharper have suggested. But it might be the query.
@Grant, thanks! I am wondering if stats play the role here. I found that We have the update stats job( from OLA script) scheduled to run sat morning. However when we ran to check last updated stats that show last date of 21st only within job run time. even though auto update stats was set to true which was false earlier. Do that mean it did not set to true and stats are not getting updated?
If the auto_update stats are set to true, then they're set to true. It can't be half on or anything. And yes, this could be stats, as everyone has already said. Use DBCC SHOW_STATISTICS to see when the statistics were last updated on that table. You can also compare the number of rows in the table in the statistics to the actual number of rows in the table and the number of rows sampled to get an idea of when the stats were updated how many rows there were compared to how many you have now and whether or not it's a sampled or full scan.
You can also use the gui in ssms to check the stats info - expand the stat's node (in the table) and double click to see what it says at the bottom 'Update Statistics...' If the stats have never been updated then you can try updating them there but check afterwards as it doesn't always work - if it doesn't then you can rebuild the index to get the stats to update too (be careful when you do this though for obvious reasons). Hopefully when you have better stats then you'll also have a better plan which should hopefully help with index seeks and nested loops issue @GrantFritchey mentions.
16 People are following this question.
