Ok to rely on SQL Server tuning advisor for generating Indexes?

Question

I've been having it analyze my queries and pushing the recommendations for some time now. Are there any potential pitfalls with doing this? Would I be better of creating my own indexes by hand?

Answer 1

The tuning wizard does a good job for many queries, but there are still plenty of failure modes. Nothing beats a human brain to evaluate the best query plan and the indices required to achieve it. If there's a hot spot, the tuning wizard often turns out to be useless in my experience.

Answer 2

It's not easy to answer, the tuning advisor can get you close to what you probably want using a very scientific approach to the index creation - but it is very scientific about what it thinks it needs and can not make the jumps of optimisation that you can from practise.

Index creation and performance tuning is somewhat of an art as well as a science, so whilst it gives you that scientific start of where the issues are, I would never implement them without first reviewing every suggestion and checking whether it really was what I wanted, or whether I could come up with a better / more suitable approach.

Answer 3

Should one blindly trust the DTA advise and implement all recommendations without considering the implications? No. Should one ignore the DTA and just analyze everything from scratch? No. DTA (or whatever it's later incarnation is called) it's a tool. Like any other tool, is how you use it...

The tunning advisor will give advice based on the workload you present to it. As a general rule, the DTA advice is good quality and solid advice. DTA cannot advise on other types of workload, that was not captured for the analysis replay. It cannot advise on the implications of adding extra indexes on the storage capacity and long term retention policies. It cannot detect stupid queries, it will give advise to improve the access path of those stupid queries but an inteligent human analysis might give advise to improve the query syntax or redefine the requirements.

For someone immersed in a project for months and working with the project data schema and queries every day, the DTA should not reveal any surprise. So it may be said that is a superflous tool in that case. But I'd say that it should still be used exactly to validate that it does not give any stelar new advice.

Answer 4

Even if you run the advisor, if you are putting your database objects into source control, you will want to write scripts to create the indexes not just have them created for you. I need the scripts for source control and deployment as we do not allow any other way for database changes to be deployed to production. While you can script them after the change on dev, why bother when the create index code is so simple?