X hits on this document





31 / 48


  • Fragmented indexes can slow down query performance since more I/O operations are required in order to return results for a query. Keep index fragmentation to a minimum by rebuilding and/or reorganizing your indexes on a scheduled or as-needed basis.

  • Select clustered index keys that are rarely modified, highly unique, and narrow in data type width. Width is particularly important because each nonclustered index also contains within it the clustered index key. Clustered indexes are useful when applied to columns used in range queries. This includes queries that use the operators BETWEEN, >, >=, <, and <=. Clustered index keys also help reduce execution time for queries that return large result sets or depend heavily on ORDER BY and GROUP BY clauses. With all these factors in mind, remember that you can only have a single clustered index for your table, so choose carefully.

  • Nonclustered indexes are ideal for small or one-row result sets. Again, columns should be chosen based on their use in a query, specifically in the JOIN or WHERE clause. Nonclustered indexes should be made on columns containing highly unique data. As discussed in Chapter 5, don’t forget to consider using covering queries and the new SQL Server 2005 INCLUDE func- tionality for non-key columns.

  • Use a 100% fill factor for those indexes that are located within read-only filegroups or data- bases. This reduces I/O and can improve query performance because less data pages are required to fulfill a query’s result set.

  • Try to anticipate which indexes will be needed based on the queries you perform—but also don’t be afraid to make frequent use of the Database Engine Tuning Advisor tool. Using the Database Engine Tuning Advisor, SQL Server can evaluate your query or batch of queries and determine what indexes could be added (or removed) in order to help the query run faster. I’ll demonstrate this later on.

The next recipe will now demonstrate how to display index fragmentation.

Displaying Index Fragmentation

In SQL Server 2000, the DBCC SHOWCONTIG command was used to display index fragmentation. Fragmentation is the natural byproduct of data modifications to a table. When data is updated in the database, the logical order of indexes (based on the index key) gets out of sync with the actual physical order of the data pages. As data pages become further and further out of order, more I/O operations are required in order to return results requested by a query. Rebuilding or reorganizing an index allows you to defragment the index by synchronizing the logical index order, re-ordering the physical data pages to match the logical index order.

  • Note See Chapter 5 for a review of index management and Chapter 23 for a review of index defragmentation

and reorganization.

Now in SQL Server 2005, DBCC SHOWCONTIG has been deprecated in place of the new dynamic management function, sys.dm_db_index_physical_stats. The sys.dm_db_index_physical_stats dynamic management function returns information that allows you to determine an index’s level of fragmentation.

T h e s y n t a x f o r s y s . d m _ d b _ i n d e x _ p h y s i c a l _ s t a t s i s a s f o l l o w s :

sys.dm_db_index_physical_stats ( { database_id | NULL } , { object_id | NULL } , { index_id | NULL | 0 } , { partition_number | NULL }


Document info
Document views82
Page views82
Page last viewedThu Oct 27 09:00:35 UTC 2016