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.
13,131 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Olaf Helper 42,576 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 No comments

  2. CathyJi-MSFT 21,111 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 No comments

  3. Erland Sommarskog 104.3K 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 No comments