Report take a long time and time out

Question

Report take a long time and time out

monamym

Valued Member

Points: 53
More actions
February 29, 2016 at 11:09 pm

#316470

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

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jon-183221 SSCommitted Points: 1908 More actions · Answer 1

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?

monamym Valued Member Points: 53 More actions · Answer 2

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

Jon-183221 SSCommitted Points: 1908 More actions · Answer 3

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?

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

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

WHug Ten Centuries Points: 1248 More actions · Answer 5

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.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)