Segue um conjunto de queries muito importante para avaliarmos a necessidade de criação de um índice ou até mesmo de eliminação do mesmo. Sempre é bom ter cuidado também no gerenciamento de índices pois ao invés de melhorarmos podemos piorar a performance.
/*Tabelas que não contêm índices clusterizados*/
select distinct(tb.name) as Tabela, p.rows from sys.objects tb
join sys.partitions p on p.object_id = tb.object_id
where type = 'U' and tb.object_id not in
(
select ix.object_id from sys.indexes ix where type = 1
)
order by p.rows desc;
/*Tabelas que não possuem chave primária*/
select distinct (tb.name) as Tabela, p.rows
from sys.objects tb
join sys.partitions p on p.object_id = tb.object_id
where type = 'PK' and tb.object_id not in
(
select ix.object_id from sys.key_constraints ix where type = 'PK'
)
order by p.rows desc;
/* Tabelas que mais seriam beneficiadas com novos índices */
select top 15 AVG((avg_total_user_cost * avg_user_impact * (user_seeks + user_scans))) as Impacto ,mid.object_id,mid.statement as Tabela
from sys.dm_db_missing_index_group_stats as migs
join sys.dm_db_missing_index_groups as mig on migs.group_handle = mig.index_group_handle
join sys.dm_db_missing_index_details as mid on mig.index_handle = mid.index_handle and database_id = DB_ID('nomeBase')
group by mid.object_id, mid.statement;
/*Índices sugeridos pelo SGBD*/
select top 15 AVG((avg_total_user_cost * avg_user_impact * (user_seeks + user_scans))) as Impacto,migs.group_handle,mid.index_handle,migs.user_seeks,
migs.user_scans,mid.object_id,mid.statement,
mid.equality_columns,mid.inequality_columns,mid.included_columns
from sys.dm_db_missing_index_group_stats as migs
join sys.dm_db_missing_index_groups as mig on migs.group_handle = mig.index_group_handle
join sys.dm_db_missing_index_details as mid on mig.index_handle =
mid.index_handle and database_id = DB_ID('nomeBase')
group by mid.object_id, migs.group_handle,mid.index_handle,migs.user_seeks,
migs.user_scans,mid.object_id,mid.statement
,mid.equality_columns,mid.inequality_columns,
mid.included_columns;
/*Índices nunca utilizados pelo SGBD*/
select tb.name as Tabela, ix.name as Indice, ix.type_desc,leaf_insert_count,
leaf_delete_count,leaf_update_count,nonleaf_insert_count
,nonleaf_delete_count,
nonleaf_update_count from sys.dm_db_index_usage_stats vw
join sys.objects tb on tb.object_id = vw.object_id
join sys.indexes ix on ix.index_id = vw.index_id and ix.object_id = tb.object_id
join sys.dm_db_index_operational_stats(db_id('nomeBase'),null,null,null)
vwx on vwx.object_id
= tb.object_id and vwx.index_id = ix.index_id
where vw.database_id = db_id('nomeBase') and vw.user_seeks = 0 and vw.user_scans = 0
and vw.user_lookups = 0 and vw.system_seeks = 0 and vw.system_scans = 0 and vw.system_lookups = 0
order by leaf_insert_count desc, tb.name asc, ix.name asc;
/*Análise de índices*/
select ix.name, ix.type_desc, vwy.partition_number, vw.user_seeks,
vw.last_user_seek,
vw.user_scans,vw.last_user_scan, vw.user_lookups,
vw.user_updates as 'Total_User_Escrita',
(vw.user_scans + vw.user_seeks + vw.user_lookups) as 'Total_User_Leitura',
vw.user_updates - (vw.user_scans + vw.user_seeks + vw.user_lookups) as 'Dif_Read_Write',ix.allow_row_locks,vwx.row_lock_count,row_lock_wait_count,
row_lock_wait_in_ms,ix.allow_page_locks,
vwx.page_lock_count,page_lock_wait_count,
page_lock_wait_in_ms,ix.fill_factor,ix.is_padded,
vwy.avg_fragmentation_in_percent,vwy.avg_page_space_used_in_percent,
ps.in_row_data_page_count as Total_Pagina_usada,
ps.in_row_reserved_page_count as Total_Pagina_reservada,
CONVERT(real,ps.in_row_used_page_count) * 8192 / 1024 /1024 as Total_Indice_usado_MB,
CONVERT(real,ps.in_row_reserved_page_count) * 8192 /1024 /1024 as Total_Indice_Reservado_MB,page_io_latch_wait_count,page_io_latch_wait_in_ms
from sys.dm_db_index_usage_stats vw
join sys.indexes ix on ix.index_id = vw.index_id and ix.object_id = vw.object_id
join sys.dm_db_index_operational_stats(db_id('nomeBase'),
OBJECT_ID(N'Log'),null,null) vwx on vwx.index_id = ix.index_id and ix.object_id = vwx.object_id
join sys.dm_db_index_physical_stats(db_id('nomeBase'),
OBJECT_ID(N'Log'),null,null,'SAMPLED') vwy on vwy.index_id = ix.index_id and ix.object_id
= vwy.object_id and vwy.partition_number = vwx.partition_number
join sys.dm_db_partition_stats ps on ps.index_id = vw.index_id and ps.object_id = vw.object_id
where vw.database_id = DB_ID('nomeBase') and OBJECT_NAME(vw.object_id) = 'Log'
order by user_seeks desc, user_scans desc;
/*Tabelas com maior quantidade de índices*/
select x.id,x.Tabela,x.Total_Indice,COUNT(*) as Total_Coluna
from sys.columns cl
join
(
select ix.object_id as id, tb.name as Tabela, COUNT(ix.object_id) as Total_Indice
from sys.indexes ix
join sys.objects tb on tb.object_id = ix.object_id and tb.type = 'u'
group by ix.object_id, tb.name
) x on x.id = cl.object_id
group by id,Tabela,Total_Indice
order by Total_Coluna,Total_Indice;
/*Consultas que mais consomem processamento do servidor*/
select top 10 (total_worker_time / execution_count) / 1000 as [avg cpu time as ms],
SUBSTRING(st.text,(qs.statement_start_offset / 2) + 1,
((CASE qs.statement_end_offset
WHEN -1 then DATALENGTH(st.text)
else qs.statement_end_offset
END - qs.statement_start_offset) / 2) + 1 ) as statement_text,
execution_count,last_execution_time,
last_worker_time / 1000 as last_worker_time,
min_worker_time / 1000 as min_worker_time,
max_worker_time / 1000 as max_worker_time,
total_physical_reads,last_physical_reads,
min_physical_reads,max_physical_reads,
total_logical_writes, last_logical_writes,
min_logical_writes,max_logical_writes, query_plan
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
cross apply sys.dm_exec_text_query_plan(qs.plan_handle,DEFAULT,DEFAULT) as qp
order by 1 desc;