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


Anonymous 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.

for ict 99 said...

IEEE Final Year projects Project Centers in Chennai are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes, while specialists like the enjoyment in interfering with innovation. For experts, it's an alternate ball game through and through. Smaller than expected IEEE Final Year project centers ground for all fragments of CSE & IT engineers hoping to assemble. Final Year Project Domains for IT It gives you tips and rules that is progressively critical to consider while choosing any final year project point.

JavaScript Training in Chennai

JavaScript Training in Chennai

mahil said...

It is a very helpful data. It will help to improve my knowledge about this topic. Thank you for this awesome post.
Corporate Training in Chennai
Corporate Training Companies in Chennai
Soft Skills Training in Chennai
JMeter Training in Chennai
Pega Training in Chennai
Appium Training in Chennai
Advanced Excel Training in Chennai
Oracle Training in Chennai
Social Media Marketing Courses in Chennai
Tableau Training in Chennai
Power BI Training in Chennai