Wednesday, September 26, 2007

Are we missing any? (indexes)

One of the really greath things about SQL Server 2005 are the DM's - Dynamic Management views. These generally provide a good insight into the servers current state and can be used to monitor the health of the server as well as tune or diagnose any problems.

A key issue in tuning any database is tuning the indexes. Not only maintaining those that are already there, but also finding out if we are missing any, or if any existing ones are not being used.

Finding the missing indexes can be done by using a group of dm views, the sys.dm_db_missing_index_xx group. This group of dm views record information about index usage from the query optimizer. For each statement the optimizer generates an optimal plan, but if the indexes does not exist for this plan, it generates another less optimal plan for the statement. The missing index is then recorded in the missing_index_detail view.

If the optimal index for the statement is present the usage of this index is recorded in the sys.dm_db_index_usage stats which can the be used to remove used indexes.

An examble of the use of the missing_index_detail view:
Use AdventureWorks

Select Firstname, Lastname
from Person.Contact
Where Lastname='Smith'

Select *
from sys.dm_db_missing_index_details

OR if you want the result for the missing index, column by column

SELECT mig.*, statement AS table_name, column_id, column_name, column_usageFROM sys.dm_db_missing_index_details AS midCROSS APPLY sys.dm_db_missing_index_columns (mid.index_handle)INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handleORDER BY mig.index_group_handle, mig.index_handle, column_id

Please note that this example also proves the point that the missing_index views are limited in their recordings. In this examble it would be the most optimal solution also to include the Firstname column in the index. But the missing_index views has a number of limitations and should therefore be used as a guide towards the tables and columns that are missing indexes - not as a tool for fine tuning.

Further readings on subject

1 comment:

Komunitas Youtuber Indonesia said...

To do Not Pressure OR Anything, But Have Ever This considered post there is statement PT Lampung Service this is a
Service HP Bandar Lampung whose looking to do day
Service iPhone Lampung to this looking then to that is
Jasa Kursus Service HP I will try it.
Jasa Kursus Service HP They have jumping places and so that the device other kid's activity.Youtuber Lampung , Thanks ! Visit Back.