September 22, 2010 at 5:05 pm
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
September 22, 2010 at 11:25 pm
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
September 23, 2010 at 1:54 am
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;-)
September 23, 2010 at 11:37 am
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?
September 23, 2010 at 10:43 pm
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;-)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply