Bill Vaughn, MS MVP and author of the popular Hitchhiker's Guide SQL Server books, provides a roundup of SQL Server indexing tips and tricks. The topics are based on guidance from Kimberly Tripp and Paul Randall, SQL Server high availability and performance experts.
Highlights from the list:
Tripp and Randall also call out the DTA, a.k.a. Data Engine Tuning Advisor, as a valuable tool. The DTA has been updated in SQL Server 2008 to provide new features, including Improved Workload Parsing, Enhanced Scalability, Multiple Database Tuning, Improved Scripting Support Through dta command line interface.
Highlights from the list:
- Page Density
Optimize Row Size: SQL Server 2005 (and later) supports 8K columns. This means a row can be, well, over 80K. Does this make sense? Ah, not usually. Managing row size is as important as ever to those interested in efficiency. The trick to good performance and good use of space is to make sure that when the 8K page is filled with rows, there is little wasted space. This means that if the row size is over (about) 4K, only one row will fit on a page and (about) 4K of space is wasted on the page. A secondary problem is that the number of index pages also has to increase to address the pages.
- Filtered Indexes
New for SQL Server 2008, permit you to add a WHERE clause to an index, thus focusing the index on the most important rows.
- Selectivity
Selectivity: When the query optimizer (QO) studies your SQL, the degree of selectivity determines if an index should be used to perform the operation. By processing the Statistics (or "stats") for an index (and an index can have from one to many (many) sets of stats), the QO can determine the selectivity. Basically, it's weighing the choice of using the index to walk through the selected rows or doing a table scan. The example Kimberly used made it pretty clear how it worked but we were surprised to learn: "When the number of rows (selected by the query) is around 1/4 of the number of data pages in the table, the index was not useful and it is more efficient to perform a table scan to fetch the rows selected. This often turns out to be less than 5% of the rows in the table..."
Tripp and Randall also call out the DTA, a.k.a. Data Engine Tuning Advisor, as a valuable tool. The DTA has been updated in SQL Server 2008 to provide new features, including Improved Workload Parsing, Enhanced Scalability, Multiple Database Tuning, Improved Scripting Support Through dta command line interface.