How to find the needle in the haystack

Question

Post reply

How to find the needle in the haystack

Viewing 15 posts - 16 through 30 (of 30 total)

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

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 1

tcronin 95651

SSCertifiable

Points: 6735

April 9, 2015 at 6:54 am

#1789841

no more than at any other time, still looking

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 2

May have found the needle, started going through the snapshots Idera DM saves, as I scrolled through I noticed there is one stored procedure with about 12 occurances that was killing the server, the CPU ms for the session was over 100K, if I kept scrolling all the other connections were just fine. Today that same stored proc runs without issue. Almost like sql server somehow lost the roadmap to process this stored proc

Grant Fritchey SSC Guru Points: 398911 More actions · Answer 3

tcronin 95651 (4/9/2015)
May have found the needle, started going through the snapshots Idera DM saves, as I scrolled through I noticed there is one stored procedure with about 12 occurances that was killing the server, the CPU ms for the session was over 100K, if I kept scrolling all the other connections were just fine. Today that same stored proc runs without issue. Almost like sql server somehow lost the roadmap to process this stored proc

Bad parameters sniffing? Capture the execution plan while it's running good. Next time it runs poorly, capture it again. Compare the two.

"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

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 4

Found one more clue, the locks/sec almost tripled right at 8:30, thank goodness for Idera's snapshot with alerts

Eirikur Eiriksson SSC Guru Points: 182918 More actions · Answer 5

Grant Fritchey (4/9/2015)
tcronin 95651 (4/9/2015)
May have found the needle, started going through the snapshots Idera DM saves, as I scrolled through I noticed there is one stored procedure with about 12 occurances that was killing the server, the CPU ms for the session was over 100K, if I kept scrolling all the other connections were just fine. Today that same stored proc runs without issue. Almost like sql server somehow lost the roadmap to process this stored proc
Bad parameters sniffing? Capture the execution plan while it's running good. Next time it runs poorly, capture it again. Compare the two.

Either parameter sniffing or blocking, sounds even more like the latter to me. Can be tricky to detect unless one knows what to look for, how are the signal waits when this occurs?

😎

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 6

no blocking that was the first thing I looked for. I had dropped an unused index the night before on a table that the stored proc that ran wild utilized. But after the restart I looked at the query plan it is using and it looks fine.

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 7

time to get my eyes checked the lock reuests went to 50 Million not 500 K Not even sure how you can get to that number.

Eirikur Eiriksson SSC Guru Points: 182918 More actions · Answer 8

tcronin 95651 (4/9/2015)
no blocking that was the first thing I looked for. I had dropped an unused index the night before on a table that the stored proc that ran wild utilized. But after the restart I looked at the query plan it is using and it looks fine.

This is starting/already becoming like a Trivial Pursuit game;-), as much fun as it is I suggest you collect your baseline date, any variances including recompilation triggers such as changing/dropping indexes and work through those, we can only keep guessing when only piecemeal facts are provided.

😎

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 9

I have baselines, just stunned to see this happen, I have been a dba for 20 years and 99% of the time this happens either blocking or someone decided to run a cartesan join query. Hope to never see again

Eirikur Eiriksson SSC Guru Points: 182918 More actions · Answer 10

tcronin 95651 (4/9/2015)
I have baselines, just stunned to see this happen, I have been a dba for 20 years and 99% of the time this happens either blocking or someone decided to run a cartesan join query. Hope to never see again

I know what you're saying, see this almost every day unfortunately, my thought is that one can never build too much on experience as just like trying to make things fool proof will only produce better fools:-D

😎

Jeff Moden SSC Guru Points: 1004433 More actions · Answer 11

Eirikur Eiriksson (4/9/2015)
tcronin 95651 (4/9/2015)
I have baselines, just stunned to see this happen, I have been a dba for 20 years and 99% of the time this happens either blocking or someone decided to run a cartesan join query. Hope to never see again
I know what you're saying, see this almost every day unfortunately, my thought is that one can never build too much on experience as just like trying to make things fool proof will only produce better fools:-D
😎

My Dad has a notable quote. "If you make something idiot proof, only idiots will use it." 😛

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

Eirikur Eiriksson SSC Guru Points: 182918 More actions · Answer 12

Jeff Moden (4/9/2015)
Eirikur Eiriksson (4/9/2015)
tcronin 95651 (4/9/2015)
I have baselines, just stunned to see this happen, I have been a dba for 20 years and 99% of the time this happens either blocking or someone decided to run a cartesan join query. Hope to never see again
I know what you're saying, see this almost every day unfortunately, my thought is that one can never build too much on experience as just like trying to make things fool proof will only produce better fools:-D
😎
My Dad has a notable quote. "If you make something idiot proof, only idiots will use it." 😛

Cannot comment, still grasping for air:exclamation:

😎

Jeff Moden SSC Guru Points: 1004433 More actions · Answer 13