ronner


Over the past week and a half we started experiencing a sporadic slowdown in our production x64 SQL 2005 Ent. Edition server. Users started complaining of slowness then they started getting timeouts. In looking at sp_who2 and perfmon we saw the following during the slow/frozen periods:
* Dramatic increase in Perfmon Active Transactions

* CPU higher than norm, but not dramatically so

* sp_who2 shows a number of spids in SUSPENDED state (and not running waits)

* no blocking indicated from sp_who2

* active connections slowly increasing

* no disk queuing (or at most some spikes to 1)

After a couple of minutes of this we would then see the following:
* no more spids in SUSPENDED state

* Logins per second spikes dramatically

* Active transactions spikes down to "normal levels"

* CPU goes high then levels out at moderately higher than normal

* active connections slowly decreases back towards normal levels

* large spike in lock wait time

We turned on the Async Auto Update Statistics option (after testing in our staging environment) on the primary database about a week before we saw this problem. By turning it off we can visually see the problem go away by watching the above metrics. So my question is, What metrics can I use to see the "blocking" or resouce locking that is causing these problems

Anyone

Thx

Ron





Re: Auto Update Statistics Asynch and sporadic blocking of throughput

ronner


did some further checking. Anyone have experience with the Profiler's Performance>AutoStats event I am playing with that in our staging environment. Seems that that may give some indication as to when stats are auto updated and how long they took. However, still no indicator as to what is blocked during that process. Anyone







Re: Auto Update Statistics Asynch and sporadic blocking of throughput

Ajmer Dhariwal

You need to track the AutoStats event to confirm if this is what is causing your blocking, as this will indicate when an auto update of stats is taking place.

You need to also make sure you're also tracking the Errors and Warnings>Blocked Process Report event to pick up blocking, and that your 'blocked process threshold' value is set to a a delay interval that you consider long enough, e.g. 5 (seconds): http://msdn2.microsoft.com/en-us/library/ms191168.aspx






Re: Auto Update Statistics Asynch and sporadic blocking of throughput

ronner

Thanks Ajmer,

I am going to test these shortly.

Ron