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


Very erratic performance in last few days.


Very erratic performance in last few days.

Author
Message
jay-h
jay-h
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15919 Visits: 2805
I apologize for such a general question, and there is not enough info for a detailed answer, but ANY general suggestions would be welcome.

We're running an SQL2008R2 instance on a VM with 12 cores and 32G ram. For years a number of nightly TSQL jobs have been run, each one consisting of multiple steps, each step (generally relatively small select queries and update steps) normally runs from a few mS to maybe 2 minutes. It has been in place for years.

This week, the jobs suddenly are not complete in the morning. What is happening is that only one or two of the many steps during the night unexpectedly take an extraordinarily long time to run (a step that might normally be a few seconds to a minute suddenly takes an hour or two).

* It's affecting DIFFERENT steps at DIFFERENT times during the night. Does not seem related to time, or to what kind of step is running.

* During the rest of the night all the other steps take normal times.

* There are no errors in the SQL logs or the Windows logs.

* Monitoring software (Idera diagnostic manager) shows normal levels of locks, wait states, disk access times, memory, CPU etc during these time periods--nothing that would appear to cause such extreme slowdown.

* After an affected step ends, the following steps occur at normal speed like nothing was ever wrong. For example a step that normally takes 50 seconds might suddenly take an hour, but the step before, and the step after seem perfectly normal.

* VM administrator has examined VM logs and sees no evidence of extended processor 'ready' states, memory or disk conflicts, or other VM issues. Since SQL and OS don't "see" such slowdowns, we had theorized that VM issues might be the case.

* Rerunning the job will often complete at a normal speed.

Not sure where to look next. Never saw an issue like this before.

...

-- FORTRAN manual for Xerox Computers --
Sue_H
Sue_H
SSC Guru
SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)SSC Guru (75K reputation)

Group: General Forum Members
Points: 75018 Visits: 15342


Did you check other guests using that host to see if they have experienced any issues around the same time that job has issues?
Did you check sysjobs to see if the job has been modified recently?
If possible, try changing the time the job runs.
Could you add a step at the beginning to dump wait stats to a table and then last step to capture them again so that you can see what the wait stats were during that time period.

Sue



jay-h
jay-h
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15919 Visits: 2805
Thank you for your input so far.

Yes I did check for job changes, and in each case it was a different query step that failed (even though this is regular, very consistent data). The monitor software provided logging of wait states and did not show anything odd.

Over the weekend, however, the server support admin updated patches and rebooted it off hours. So far today we've not seen any problems.

I'll hang on and watch.

...

-- FORTRAN manual for Xerox Computers --
Arsh
Arsh
SSCertifiable
SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)SSCertifiable (5.2K reputation)

Group: General Forum Members
Points: 5235 Visits: 1001
Did you look at ASYNC_NETWORK_IO wait type trend at time . Check this in conjunction with inputs from network team .

psheperia
psheperia
SSC Rookie
SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)SSC Rookie (33 reputation)

Group: General Forum Members
Points: 33 Visits: 42
It's worth looking at the index fragmentation. Bad indexes could be hampering query performance.
jay-h
jay-h
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15919 Visits: 2805
Thanks for additional suggestions.

We were monitoring wait states and saw no increase at the times of the slowdowns.

Indexing can slow things, but the statements involved were different each time, and the next time they ran things were fine. The failure points were different statements and at different times at night.

So far we've not seen this repeat since the full restart.

...

-- FORTRAN manual for Xerox Computers --
mig28mx
mig28mx
SSC-Addicted
SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)SSC-Addicted (492 reputation)

Group: General Forum Members
Points: 492 Visits: 269
Hi,
For the symptoms described, can be a bad cardinality issue or parameter sniffing issue.
Basically, you ended with a wrong execution plan that causes the unexpected delays in your jobs.
When you restart the instance, all the execution plans are wiped out and when the jobs run again, it creates a new one.

If there is a bad cardinallity issue, by updating the statistics will solve it.
If there is a parameter sniffing issue, removing the plan from memory, can mitigate the issue, but more research will be required.

Hope this helps.








































































































































.
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