Missing index query help

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.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.include
d_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
"Ben Nevarez" wrote:
[vbcol=seagreen]
> 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 in
to
> 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:
>|||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/.../1/Default.aspx
"Hassan" <hassan@.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.include
d_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.