Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

Something like sys.dm_db_index_usage_stats would be helpful. Is there a DMV like this which counts statistics usage instead of index usage?

share|improve this question
    
I should mention that we have disabled the AUTOCREATE STATISTICS option in our production environments. –  Jacob Jul 5 '12 at 21:31
    
    
Yes, those are the links I found yesterday by following the link in Aaron's answer. –  Jacob Jul 6 '12 at 13:43
add comment

1 Answer

I don't know of any exposed or documented way that SQL Server keeps track of this as a separate statistic (no pun intended). But if you monitor your server for expensive queries (long duration, high CPU, high I/O etc) and collect the plans for those queries, you may be able to derive some of that information from the plans you've collected - but keep in mind that not all statistics will be represented (some details on that here). If you know of queries off-hand that are slow and you suspect the problem may be due to specific statistics or missing statistics, you can generate an actual execution plan for that query and inspect the plan - you'll often find evidence such as a missing statistics warning or a huge discrepancy between estimated and actual rows (and the latter is why generating an estimated plan only is kind of worthless).

With the disclaimer that I work for SQL Sentry, our Performance Advisor product is one way to help with the collection of high-impact queries (we call them Top SQL) and the execution plans associated with them at runtime, since we collect those and keep them for you over time. Also our Plan Explorer is a free tool that can generate plans or open existing plans you've collected, often highlighting some of these issues much easier than viewing the graphical showplan in Management Studio or parsing the XML yourself.

share|improve this answer
    
I'll give an example of the kind of thing we've experienced. In a production system with an ad-hoc statistics entity, a certain query ran quickly and successfully. The execution plan indicates it performed a SEEK on index X. When we dropped the statistics, the same query failed. The execution plan changed dramtically; it now performs a SCAN. As it turns out that the join logic in the query is joining int to varchar, and doing implicit conversion on the varchar. We now recognize this as a risk, and we don't want to make more changes without full understanding. –  Jacob Jul 5 '12 at 21:34
    
The link you provided has a link to IMO a better post on how to use traceflags to see the statistics being used. Thank you. –  Jacob Jul 5 '12 at 21:43
    
Btw I'm a heavy user of the SQL Sentry Plan Explorer. Thank you for the freebie. –  Jacob Jul 5 '12 at 21:44
    
@Jacob Paul's post is quite useful, but it requires that you are able to add those options to the query and run them manually. You might choose to run the queries at a time where a certain plan is chosen, but that is not the plan that causes issues (as plan choices can change over time due to various factors), or that the change to the query text itself leads to a different plan altogether when it is compiled (since it is now a different query). –  Aaron Bertrand Jul 6 '12 at 1:04
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.