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.

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?

share|improve this question
1  
There are suggested indexes but these are very limited and should still be evaluated by someone who knows what they are doing. Do you have a specific question about indexing? –  JNK Jun 7 '12 at 13:02
1  
Is there a query/table you're having poor performance on? As JNK implied you should really only add indexes when you know what you're doing (and test it regardless). Unless you have reason to believe a table needs an index (huge table, slow queries, an obvious clustered index) you shouldn't just try and add them –  Ben Brocka Jun 7 '12 at 13:07

2 Answers 2

up vote 6 down vote accepted

Yes, there are tools, like the database engine tuning advisor. It ships with SQL Server and does some pretty nasty things:

  • It allows you to consider only a very small workload (even a single query). Which will then suggest indexes that may help that small workload only, with no regard whatsoever to the rest of the workload (indexes help speed up some queries, but they can slow down others, especially insert/update/delete), never mind the rest of the business cycle.
  • It will often recommend redundant indexes that only differ by a trailing column or by an included column.
  • It really, really, really likes included columns - I think it may be a fetish.

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

share|improve this answer

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.

Link for DTA Tutorial

It is also worth noting that there are built-in DMVs for gathering that type of information:

share|improve this answer

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.