November 19, 2012 at 9:50 am
I am using following DMV quries find out missing index details
SET TRANSACTION ISOLATION LEVEL
READ UNCOMMITTED
SELECT TOP 2
ROUND(s.avg_total_user_cost *
s.avg_user_impact
* (s.user_seeks + s.user_scans),0)
AS [Total Cost]
,d.[statement] AS [Table Name]
,equality_columns
,inequality_columns
,included_columns
FROM sys.dm_db_missing_index_groups g
INNER JOIN sys.dm_db_missing_index_group_stats s
ON s.group_handle = g.index_group_handle
INNER JOIN sys.dm_db_missing_index_details d
ON d.index_handle = g.index_handle
ORDER BY [Total Cost] DESC
I got following results
Total Cost Table Name Equality_colums Inequality_columns included_column
26034622[IEDB].[dbo].[StudentMaster] [CID] NULL [BatchID]
12537364[IEDB].[dbo].[ExamApplication] [BatchId], [ExamScheduleID]NULL [Appno], [RegNo]
My question is how to apply index above result set?
what is equality column ? what is included column?
how to apply yhe index give me example?
November 19, 2012 at 9:52 am
Equality and inequality columns go in the index key (equality first, then inequality). Included columns go in the index's include columns.
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
November 19, 2012 at 9:59 am
Please give me a example
November 19, 2012 at 10:01 am
http://msdn.microsoft.com/en-us/library/ms345405%28v=sql.105%29.aspx
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply