How can I learn SQL Server index tuning?

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

If you have SQL Developer edition, you want to have a look at the Database engine tuning advisor.

If you use the profiler to capture a standard workload against your database, the DETA can recommend what statistics and indexes you could apply.

Beware though that tuning requires a good deal of thought, as adding new indexes may increase the speed of your workload, but you may increase the speed of an unimportant query at the detriment of a very important one.

There is a good book about TSQL Querying which has some very good advice about how queries work and how you can look at tuning them, which is MSSQL specific.

Answer 2

In SQL Server, if your database schema is a more simple one, you don't really need to tune much, since primary keys automatically result in a clustered index, and foreign key constraints require a unique index. So your joins are taken care of, usually.

Where is gets tricky is with searches and filters that the queries in the application use. You will have to specifically look at these queries and identify candidates for additional indices.

Another option is the SQL Server Tuning Advisor, but I discourage it's use, because it generates so much noise. You can use it to find the worst-offending queries, though.

Answer 3

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.

Answer 4

For a starting point of identifying your worst performing queries, please see my answer here.

Learning how to tune is something that requires practice. If you want to become skilled at it, start by buying Sajal Dam's book SQL Server Query Performance Tuning Distilled

Answer 5

Kimberly Trip (SQL Goddess) is an expert and has talked & written lots on the subject:

http://www.sqlskills.com/BLOGS/KIMBERLY/category/Indexes.aspx

Answer 6

Ken England's Microsoft SQL Server 2000 Performance Optimization and Tuning Handbook is a classic. (The Amazon reviews say the 2005 version of this book is not as good)