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:
Head blocker is a SPID (694) from a SOAP server.
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).
This is interesting – the first time I’ve seen an auto update stats operation have impact…
SELECT StatMan([SC0], [SB0000])
SELECT TOP 100 PERCENT [SC0],
step_direction([SC0]) over (order by NULL) AS [SB0000]
SELECT [i_api_id] AS [SC0]
FROM [dbo].[cpi_tracking_guid_3] TABLESAMPLE SYSTEM (1.891471e+000 PERCENT)
) AS _MS_UPDSTATS_TBL_HELPER
ORDER BY [SC0], [SB0000]
) AS _MS_UPDSTATS_TBL OPTION (MAXDOP 1)