Beware the impact of Auto Update Stats kicking in unexpectedly..

Interesting catch today with someone complaining of some API latency a week ago for a couple minutes.  IGNITE shows some 50 processes blocked at this time:

blocked sessions

Head blocker is a SPID (694) from a SOAP server.

head blocker

The SQL being executed by the SOAP server is actually an AUTO STATISTICS update kicking in….    By default we have auto update statistics enabled on all databases..   Looks like cpi_tracking_guid_3 hit some internal threshold that required the stats updating..     We disabled the daily update stats job but never disabled auto update of stats on the DBS (this happens when 20% of data changes in a table or a sproc recompile thinks it needs updated stats).

statman

This is interesting – the first time I’ve seen an auto update stats operation have impact…

 

 

 

SQL:

 

SELECT StatMan([SC0], [SB0000])

FROM

(

SELECT TOP 100 PERCENT [SC0],

step_direction([SC0]) over (order by NULL) AS [SB0000]

FROM

(

SELECT [i_api_id] AS [SC0]

FROM [dbo].[cpi_tracking_guid_3] TABLESAMPLE SYSTEM (1.891471e+000 PERCENT)

WITH

(

READUNCOMMITTED

)

) AS _MS_UPDSTATS_TBL_HELPER

ORDER BY [SC0], [SB0000]

) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 1)

Advertisements
Image | This entry was posted in SQL Server and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s