How to diagnose slow reporting

Question

How to diagnose slow reporting

webrunner

SSC-Dedicated

Points: 31866
More actions
January 6, 2009 at 7:59 am

#204711

Hello,
I'm seeing a problem where a report that should run in a few minutes is taking anywhere from 45 minutes to several hours to run.
I know that I've stated the problem very generally, but I'm interested in what kind of troubleshooting steps a SQL expert takes when trying to find and fix the cause of such a problem.
One specific note: the database used for the report is copied and restored from a backup of the live database.
Thanks for any help,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

Viewing 5 posts - 1 through 4 (of 4 total)

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

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 1

I would run a server side trace (which can be generated through the Profiler tool, but shouldn't be run through it) to capture the calls against the database. Once I have those, I would examine the execution plan of the longest running query (or the most frequently called). From there... it depends on what you find.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

One thing I quite obviously found is that even if the query runs in seconds, it can take minutes... hours to render the report.

It's quite easy to spot, if you run the same query on the same server through ssms and runs as fast as expected, and lots faster than the report, then you have a rendering, or reporting server or network issue.

webrunner SSC-Dedicated Points: 31866 More actions · Answer 3

Grant Fritchey (1/6/2009)
I would run a server side trace (which can be generated through the Profiler tool, but shouldn't be run through it) to capture the calls against the database. Once I have those, I would examine the execution plan of the longest running query (or the most frequently called). From there... it depends on what you find.

Thanks! That's a great idea. I've heard about server-side traces but did not have the presence of mind to consider using one here. I really appreciate your advice.

Sincerely,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

webrunner SSC-Dedicated Points: 31866 More actions · Answer 4

Ninja's_RGR'us (1/6/2009)
One thing I quite obviously found is that even if the query runs in seconds, it can take minutes... hours to render the report.
It's quite easy to spot, if you run the same query on the same server through ssms, then you have a rendering, or reporting server or network issue.

Thanks! I did consider the possibility that the rendering (via the web server) is taking up the time. In fact, it's likely that the report is going to take 5-10 minutes even if any possible database-side issues are resolved. I will use try to set up a server-side trace to narrow down what, if any, queries are taking a long time. Perhaps it's a combination of one or two long queries and the rendering side.

Thanks again,

webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html