Besides knowing the types of queries that your users will run, you need to know the relative priority and frequency of the queries. If one query is run every time a customer calls for information about an account, that query might have higher priority than a query that generates a report overnight once a month. You might want to let the frequent or high-priority queries determine the indexing on a table.
Is the Data Changing?
If the answer to question 6 is that a table is mainly used for updating, you can look at questions 7 and 8 to refine the types of update operations you'll be performing. For tables used primarily for retrieval, questions 7 and 8 won't be much help. But if the answer to question 7 tells you that most of the rows will be updated all at once, you have a bulk operation. For bulk queries, you have to decide whether to have any indexes on the table while the batch operation is running. (See my August 2002 column, "Tame Bulky Inserts," InstantDoc ID 25521, for index considerations for bulk-modification operations.)
If the answer to question 7 tells you that only a small percentage of rows in a table will change, look at the search arguments in the relevant queries. Consider indexing the queried columns in the same way you would index search-argument columns for a SELECT query. Also, keep in mind that all data-modification operations can require some index maintenance. You can minimize that maintenance by finding the answer to question 8, determining which columns will be updated, and not indexing those columns. You can also minimize index maintenance by making sure that the updated columns aren't variable-length columns, which can increase in size during modification and cause the row to move to a new page. (You can find the data type of existing table data by using the sp_help command.)
My general recommendation for tables that are mainly used for data modifications—such as an orders table, in which inserts are occurring continuously—is to have as few indexes as possible. You want to have enough indexes to help you find the data rows to modify, but you want to limit the number of indexes because data modification causes index maintenance.
For tables that are mostly used for data retrieval, the number of nonclustered indexes you can have is limited only by the available disk space. Because data-retrieval indexes don't need maintenance, consider creating any index that can help.
If a table is used for both retrievals and data-modification operations, you need to find a balance between having enough indexes to find the data you need and having so many indexes that maintaining them becomes costly. However, rather than try to design a set of indexes that are good for both retrieval and modification operations, you'll probably have better overall performance if you choose either retrieval or modification and design your indexes as if that's the only type of operation you'll be doing.
If a table is used for both modification and retrieval, you need to be concerned with one other factor: data fragmentation. In a table that's used only for retrieval, fragmentation won't occur, and in a table that's used only for modification of a few rows at a time, fragmentation generally won't matter. But if you perform both kinds of operations, the modifications will create fragmentation, which then slows down any retrievals that return multiple rows in a range or in sorted sequence. In such a case, you'll need to rebuild or defragment your indexes regularly. (For more information about fragmentation and defragmenting indexes, see my article "Keep SQL Server Up and Running," December 2000, InstantDoc ID 15742.)
The answer to question 9, finding which columns are used for JOIN operations, will give you further possibilities for indexing because indexes on your join columns can often improve performance. However, indexing for JOIN operations is a complex topic, which I won't review here. You can take a look at my Inside SQL Server columns in March 2002 through May 2002, in which I discussed JOIN operations and the choice of indexes to support joins.
Heaps Need Not Apply
Reviewing your tables and queries to determine the answers to the nine questions I pose in this column will help you become intimately familiar with the kinds of queries your users execute—and will help you choose the right indexes for those operations. In general, I recommend that you give every table a clustered index unless you have a compelling reason not to. Remember that SQL Server uses the clustered index key as the bookmark in every nonclustered index's leaf-level rows, and thus having a clustered index is like having an extra column in the nonclustered index. For retrieval operations, you can easily end up with many more covered queries than you planned for. For INSERT and UPDATE operations, having a clustered index avoids the overhead involved with forwarding pointers, as I discussed in my July 2002 column ("Indexing Updates," InstantDoc ID 25198). Try to keep your clustering key as short as possible and place it on a static column or columns.
By now, you're aware that query tuning is a huge topic. I hope I've given you enough information about the way SQL Server organizes its data and chooses the indexes to use that you can start designing your own indexes. Alternatively, consider using the Index Tuning Wizard (ITW) to get initial suggestions about the best indexes, but don't take those recommendations as the final word. Use your own understanding of your applications and your data to fine-tune those suggestions after testing them thoroughly. The ITW doesn't consider multiuser concurrency and possible blocking problems, nor does it consider indexes on temporary tables, to name just a couple of its limitations. The more representative a sample of actual user queries you can supply as input, the better the ITW's recommendations will be. But again, don't take what the Tuning Wizard tells you as final because testing and tuning is a never-ending process.
End of Article
Prev. page
1
[2]
next page -->
