August 29, 2012 at 12:49 am
1. First script
As below code, wirtten too much overlaping index whenever executing also impact application performance.
SELECT
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
object_name(dm_mid.object_id,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + object_name(dm_mid.object_id,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') +
CASE
WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO
output
Avg_Estimated_Impact
92.31
CREATE INDEX [IX_CSV_Details_Mark_Rev_No_fab] ON [SMTS].[dbo].[CSV_Details] ([Mark_Rev_No_fab])
Avg_Estimated_Impact
56.6
CREATE INDEX [IX_CSV_Details_MainFile_GA_Drg_NO_Rev_NO_Mark_No] ON [SMTS].[dbo].[CSV_Details_MainFile] ([GA_Drg_NO], [Rev_NO],[Mark_No])
2. Second script
Nothing recommendad to create any index, I got it from A Guide for the Accidental DBA Books.
SELECT migs.avg_total_user_cost * ( migs.avg_user_impact / 100.0 )
* ( migs.user_seeks + migs.user_scans ) AS improvement_measure ,
'CREATE INDEX [missing_index_'
+ CONVERT (VARCHAR, mig.index_group_handle) + '_'
+ CONVERT (VARCHAR, mid.index_handle) + '_'
+ LEFT(PARSENAME(mid.statement, 1), 32) + ']' + ' ON '
+ mid.statement
+ ' (' + ISNULL(mid.equality_columns, '')
+ CASE WHEN mid.equality_columns IS NOT NULL
AND mid.inequality_columns IS NOT NULL THEN ','
ELSE ''
END + ISNULL(mid.inequality_columns, '') + ')'
+ ISNULL(' INCLUDE ('
+ mid.included_columns
+ ')', '')
AS create_index_statement ,
migs.* ,
mid.database_id ,
mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid
ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * ( migs.avg_user_impact / 100.0 )
* ( migs.user_seeks + migs.user_scans ) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact
* ( migs.user_seeks + migs.user_scans ) DESC
please guide me, Which is best one to capture the missing index.
thanks
August 29, 2012 at 12:54 am
Taking a couple steps back...
Missing indexes is there to give you a place to start with creating indexes that may be missing. It is absolutely not something that you want to use to generate you create index statements that you then just run.
Check the troubleshooting book again, there should be some comments about testing and not just creating all suggestions.
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
August 29, 2012 at 7:37 am
I generally use this query as a first step in determining missing indexes.
DECLARE @DBName VARCHAR(50) = 'yourDB';
SELECT [Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans), 0),
avg_user_impact, TableName = statement, [EqualityUsage] = equality_columns,
[InequalityUsage] = inequality_columns, [Include Cloumns] = 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
WHERE DB_NAME(database_id) = @DBName
ORDER BY [Total Cost] DESC;
Which could be written like this: -
DECLARE @DBName VARCHAR(50) = 'yourDB';
SELECT
[Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans), 0),
avg_user_impact, TableName = statement,
'CREATE NONCLUSTERED INDEX [IX_' + OBJECT_NAME(d.object_id, database_id) + '_' +
REPLACE(REPLACE(REPLACE(ISNULL(REPLACE(d.equality_columns,', ','_') + '_','') +
ISNULL(REPLACE(d.inequality_columns,', ','_'),''),'[',''),']','') + ']','_]',']') +
' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(d.object_id, database_id)) + '.' + QUOTENAME(OBJECT_NAME(d.object_id, database_id)) +
' ( ' + CASE WHEN d.equality_columns IS NOT NULL
THEN d.equality_columns + CASE WHEN d.inequality_columns IS NOT NULL
THEN ', ' + d.inequality_columns
ELSE '' END
ELSE ISNULL(d.inequality_columns,'') END + ' ) ' + ISNULL('INCLUDE ( ' + d.included_columns + ' )','') +
';' AS [Create Index Statement]
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
WHERE DB_NAME(database_id) = @DBName
ORDER BY [Total Cost] DESC;
Now, I'm not saying that indexes that are suggested from the above are the ones that need creating. They're more of a guide that points me towards the areas of the database that I need to investigate.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply