Slow running query - only off peak

Question

Slow running query - only off peak

sharky

Default port

Points: 1494
More actions
September 13, 2018 at 7:28 am

#408447

Hi All,
I have an an issue with a query in a scheduled job running every ten minutes around the clock.
Sometimes, around twice a month it suddenly shoots up from the normal 2 minutes to about about 70 - 90 mins mins. After that it goes back to the normal 2 mins.
The funny thing is it always occurs at a non peak time (between 1:00 and 7:00) without any other scheduled jobs or peak trade and all the resource usage(CPU,IO,Blocking) is non existent ( although the logical reads of one of the tables shoots up dramatically to billions).
I also can not see any really waits that jumps out(CXPACKET waits is high though) , and also does not pass any parameters(Initially I thought it might be a parameter sniffing issue). This is a query that normally runs less than a minute for 99% of the time! but only when everything is quiet sometimes misbehaves? Does not make sense
Maybe I am missing something simple, and just some new suggestions, or any ideas of XE's to catch?
Thanks for the help,

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

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

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

My first thought would be blocking by another resource, so I'd focus there. However, it's possible that a recompile event leads to differences in the execution plan. Capture it when it's slow as well as when it's fast and compare the two. Parameters aren't the only way to get differences in a plan. Hard coded values in batch commands use specific statistics like parameters. Local variables are sniffed just like parameters in a recompile. Any of these could lead to a difference in the plan.

"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

sharky Default port Points: 1494 More actions · Answer 2

Hi Grant,

Thanks for this. Think blocking is the most probable, and have set up an XE to catch something next time..

We are using Redgate and a quick question about the counters:

a) The avg. lock time briefly shoots up when the query starts, but then flatlines for the duration of execution. Can this be expected behaviour when there is massive blocking?
b) Will logical reads also shoots up dramatically when blocking occurs?

Thanks,

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

sharky - Friday, September 14, 2018 3:33 AM
Hi Grant,
Thanks for this. Think blocking is the most probable, and have set up an XE to catch something next time..
We are using Redgate and a quick question about the counters:
a) The avg. lock time briefly shoots up when the query starts, but then flatlines for the duration of execution. Can this be expected behaviour when there is massive blocking?
b) Will logical reads also shoots up dramatically when blocking occurs?
Thanks,

a) I would expect to see lock time go up, yeah. No locking at all (or little locking) suggests you might be looking at simple resource contention. What's happening with the waits?
b) That's an "it depends" situation. Maybe, maybe not, completely situational. In fact, the more likely scenario is that reads drop, but it's just all around what's actually happening.

"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

sharky Default port Points: 1494 More actions · Answer 4

Thanks Grant,

Thanks for the help.

On thing that jumps out on the waits is LCK_M_SCH_S with with a average wait of 119 secs. I have noticed that the Redgate tool takes a lot of these waits though, without any performance issues so I might be barking up the wrong tree.
The highest is the CXPACKET waits. Even thought at one point it it might be a parallelism bug, but probably unlikely...No other waits really to take note of.
Might be some outside resource contention, but looking at the redgate tools everything looks fine (No CPU, IO, memory contention). In fact, these all goes down during the slow running query?

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

What version and service pack of SQL Server? I ask because CXPACKET waits have changed. They used to be noise. Now they're not. Here's an article from the Tiger Team that's not getting nearly enough attention.

Seeing everything go down during the slow running query is actually a sign of contention believe it or not. The blocking and/or waits that are occurring is preventing other work from getting through.

It's probably not the schema locks, and yeah, we're partly to blame for those, it's an artifact of monitoring.

"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

Arsh SSCertifiable Points: 6345 More actions · Answer 6

Did you check for any disk stalls ? I mean io_pendings on the disk .

Best Regards..Arshad