Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

missing index script? Expand / Collapse
Author
Message
Posted Wednesday, August 29, 2012 12:49 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:56 AM
Points: 1,068, Visits: 3,033

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
Post #1351420
Posted Wednesday, August 29, 2012 12:54 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:08 PM
Points: 40,177, Visits: 36,580
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 2008, MVP
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

Post #1351422
Posted Wednesday, August 29, 2012 7:37 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:30 AM
Points: 2,379, Visits: 7,582
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.



Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1351613
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse