Are there any dmv which I can look for the to WA* (auto create stats) usage ??
Are there any dmv which I can look for the to WA* (auto create stats) usage ??
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.
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%'
;
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.
15 people are following this question.