Bom Dia,
Localizei a seguinte query na internet com o objetivo de realizar a engenharia reversa de criação de novos índices necessários a melhoria de performance.
-- Missing Index Script
-- Original Author: Pinal Dave (C) 2011
SELECT TOP 25
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
Porém não corro o risco de prejudicar a performance de inserts e updates causando locks no banco de dados?, o que seria pior.
Indices nonclustered causam este problema? Ou somente indices cluster?
Existe alguma query específica para medir se a tabela recebe muita escrita?
Obs: Faço update statistcs e Rebuid online de 30 em 30 minutos em intervalos diferentes durante todo o dia.
Grato,
Maurício
