Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


missing index script?


missing index script?

Author
Message
SQL Galaxy
SQL Galaxy
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1218 Visits: 3465
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47137 Visits: 44346
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

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


Cadavre
Cadavre
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2592 Visits: 8436
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.


Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search