My enterprise has a farm of many nearly-identical, isolated production environments. Each environment has a SQL Server 2005 database server, each supporting a database with nearly identical schemas. Ideally each database would have the same schema. Enhancements to the schema would be managed centrally, in a model schema. The schema changes would be coordinated with rollout of new versions of the client software.
Unfortunately, as time has passed, sysadmins have added ad-hoc statistics entities to some of these databases, and as a result, we see inconsistent performance among our production systems.
We would like to either incorporate the ad-hoc statistics into the model schema, or rid them from the production servers. However we cannot confidently do either unless we know what effect they are having on query performance.
It seems that there should be a way to see what the Query Optimizer is doing, in terms of accessing statistics entities as it evaluates different execution plans. Is this information available anywhere?
Is there a SQLTrace option which will report it?
sys.dm_db_index_usage_stats would be helpful. Is there a DMV like this which counts statistics usage instead of index usage?