How can I learn SQL Server index tuning? [closed]

Question

What is the best practical way of learning index tuning while writing tsql queries? I have VS2008 SQL Express. Could someone please provide me examples, etc? I have already found online articles and they are great in theory, but I still fail to see index tuning in real life action. Are there small easy to create examples out there?

Answer 1

To tune indexes, you tend to need large tables with lots of data, so small simple examples aren't easy to come by.

My experience is with the SQL 2000 tools. Query Analyser, showing the Execution Plan and looking at the types of index and joins used. Very hard to describe it here.

I can recommend a good book on the subject, particularly Chapter 9.

http://www.amazon.com/Professional-Server-Performance-Tuning-Programmer/dp/0470176393

I would discourage you from using the automated Index Tuning tools until you understand how to do it yourself manually. I think it's important when it recommends adding an index that you have the ability to sanity-check the recommendation and decide for yourself whether it is a good option. Often it will recommend you add a "covering" index with many columns in order to speed up a single query you've asked to be analysed, but this may have adverse effects on your database overall when you look at all queries against that table.