4

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 sp_whoisactive that 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!

UPDATE 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:

SELECT  time_queued,
        OBJECT_NAME(object_id1),
        ss.name,
        sc.name,
        auto_created,
        user_created,
        no_recompute,
        has_filter,
        is_temporary,
        is_incremental
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.

Improve this question

1 Answer 1

Reset to default
2

I still see StatMan operations captured if I use SQL Sentry Plan Explorer PRO against SQL Server 2014:

enter image description here click to enlarge

So, not exactly sure what sp_whoisactive is doing differently in this case (I've never used it to analyze statistics creation), but I can assure you that SQL Server 2014 still uses StatMan.

Improve this answer
2
  • @Kristan This is hardly proof, but it seems the stats update is trigger by the query, and if you don't have async on then it has to create the statistic before it can continue running the query that uses it. I don't have the time right now to confirm with a larger data set, sorry. But that makes me wonder - perhaps you have changed your auto-stats setting between sync and async?
    – Aaron Bertrand
    Commented Mar 2, 2015 at 15:40
  • No worries, thanks for looking into it. Indeed async is not on, so the query needs to wait for the updated stats. With async on, the query proceeds and we get a new session, running as SA with a blank sql text (which is just got from sys.dm_exec_sql_text(), so nothing magic)..... Which you can't kill!
    – Kristan
    Commented Mar 2, 2015 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.