Sudden performance hit

Question

Sudden performance hit

C.K.Shaiju

SSCarpal Tunnel

Points: 4228
More actions
March 8, 2016 at 9:36 am

#311969

Hi All,
I am facing a performance issue on one of my SQL job. Job execution was completing by 40-45 minutes daily, but from the past 2 weeks it is taking more than 140 minutes to complete the process. Below table showing the execution duration in minutes, can see the change 21st Feb onwards.
There was no update on server
There was no update on database configuration
ExecDateTimeExecDurationInMinutes
3/6/2016------- 141
3/4/2016------- 143
3/3/2016------- 145
3/2/2016------- 143
3/1/2016------- 142
2/29/2016------- 141
2/28/2016------- 139
2/26/2016------- 138
2/25/2016------- 137
2/24/2016------- 138
2/23/2016------- 143
2/22/2016------- 138
2/21/2016------- 155
2/19/2016------- 46
2/18/2016------- 45
2/17/2016------- 48
2/16/2016------- 44
2/15/2016------- 45
How do I proceed? Where do I check?
Regards,
Shaiju
_____________________________________________
One ounce of practice is more important than tonnes of dreams

Viewing 11 posts - 1 through 10 (of 10 total)

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

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

With no details on what the job does, the best thing would be to capture the wait statistics before and after the job runs. You can then compare the two in order to understand what's happening. You might also want to set up a blocked process report in extended events to capture blocking issues. Another option is to capture query execution metrics using extended events for the period in question. It's impossible to suggest what to do about the issue without understanding where things are running slowly. Gather data and let us know.

"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

C.K.Shaiju SSCarpal Tunnel Points: 4228 More actions · Answer 2

Ok. Thank you very much. Let me try these.

_____________________________________________
One ounce of practice is more important than tonnes of dreams

C.K.Shaiju SSCarpal Tunnel Points: 4228 More actions · Answer 3

Grant Fritchey (3/8/2016)
With no details on what the job does, the best thing would be to capture the wait statistics before and after the job runs. You can then compare the two in order to understand what's happening. You might also want to set up a blocked process report in extended events to capture blocking issues. Another option is to capture query execution metrics using extended events for the period in question. It's impossible to suggest what to do about the issue without understanding where things are running slowly. Gather data and let us know.

Hi,

I got solved the issue before start doing these steps.

Issue was one of the disk failure in SAN. Once they replaced it, the performance back to normal.

Thank you very much.

Regards,

Shaiju

_____________________________________________
One ounce of practice is more important than tonnes of dreams

PretendDBA SSCrazy Points: 2060 More actions · Answer 4

Wow, in a modern SAN that is a significant performance hit from 1 failed disk.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

PretendDBA (3/17/2016)
Wow, in a modern SAN that is a significant performance hit from 1 failed disk.

Worse than that, there was a bad disk that slowed the system down to 1/3rd of normal for more than 2 weeks and the "SAN people" either didn't catch it or they didn't communicate it. Someone didn't earn their pay.

--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)

C.K.Shaiju SSCarpal Tunnel Points: 4228 More actions · Answer 6

Jeff Moden (3/17/2016)
PretendDBA (3/17/2016)
Wow, in a modern SAN that is a significant performance hit from 1 failed disk.
Worse than that, there was a bad disk that slowed the system down to 1/3rd of normal for more than 2 weeks and the "SAN people" either didn't catch it or they didn't communicate it. Someone didn't earn their pay.

Exactly.

If the performance hit showing totally on all places, we might have thought something different.

But here the issue on a specific process and we concentrated only on that which is happening on SQL Server and blamed the sql query.

_____________________________________________
One ounce of practice is more important than tonnes of dreams

R.P.Rozema SSChampion Points: 12317 More actions · Answer 7

C.K.Shaiju (4/4/2016)
Jeff Moden (3/17/2016)
PretendDBA (3/17/2016)
Wow, in a modern SAN that is a significant performance hit from 1 failed disk.
Worse than that, there was a bad disk that slowed the system down to 1/3rd of normal for more than 2 weeks and the "SAN people" either didn't catch it or they didn't communicate it. Someone didn't earn their pay.
Exactly.
If the performance hit showing totally on all places, we might have thought something different.
But here the issue on a specific process and we concentrated only on that which is happening on SQL Server and blamed the sql query.

No, that's not the point being made. "They" should have monitoring in place on the operation of the SAN. At the very least, the SAN sends out emails when something goes wrong. "They" should take immediate action upon such mails. In your case "They" did a poor job...

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

PretendDBA SSCrazy Points: 2060 More actions · Answer 8

For one or two drives to cause the significant of a performance hit I'd suggest your SAN needs some sort of firmware upgrade. One or two disk failures should go largely unnoticed. Yes there should also be proactive monitoring reporting issues to the admins and to the vendor.

C.K.Shaiju SSCarpal Tunnel Points: 4228 More actions · Answer 9

R.P.Rozema (4/5/2016)
No, that's not the point being made. "They" should have monitoring in place on the operation of the SAN. At the very least, the SAN sends out emails when something goes wrong. "They" should take immediate action upon such mails. In your case "They" did a poor job...

Ok. Yes, they did not get the information on time.

PretendDBA (4/5/2016)
For one or two drives to cause the significant of a performance hit I'd suggest your SAN needs some sort of firmware upgrade. One or two disk failures should go largely unnoticed. Yes there should also be proactive monitoring reporting issues to the admins and to the vendor.

Sure.

_____________________________________________
One ounce of practice is more important than tonnes of dreams

C.K.Shaiju SSCarpal Tunnel Points: 4228 More actions · Answer 10

R.P.Rozema (4/5/2016)
No, that's not the point being made. "They" should have monitoring in place on the operation of the SAN. At the very least, the SAN sends out emails when something goes wrong. "They" should take immediate action upon such mails. In your case "They" did a poor job...

Ok. Yes, they did not get the information on time.

PretendDBA (4/5/2016)
For one or two drives to cause the significant of a performance hit I'd suggest your SAN needs some sort of firmware upgrade. One or two disk failures should go largely unnoticed. Yes there should also be proactive monitoring reporting issues to the admins and to the vendor.

Sure.

_____________________________________________
One ounce of practice is more important than tonnes of dreams