|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Missing index query helpprovided the query below to find missing indices. Results are below. select object_name(t1.object_id) TblName,t2.user_seeks,t1.equality_columns,t1.inequality_columns,t1.included_columns from sys.dm_db_missing_index_details t1,sys.dm_db_missing_index_group_stats t2, sys.dm_db_missing_index_groups t3 where database_id=db_id() and t1.index_handle=t3.index_handle and t2.group_handle=t3.index_group_handle and object_name(object_id) = 'tableA' order by 1 , 2 desc Output : TblName seeks Equality_cols Inequality_cols Included_cols TableA 3609843 [Col1] NULL [Col2] TableA 3434018 [Col2], [Col1] NULL NULL TableA 703743 [Col1] [Col3] [Col2] TableA 495032 [Col2], [Col1] [Col3] NULL So how do I create these indices now ? For the first entry, is it stating to create an index on col1 with Col2 as included col ? second entry, I guess it wants a covered index on Col2,Col1 For the 3rd and 4th entry I dont know what it wants us to create. The 3rd entry has a column listed for each of the 3 column types namely equality,inequality and included. Thanks Please help me figure this out. Hi Hassan,
See the BOL entry for sys.dm_db_missing_index_details: "To convert the information returned by sys.dm_db_missing_index_details into a CREATE INDEX statement, equality columns should be put before the inequality columns, and together they should make the key of the index. Included columns should be added to the CREATE INDEX statement using the INCLUDE clause." Hope this helps, Ben Nevarez Senior Database Administrator AIG SunAmerica Show quote "Hassan" wrote: > When I was going through Kalens Query Tuning and Optimization book , she > provided the query below to find missing indices. Results are below. > > select object_name(t1.object_id) > TblName,t2.user_seeks,t1.equality_columns,t1.inequality_columns,t1.included_columns > from > sys.dm_db_missing_index_details t1,sys.dm_db_missing_index_group_stats t2, > sys.dm_db_missing_index_groups t3 > where database_id=db_id() > and t1.index_handle=t3.index_handle > and t2.group_handle=t3.index_group_handle > and object_name(object_id) = 'tableA' > order by 1 , 2 desc > > Output : > > TblName seeks Equality_cols Inequality_cols Included_cols > > TableA 3609843 [Col1] NULL [Col2] > TableA 3434018 [Col2], [Col1] NULL NULL > TableA 703743 [Col1] [Col3] [Col2] > TableA 495032 [Col2], [Col1] [Col3] NULL > > So how do I create these indices now ? > > For the first entry, is it stating to create an index on col1 with Col2 as > included col ? > second entry, I guess it wants a covered index on Col2,Col1 > For the 3rd and 4th entry I dont know what it wants us to create. > > The 3rd entry has a column listed for each of the 3 column types namely > equality,inequality and included. > > Thanks > > Please help me figure this out. > > By the way, you can also use the Database Engine Tuning Advisor for
recommendations for indexes on your database. Hope this helps, Ben Nevarez Senior Database Administrator AIG SunAmerica Show quote "Ben Nevarez" wrote: > > Hi Hassan, > > See the BOL entry for sys.dm_db_missing_index_details: > > "To convert the information returned by sys.dm_db_missing_index_details into > a CREATE INDEX statement, equality columns should be put before the > inequality columns, and together they should make the key of the index. > Included columns should be added to the CREATE INDEX statement using the > INCLUDE clause." > > Hope this helps, > > Ben Nevarez > Senior Database Administrator > AIG SunAmerica > > > > "Hassan" wrote: > > > When I was going through Kalens Query Tuning and Optimization book , she > > provided the query below to find missing indices. Results are below. > > > > select object_name(t1.object_id) > > TblName,t2.user_seeks,t1.equality_columns,t1.inequality_columns,t1.included_columns > > from > > sys.dm_db_missing_index_details t1,sys.dm_db_missing_index_group_stats t2, > > sys.dm_db_missing_index_groups t3 > > where database_id=db_id() > > and t1.index_handle=t3.index_handle > > and t2.group_handle=t3.index_group_handle > > and object_name(object_id) = 'tableA' > > order by 1 , 2 desc > > > > Output : > > > > TblName seeks Equality_cols Inequality_cols Included_cols > > > > TableA 3609843 [Col1] NULL [Col2] > > TableA 3434018 [Col2], [Col1] NULL NULL > > TableA 703743 [Col1] [Col3] [Col2] > > TableA 495032 [Col2], [Col1] [Col3] NULL > > > > So how do I create these indices now ? > > > > For the first entry, is it stating to create an index on col1 with Col2 as > > included col ? > > second entry, I guess it wants a covered index on Col2,Col1 > > For the 3rd and 4th entry I dont know what it wants us to create. > > > > The 3rd entry has a column listed for each of the 3 column types namely > > equality,inequality and included. > > > > Thanks > > > > Please help me figure this out. > > > > Here is a query that can help do what you need. However, you need to test
the results. The column order may not be right so use your judgement. SELECT sys.objects.name, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) as Impact, 'CREATE INDEX YourName ON ' + sys.objects.name + ' ( ' + mid.equality_columns + CASE WHEN mid.inequality_columns IS NULL THEN '' ELSE CASE WHEN mid.equality_columns IS NULL THEN '' ELSE ',' END + mid.inequality_columns END + ' ) ' + CASE WHEN mid.included_columns IS NULL THEN '' ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';' AS CreateIndexStatement, mid.equality_columns, mid.inequality_columns, mid.included_columns FROM sys.dm_db_missing_index_group_stats AS migs INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle INNER JOIN sys.objects WITH (nolock) ON mid.object_id = sys.objects.object_id WHERE (migs.group_handle IN (SELECT TOP (5000) group_handle FROM sys.dm_db_missing_index_group_stats WITH (nolock) ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC)) and objectproperty(sys.objects.object_id, 'isusertable')=1 --and name = 'tblperson' ORDER BY 2 DESC -- Jason Massie Web: http://statisticsio.com RSS: http://statisticsio.com/Home/tabid/36/rssid/1/Default.aspx Show quote "Hassan" <has***@test.com> wrote in message news:e9KaGLMMIHA.748@TK2MSFTNGP04.phx.gbl... > When I was going through Kalens Query Tuning and Optimization book , she > provided the query below to find missing indices. Results are below. > > select object_name(t1.object_id) > TblName,t2.user_seeks,t1.equality_columns,t1.inequality_columns,t1.included_columns > from > sys.dm_db_missing_index_details t1,sys.dm_db_missing_index_group_stats t2, > sys.dm_db_missing_index_groups t3 > where database_id=db_id() > and t1.index_handle=t3.index_handle > and t2.group_handle=t3.index_group_handle > and object_name(object_id) = 'tableA' > order by 1 , 2 desc > > Output : > > TblName seeks Equality_cols Inequality_cols Included_cols > > TableA 3609843 [Col1] NULL [Col2] > TableA 3434018 [Col2], [Col1] NULL NULL > TableA 703743 [Col1] [Col3] [Col2] > TableA 495032 [Col2], [Col1] [Col3] NULL > > So how do I create these indices now ? > > For the first entry, is it stating to create an index on col1 with Col2 as > included col ? > second entry, I guess it wants a covered index on Col2,Col1 > For the 3rd and 4th entry I dont know what it wants us to create. > > The 3rd entry has a column listed for each of the 3 column types namely > equality,inequality and included. > > Thanks > > Please help me figure this out.  |
|||||||||||||||||||||||
Other interesting topics