Reports are very slow and fast?

Question

Post reply

Reports are very slow and fast?

SQL Galaxy

One Orange Chip

Points: 29472
More actions
February 26, 2014 at 11:37 pm

#304529

Hi,
Application side some times all the reports disaply very fast with in 2 seconds, and same reports very slow it tooks more than 30 seconds,
I have verified all statistics are up to date and index fragmentation are less 5 %, IO,memory and cpu under normal state. but why those reports are comes very slow some time? what is the forward path to troubleshoot again?
Thanks
ananda

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Is there a time of day that they run slow all the time?

Are the reports being run with different parameters when they run slow?

Do you have any jobs running at the same time?

Did any large jobs just complete prior to the reports running slow?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

SQLRNNR (2/26/2014)
Is there a time of day that they run slow all the time?
There is no fixed time period, whenever user tried generated reports time running fast & slow, I will ask again user side to noted down that what exact time for slow running reports.
Are the reports being run with different parameters when they run slow?
All the the reports are running same parameter, I noted some of the SP developer has mentioned SET isolation level read uncommited, I think that could be double reading data and slow performance.
Do you have any jobs running at the same time?
No Jobs are running at day time.
Did any large jobs just complete prior to the reports running slow?

There is no large jobs.

Thanks

ananda

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Are the reports in SSRS or some other app?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 4

SQL Galaxy

One Orange Chip

Points: 29472

February 27, 2014 at 12:29 am

#1692557

it is not SSRS, another application such as aspx.

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 5

It sounds like blocking and resource contention. You'd need to monitor the server to see which queries are the ones that are being slowed down and then determine what resources it is that they need that they can't get to. I'd start with capturing query metrics using extended events (or, since Jason is here, trace events).

"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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Grant Fritchey (2/27/2014)
It sounds like blocking and resource contention. You'd need to monitor the server to see which queries are the ones that are being slowed down and then determine what resources it is that they need that they can't get to. I'd start with capturing query metrics using extended events (or, since Jason is here, trace events).

😀

haha - give me the XE stuff:cool:

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

mikeg317X SSC-Addicted Points: 479 More actions · Answer 7

are multiple people running the report with the same parameters?

or in your testing are you running it once when it takes 30 seconds, and then the same parameters takes 2 seconds a while later?

it sounds like the query is getting cached