February 29, 2016 at 11:09 pm
Hi ,
I have a problem running a query reports , when i run it it take long time and there is no result back, but it works fine if i restore the 3 days back backup !
Could you tell me which parameters should i generally check /what/where should i check to diagnostic this problem ? and how can i check the difference between current DB and 3 days before ?
Thanks,
M
March 1, 2016 at 12:30 am
Can you provide any further information about the parameters in the report? Can you simplify the report to see if you can get it running and build it back up to try and identify the issue?
March 1, 2016 at 1:07 am
sorry i my mistake ,actually when i run the T-SQL query directly inside management studio ,i see time out , but it's okay for a backup from 3 days ago
March 1, 2016 at 1:43 am
Can you compare query plans across the two?
Can you simplify the TSQL to get it to work and then try and build back up until it causes the problem?
Can you check the indexe fragmentation on live as opposed the backup?
March 1, 2016 at 7:22 am
Presumably no one is hitting your 3-day-old restored backup when you execute against it. Thus one very likely issue is blocking. Use sp_whoisactive during execution on production to check for that.
You can also use that awesome piece of free code to check the query plan for badness. Compare production and 3-day-old plans.
Are you restoring on the same server? If not then the production server could be overwhelmed during execution.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 1, 2016 at 5:25 pm
From what I have seen, if the report has been fast (in SSRS) and now suddenly is slow (still in SSRS) then an index has become too fragmented or statistics are out of date.
In SSMS, select the source table and expand the tree, expand indexes, right-click the indexes and select properties. From there select the Fragmentation page. If the total fragmentation is high (based on your judgement) then you can rebuild the index. Do that by right-clicking the index and selecting rebuild. Consider that it will lock the table until completed.
I hope that helps.
March 1, 2016 at 8:15 pm
WHug (3/1/2016)
From what I have seen, if the report has been fast (in SSRS) and now suddenly is slow (still in SSRS) then an index has become too fragmented or statistics are out of date.In SSMS, select the source table and expand the tree, expand indexes, right-click the indexes and select properties. From there select the Fragmentation page. If the total fragmentation is high (based on your judgement) then you can rebuild the index. Do that by right-clicking the index and selecting rebuild. Consider that it will lock the table until completed.
I hope that helps.
I absolutely agree with the first step for stats but not necessarily rebuilding the index(es)... at least not yet. Hold that thought.
Other problems that could cause this type of thing are bad code reached the "tipping point", which changed the execution plan for the worse... or a bit of the bad kind of "parameter sniffing" has occurred... or someone made a change to the code or report... or someone changed/added/deleted an index... or something else reached the tipping point and drove everything the report uses out of memory... or... well, that's probably enough to get most folks started.
--Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply