SQL Server stats usage stats

sakuraime 2,321 Reputation points
2021-09-21T04:25:22.74+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,760 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 40,901 Reputation points
    2021-09-21T05:14:32.507+00:00

    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.

    0 comments
  2. CathyJi-MSFT 21,096 Reputation points Microsoft Vendor
    2021-09-21T08:20:40.203+00:00

    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%'  

;
    0 comments
  3. Erland Sommarskog 101.4K Reputation points MVP
    2021-09-21T21:56:35.187+00:00

    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.

    0 comments