Talvez os índices não estejam otimizados o suficiente para o seu aplicativo e você não tenha o Veritas I3 para otimizar seu banco de dados, então você poderia usar uma instrução como essa para encontrar índices ausentes:
SELECT
mid.statement,
mid.equality_columns,
mid.inequality_columns,
mid.included_columns,
migs.user_seeks,
migs.user_scans,
migs.last_user_seek,
migs.avg_user_impact,
user_scans,
avg_total_user_cost,
avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) AS [weight]--, migs.*--, mid.*
FROM
sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON (migs.group_handle = mig.index_group_handle)
INNER JOIN sys.dm_db_missing_index_details AS mid
ON (mig.index_handle = mid.index_handle)
ORDER BY
avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) DESC ;
Ou uma declaração como essa para encontrar índices que não são usados em instruções selecionadas e reduzem o desempenho de atualizações / inserções:
CREATE PROCEDURE [ADMIN].[spIndexCostBenefit]
@dbname [nvarchar](75)
WITH EXECUTE AS CALLER
AS
--set @dbname='Chess'
declare @dbid nvarchar(5)
declare @sql nvarchar(2000)
select @dbid = convert(nvarchar(5),db_id(@dbname))
set @sql=N'select ''object'' = t.name,i.name
,''user reads'' = iu.user_seeks + iu.user_scans + iu.user_lookups
,''system reads'' = iu.system_seeks + iu.system_scans + iu.system_lookups
,''user writes'' = iu.user_updates
,''system writes'' = iu.system_updates
from '+ @dbname + '.sys.dm_db_index_usage_stats iu
,' + @dbname + '.sys.indexes i
,' + @dbname + '.sys.tables t
where
iu.database_id = ' + @dbid + '
and iu.index_id=i.index_id
and iu.object_id=i.object_id
and iu.object_id=t.object_id
AND (iu.user_seeks + iu.user_scans + iu.user_lookups)<iu.user_updates
order by ''user reads'' desc'
exec sp_executesql @sql
set @sql=N'SELECT
''object'' = t.name,
o.index_id,
''usage_reads'' = user_seeks + user_scans + user_lookups,
''operational_reads'' = range_scan_count + singleton_lookup_count,
range_scan_count,
singleton_lookup_count,
''usage writes'' = user_updates,
''operational_leaf_writes'' = leaf_insert_count + leaf_update_count + leaf_delete_count,
leaf_insert_count,
leaf_update_count,
leaf_delete_count,
''operational_leaf_page_splits'' = leaf_allocation_count,
''operational_nonleaf_writes'' = nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count,
''operational_nonleaf_page_splits'' = nonleaf_allocation_count
FROM
' + @dbname + '.sys.dm_db_index_operational_stats(' + @dbid + ', NULL, NULL, NULL) o,
' + @dbname + '.sys.dm_db_index_usage_stats u,
' + @dbname + '.sys.tables t
WHERE
u.object_id = o.object_id
AND u.index_id = o.index_id
and u.object_id=t.object_id
ORDER BY
operational_reads DESC,
operational_leaf_writes,
operational_nonleaf_writes'
exec sp_executesql @sql
GO
Eu tenho algumas outras instruções SQL que estou usando quando analiso problemas de desempenho no ambiente de produção, mas acho que esses dois são um bom começo.
(Eu sei, este post é um pouco de tópico, mas eu pensei que você poderia estar interessado, pois tem a ver com a estratégia de indexação)
/ Håkan Winther