You create indexes. SQL Server creates statistics. So many of these objects get created to support your query. Stats help in estimations. Indexes help the engine to get to the data faster. But are these objects really being used? Well, for indexes, you can track their usage using DMVs like sys.dm_db_index_usage_stats, sys.dm_db_index_operational_stats, etc. But for stats objects, it isn’t so straightforward. You got to depend on a few trace flags like 9204, 9292, 2388, etc. These can tell you which STATS objects have been used by the optimizer for estimation purposes. Sounds interesting? Watch the video below.
Get new content directly in your inbox – Click here
You can also visit DataPlatformGeeks (DPG) Video Library to watch more videos and to download video resources (presentation & demo code).
Hope the video was worth your time.