help interpreting missing index view

Question

help interpreting missing index view

ricardo_chicas

SSCertifiable

Points: 5024
More actions
September 22, 2010 at 5:05 pm

#157215

Hello all,
I ran this query:
SELECT sys.objects.name,
(avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) as Impact
, 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 = 'tEvent'
ORDER BY 2 DESC , 3 desc
and I get results like this
Case 1)
name impact equality_columns inequality_columns included_columns
table1 100000 columnA null null
In this case is simple, I need to create an index for columnA
Case 2)
name impact equality_columns inequality_columns included_columns
table1 100000 columnA null ColumnD
In this one index for columnA with inlcuded column D
Case 3)
name impact equality_columns inequality_columns included_columns
table1 100000 columnA columnB, columnC ColumnD
What do i need to do in this case?????
Thanks in advance

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

In this case, I'd test out an index (ColumnA, ColumnB, ColumnC, ColumnD) , see what improvements it makes to the queries.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

Study the execution plan before and after the index ( suggested by gail) and see what got improved ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

ricardo_chicas SSCertifiable Points: 5024 More actions · Answer 3

on a diferent db I ran that query and one of the results says this:

name_______impact_____________seeks_ scans_equalitycolumn_inequalitycolumn___included

table1______229918.581387738___2080___0____[pronumber]___NULL_____________NULL

but I do not have a column named pronumber on that table, any Idea of what it could means?

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

ricardo_chicas (9/23/2010)
on a diferent db I ran that query and one of the results says this:
name_______impact_____________seeks_ scans_equalitycolumn_inequalitycolumn___included
table1______229918.581387738___2080___0____[pronumber]___NULL_____________NULL

i am not aware of these things ..may be gail can comment on this ..but i still think you should post exec plan ( with .sqlplan extension )

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)