question

sakuraime avatar image
0 Votes"
sakuraime asked ErlandSommarskog answered

SQL Server stats usage stats

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

sql-server-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered Cathyji-msft edited

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

 ;



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.