So we've recently upgraded our environment to SQL Server 2014 (Enterprise on CU5) and are having some problems diagnosing performance issues, compounded by the problem that we no longer seem to be able to tell when SQL Server is updating/creating statistics.
Previously, when compiling a plan, if SQL Server needed additional statistics (we have autostats on) we could see in
STATMAN was busily working away doing his thing. Now all we get is a blank
sql_text column, no query plan and a huge number of reads and only by looking at the locks do we see that there are some statistics somewhere being generated (but not which columns on what table).
Does anyone have any insight into the changes here? Is there any way to tell what stats the optimizer has asked for? I know we can once the plan is compiled (thank you Paul White) but we have a fairly large data warehouse, so sometimes the stats collection operation takes a significant amount of time!
It looks like while
sp_whoisactive no longer shows you the stats being created/updated, you can use a dmv to find out what's going on with async stats:
FROM sys.dm_exec_background_job_queue bq
JOIN sys.stats ss ON ss.object_id = bq.object_id1
AND ss.stats_id = bq.object_id2
JOIN sys.stats_columns stc ON stc.object_id = ss.object_id
AND stc.stats_id = ss.stats_id
JOIN sys.columns sc ON sc.column_id = stc.column_id
AND sc.object_id = stc.object_id
WHERE in_progress = 1
The way to kill the background jobs is via the
KILL STATS JOB command to terminate the stats process.