- Indexes 101
- Indexes for Various Database Application Types
- The Size of Index Keys
- Determining Proper Indexes
- Index Tuning Toolbox
- Miscellaneous
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Determining Proper Indexes
It's nice to know about the indexing strategies, but how do you go about selecting a column on which to build an index? The answer is fairly simple: Look at your queries. If 80% of your queries have a particular column in the WHERE clause, build a clustered index on that column. If you have a 50/50 split among several columns, get with users and determine which query is the most important. If one query is taking 2 minutes and the other one 10 seconds, the first one might be more important to optimize. On the other hand, if the second query is constantly executed by 75% of all users and the first one is executed only once a day, the second query should be given a higher priority.
In addition to checking the WHERE clauses, be sure to examine the joins. SQL Server can often take advantage of indexes while joining two tables. This is especially true if both tables included in the join have indexes that are ordered the same way. If this happens, SQL Server can use the MERGE join algorithm, which can be much faster than the LOOP or HASH join. The join algorithm is beyond the scope of this article, but keep in mind that SQL Server makes the best decision on which algorithm to use 99% of the time. Occasionally you can get a slight improvement by overriding the chosen join algorithm by providing the join hint. We'll discuss join hints further in the next article.
Sometimes you might have all the appropriate indexes, but SQL Server can't take advantage of them because of the way your queries are written. If this is the case, Transact-SQL code needs to be altered so that SQL Server can use indexes effectively. We'll cover such issues in the code optimization article.