4

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?

Improve this question
3

1 Answer 1

Reset to default
1

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).

SentryOne Performance Advisor product is one way to help with the collection of high-impact queries ("Top SQL") and the execution plans associated with them at runtime, since they are collected and saved for you over time. Also 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.*

Improve this answer
4
  • 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, 2012 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, 2012 at 21:43
  • Btw I'm a heavy user of the SQL Sentry Plan Explorer. Thank you for the freebie.
    – Jacob
    Jul 5, 2012 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, 2012 at 1:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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