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.
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.
16 people are following this question.