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
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8299 Visits: 2610
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-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36024 Visits: 10285


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
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8299 Visits: 2610
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
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

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

psheperia
psheperia
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 27
It's worth looking at the index fragmentation. Bad indexes could be hampering query performance.
jay-h
jay-h
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8299 Visits: 2610
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 --
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