1034
Part VII: Performance Tuning and Optimization
Creating Base Indexes
Even before tuning, the locations of a few key indexes are easy to determine. These indexes
are the fi rst step in building a solid set index foundation. Following are a few things to
keep in mind when building these base indexes:■ As a rule of thumb, plan to create a clustered index for every table. There are a few
exceptions to this rule, but as a general plan cluster your tables. In many cases, it
makes sense to create the clustered index on the primary key of the table.■ (^) Plan to create nonclustered indexes for each column belonging to a foreign key
constraint. When data is entered that must adhere to a foreign key constraint, SQL
Server must verify that the new values conform to the constraint. Nonclustered
indexes prove critical to accomplish this lookup.
■ (^) Review the queries that you know will be executed often. This is where your rela-
tionship with the application developers is crucial. Everyone wins if the database
supporting the application is indexed appropriately when the application rolls out.
Although this indexing plan is far from perfect, and it’s defi nitely not a fi nal indexing plan,
it provides an initial compromise between no indexes and tuned indexes and can be a base-
line performance measurement to compare against future index tuning.
Additional tuning can likely involve creating composite indexes and removing unnecessary
indexes.
Best Practice
When planning indexes, there’s a fi ne line between serving the needs of select queries versus update
queries. Although an index may improve query performance, there’s a performance cost because
when a row is inserted, updated, or deleted, the indexes must be updated as well. Nonetheless, some
indexing is necessary for write operations. The update or delete operation must locate the row prior
to performing the write operation, and useful indexes facilitate locating that row, thereby speeding
up write operations.
Therefore, when planning indexes, include the fewest number of indexes to accomplish the job.
SQL Server exposes index usage statistics via dynamic management views. Specifi cally, sys.dm_dbindex
operational_stats and sys.dm_db_index_usage_stats uncover information about how indexes are
used. In addition, four dynamic management views reveal indexes that the Query Optimizer looked for, but didn’t fi nd:
sys.dm_db_missing_index_groups, sys.dm_db_missing_index_group_stats, sys.dmdb
missing_index_columns, and sys.dm_db_missing_index_details.
c45.indd 1034c45.indd 1034 7/31/2012 10:16:44 AM7/31/2012 10:16:44 AM
http://www.it-ebooks.info