SQL Server stats usage stats

Question

Are there any dmv which I can look for the to WA***** (auto create stats) usage ??

Answer 1

What for a "usage" do you mean?
The database engine uses stats to determine the data spreading to decide for the best way to fetch the data.

Answer 2

Hi @sakuraime ,

Use the following query to identify statistics auto-created by SQL Server. And get information about the statistics.

SELECT sp.stats_id,   
       name,   
       filter_definition,   
       last_updated,   
       rows,   
       rows_sampled,   
       steps,   
       unfiltered_rows,   
       modification_counter  
FROM sys.stats AS stat  
     CROSS APPLY sys.dm_db_stats_properties(stat.object_id, stat.stats_id) AS sp  
WHERE stat.object_id = OBJECT_ID('HumanResources.Employee')  
and name like '_WA%'  

;  

Answer 3

No, there is no DMV to my knowing to track stats usage. However, I know that there is a trace flag, so that you can view which statistics that are used when compiling a specific query. I don't know the number by heart, but you may find it if you dig around in Paul White's blog posts.