SQL Server Performance Forum – Threads Archive
missing index
how to find out missing index on tableI didn’t get you what you are asking….
But ….check the following… SQL Server 2005 Books Online
sys.dm_db_missing_index_columns
http://msdn2.microsoft.com/en-us/library/ms345364.aspx http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS†with no warranties for accuracy.
DMVs are very helpful in SQL 2005 and Mohammed’s reference is right on track. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
Here’s a nice one already done up for you! The following select shows you the indexes SQL 2005 server (portfolio) thinks could use some index tuning. The fourth column from the left shows us what impact the user can expect to have – not always 100% right on, but it gives some idea. The second column really tells us usage.
select
unique_compiles,
User_seeks,User_scans,
avg_user_impact,
equality_columns,
inequality_columns,
included_columns,
[statement],
last_user_seek,
avg_total_user_cost
from
sys.dm_db_missing_index_details d
inner join
sys.dm_db_missing_index_groups g ON
d.index_handle = g.index_handle
inner join
sys.dm_db_missing_index_group_stats s ON
g.index_group_handle = s.group_handle
order by
left (avg_user_impact,1) desc,
user_seeks desc,
unique_compiles desc; Michael
MCDBA "The fear of the Lord is the beginning of knowledge,
but fools despise wisdom and instruction." Proverbs 1:7
Hi,
when I ran below query I got the result now my question is, How should I interpret the result as in the result I’m having equality_columns, inequality_columns, included_columns which represents the column names where the indexes are missing on a given table but on which column I should I create the index….
select * from sys.dm_db_missing_index_details Rohit
This article may help http://msdn.microsoft.com/en-us/magazine/cc135978.aspx
May be I should have referenced this http://sqlserver-qa.net/blogs/perft…sing-indexes-and-identify-useful-indexes.aspx before.
]]>