SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Slow running query - only off peak


Slow running query - only off peak

Author
Message
sharky
sharky
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1114 Visits: 618
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,

Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)

Group: General Forum Members
Points: 350880 Visits: 34261
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

The Scary DBA
Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software
sharky
sharky
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1114 Visits: 618
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
Grant Fritchey
SSC Guru
SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)

Group: General Forum Members
Points: 350880 Visits: 34261
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

The Scary DBA
Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software
sharky
sharky
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1114 Visits: 618
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
Grant Fritchey
SSC Guru
SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)SSC Guru (350K reputation)

Group: General Forum Members
Points: 350880 Visits: 34261
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

The Scary DBA
Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
Product Evangelist for Red Gate Software
Arsh
Arsh
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4933 Visits: 993
Did you check for any disk stalls ? I mean io_pendings on the disk .

Best Regards..Arshad

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search