Check which statistics were used in query optimization

Posted on

Question :

Is it possible to tell when statistics were last accessed by a query using T-SQL? I’m trying to ascertain what statistics are involved in a query, I know I can check the visual query plan but I wondered whether there was a table (or tables) with this information.

Answer :

When using the original (70) cardinality estimation model, undocumented trace flags 9204 and 9292 can be used to show which statistics were loaded during query optimization, as detailed in my post How to Find the Statistics Used to Compile an Execution Plan.

You can also use undocumented trace flag 8666 as described by Fabiano Amorim in Statistics used in a cached query plan.

For the updated cardinality estimation model (120 and later), undocumented trace flag 2363 shows a lot of useful information, including which statistics were loaded.

New for SQL Server 2017, the statistics objects used to compile and optimize a query are included directly in the execution plan. See SQL Server 2017 Showplan enhancements by Pedro Lopes.

Leave a Reply

Your email address will not be published. Required fields are marked *