Tabelas que não contêm índices clusteriazados.
select distinct(tb.name) as Table_name, 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 Table_name, 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.parent_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(‘SEU Banco de Dados AQUI ‘) GROUP BY
mid.object_id, mid.statement ORDER BY Impacto DESC;
------------------------------------------------------------------------------
Top 15 índices, sugeridos pelo SGBD.
SELECT TOP 15 (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(‘SEU Banco de Dados AQUI’) –and mid.object_id =
object_id(‘tabela’) — se desejar ver apenas para uma tabela específica order by
Impacto DESC;
------------------------------------------------------------------------------
Índices nunca utilizados pelo SGBD.
select tb.name as Table_Name, ix.name as Index_Name,
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(‘AdventureWorks’),
Null, NULL, NULL) vwx on vwx.object_id = tb.object_id and vwx.index_id =
ix.index_id where vw.database_id = db_id(‘AdventureWorks’)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
------------------------------------------------------------------------------
2 - indices de sistemas nunca utilizados
select tb.name as Table_Name,
ix.name as Index_Name,
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('msdb_org'), Null, NULL, NULL)
vwx on vwx.object_id = tb.object_id and vwx.index_id = ix.index_id
where ix.name <> 'clust' and ix.name <> 'nc' and ix.name <> 'nc1' and ix.name <> 'nc2' and ix.name <> 'nc3' and ix.name <> 'clst'
And vw.user_seeks = 0 and vw.user_scans = 0 and vw.user_lookups = 0
Order By leaf_insert_count desc, tb.name asc, ix.name asc
------------------------------------------------------------------------------
Avaliando í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_used_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(‘ArtigosMS’), 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(‘ArtigosMS’), 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(‘ArtigosMS’) 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.table_name, x.Total_index, count(*) as Total_column from sys.columns cl
join (select ix.object_id as id, tb.name as table_name, count(ix.object_id) as
Total_index 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, table_name, Total_index order by 3 desc
0 comentários:
Postar um comentário