I want to create the best indexes for each table in my database. Is there a query or tool in SQL Server to help with this process?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
Yes, there are tools, like the database engine tuning advisor. It ships with SQL Server and does some pretty nasty things:
The proper approach to index tuning is to consider your entire workload over a complete business cycle. Sometimes it is better to turn to 3rd party tools than reinvent the wheel. DBSophic makes a free product called Qure Workload Analyzer that I think does a much better job than DTA - but their licensed tool (Qure Workload Optimizer) is absolutely phenomenal at what it does. Last year I blogged about how the tool works when integrated with our solution (SQL Sentry Performance Advisor for SQL Server) (disclaimer: I work for SQL Sentry).
There are some other options, too, for example missing indexes are reported in the DMVs, and you can also discover which indexes are being written to (so they are taking up space and affecting insert/update/delete operations) but never (or rarely) used by user queries:
But these also need the caveat that the DMVs are only valid since the last SQL Server restart, and that they won't necessarily cover a proper business cycle (and hence may be missing vital information
The tool you are talking about is called the Database Engine Tuning Advisor (DTA for short). It can take a number of inputs and give recommendations on things like missing indexes. It is worth noting, though, that these are just suggestions and careful consideration should be taken instead of blindly following DTA's recommendations.
It is also worth noting that there are built-in DMVs for gathering that type of information: