BPSoftware.com Blog

Monday, November 10, 2008

SQL Optimization: Am I missing any indexes? - Part I

Lately, I have been doing a lot with SQL (2005) optimization. Database design plays an important role in the efficiency of a database. A key part of the design is the use of indexes. Indexes significantly affect the performance of a database whether it is during a SELECT, INSERT, UPDATE and DELETE Transact-SQL (DML) statement. The biggest gain from an index is when a user SELECTs data from a database. The designer of the database and application should have done their best to come up with indexes that accommodate how the database will be used. How can one be sure they’re not missing any indexes?

SQL Server 2005 has a feature that keeps track of Missing Index Information. This information is contained in the sys.dm_db_missing_index_groups, sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_details tables. The missing index information in these tables can be used to determine indexes that would improve database performance based upon its usage. The following query returns the indexes that would improve database performance. Before creating the indexes review the usage values to determine if the creation of the index is really necessary.

1 /* missing indexes*/
2 use [master]
3 SELECT IG.INDEX_GROUP_HANDLE,
4 ID.INDEX_HANDLE,
5 ID.DATABASE_ID,
6 DB_NAME(ID.DATABASE_ID) AS DATABASE_NAME,
7 ID.[OBJECT_ID],
8 OBJECT_NAME(ID.[OBJECT_ID],ID.DATABASE_ID) as TABLE_NAME,
9 ID.STATEMENT AS FULL_TABLE_NAME,
10 ID.EQUALITY_COLUMNS,
11 ID.INEQUALITY_COLUMNS,
12 ID.INCLUDED_COLUMNS,
13 GS.USER_SEEKS,
14 GS.USER_SCANS,
15 GS.LAST_USER_SEEK,
16 GS.LAST_USER_SCAN,
17 GS.AVG_TOTAL_USER_COST,
18 GS.AVG_USER_IMPACT,
19 GS.SYSTEM_SEEKS,
20 GS.SYSTEM_SCANS,
21 GS.LAST_SYSTEM_SEEK,
22 GS.LAST_SYSTEM_SCAN,
23 GS.AVG_TOTAL_SYSTEM_COST
24 FROM SYS.DM_DB_MISSING_INDEX_DETAILS AS ID
25 INNER JOIN SYS.DM_DB_MISSING_INDEX_GROUPS AS IG
26 ON ID.INDEX_HANDLE = IG.INDEX_HANDLE
27 INNER JOIN SYS.DM_DB_MISSING_INDEX_GROUP_STATS AS GS
28 ON GS.GROUP_HANDLE = IG.INDEX_GROUP_HANDLE
29 ORDER BY ((GS.AVG_TOTAL_USER_COST * GS.AVG_USER_IMPACT) * (GS.USER_SEEKS + GS.USER_SCANS)) DESC
30
31

Using the above query result you could create the necessary index (the field names are listed where their values should be used):

1 CREATE NONCLUSTERED INDEX <unique index name>
2 ON <FULL_TABLE_NAME> (<EQUALITY_COLUMNS>,<INEQUALITY_COLUMNS>) -- exclude INEQUALITY_COLUMNS if NULL
3 INCLUDE (<INCLUDED_COLUMNS>); -- exclude INCLUDED_COLUMNS if NULL

The information in these tables is dynamic and When SQL Server is restarted, all of the missing index information is dropped from them. The index information is collected over time; therefore it is better to query this information when the server has been ‘used for a period of time’.

Labels: Code, How-To, SQL

posted by Brad Prendergast at 6:36:00 AM

Comments: