/*==================================================================================

Curso: SQL SERVER, do Básico ao Avançado.
Para ter acesso ao curso completo: 
https://www.udemy.com/course/draft/3957796/?referralCode=FB10D369E786D9FE8A48

Instrutor: Sandro Servino
https://www.linkedin.com/in/sandroservino/?originalSubdomain=pt
https://filiado.wixsite.com/sandroservino

ATIVIDADES ROTINEIRAS BÁSICAS DO DBA
PERFORMANCE: INDICES, algums scripts úteis.

==================================================================================*/

-- 1. sp_blitzindex (Brent Ozar)

-- Existem índices duplicados desperdiçando seu armazenamento e memória?
-- Você gostaria de ajuda para localizar índices não utilizados que estão 
-- inchando seus backups e impactando negativamente operações de updates?
-- free sp_blitzindex

use auditoriadba
go
sp_blitzindex @mode = 4 -- mais detalhado
go

-- https://www.brentozar.com/blitzindex/

----------------------------------------------------------------------------------

-- Alguns Scripts uteis:

-- 2. Identificando índices ausentes (missing index)

-- Uma das tarefas do dia a dia de um DBA, é identificar índices ausentes no banco de dados,
-- que podem sugerir um ganho de performance de consultas 
-- que são frequentemente executadas. Com a query abaixo, podemos tornar essa tarefa um 
-- pouco mais fácil, pois consultando as DMV’s de 
-- missing index, podemos identificar esses dados rapidamente, MAS CUIDADO, porque é baseado no aconselhador do SQL SERVER
-- quem nem sempre aconselha com exatidão, principalmente quando sugere a criação de indices 
-- muito grande com INCLUDE COM MAIS DE 5 CAMPOS e 
-- mesmo na atual versáo, não percebe ja que existe um indice que poderia ser apenas alterado para incluir um novo campo.
-- Faça suas análise e verifique se esta consulta é muito usada para nao criar indices desnecessários que irá impactar negativamente
-- suas operaçoes de insert, updade e delete.

SELECT
    mid.statement,
    migs.avg_total_user_cost * ( migs.avg_user_impact / 100.0 ) * ( migs.user_seeks + migs.user_scans ) AS improvement_measure,
    OBJECT_NAME(mid.object_id),
    '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

--avg_total_user_cost	float	Average cost of the user queries that could be reduced by the index in the group.
--avg_user_impact	float	Average percentage benefit that user queries could experience if this missing index group was implemented. The value means that the query cost would on average drop by this percentage if this missing index group was implemented.

----------------------------------------------------------------------------------
-- 3. Como estimar tempo para criação de um indice.

-- A partir da versão 2017, através desta dmv sys.index_resumable_operations consegue verificar o processo de REBUILD
-- de um indice e o percentual da execução do processo.
-- AINDA, a partir do SQL Server 2017 pode realizar o REBUILD de índices com o parâmetro RESUMABLE=ON, 
-- podendo pausar e resumir esse REBUILD e acompanhar o andamento utilizando a DMV sys.index_resumable_operations.
 
-- https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-index-resumable-operations?view=sql-server-ver15

--Example
-- all resumable index creation or rebuild operations that are in the RUNNIND state.
drop index [NonClusteredIndex-20210421-024911] ON [dbo].[NOindex]
go
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20210421-024911] ON [dbo].[NOindex]
(
	[Registro] ASC,
	[Numero] ASC,
	[nomecliente] ASC,
	[endereco] ASC,
	[cidade] ASC,
	[estado] ASC,
	[pais] ASC
)WITH (RESUMABLE= ON, ONLINE = ON) -- PRECISA LIGAR A OPCAO RESUMABLE E SO RODA COM ONLINE
go

-- Rodar em outra sessao
use auditoriadba
go
SELECT * FROM  sys.index_resumable_operations 

----------------------------------------------------------------------------------

-- 4. Como verificar o nivel de fragmentação dos indices

-- Uma vez identificado o nível de fragmentação do índice, pode-se escolher qual o método que será utilizado para desfragmentá-lo:
-- REORGANIZE: Pela minha experiencia, quando o nível de fragmentação está entre 50% e 80%. 
-- Esse método não causa indisponibilidade do índice, pois o índice não chega a ser apagado, apenas reorganizado.
-- REBUILD: Pela minha experiencia, quando o nível de fragmentação é superior a 80%. 
-- Esse método por padrão, no SQL SERVER standard, traz grande impacto na disponibilidade das tabelas que comportam o indice
-- pois apaga e recria o índice novamente. Para que não seja gerada indisponibilidade, pode-se utilizar o parâmetro ONLINE na execução do REBUILD,
-- disponivel apenas no SQL SERVER Enterprise. O rebuild index ainda gera grande pressão na cpu e disco para tabelas grandes. Quando
-- for executar, procure fazer em momentos de baixa operação ou nenhuma, de forma agendada e comunicada.

-- REINDEXAÇÃO = DESFRAGMENTAÇÃO E ORDENAÇÃO FISICA DOS DADOS. EM MUITOS CASOS NÃO TRAZ GRANDES VANTAGENS, MAS PODE AJUDAR EM 
-- MUITAS OPERAÇÕES TAMBÉM. AVALIE E SIMULE SEUS BENEFICIOS, ANTES DE REALIZAR REBUILDS OU MESMO REORGANIZE. 
-- VEJA ESTES ARTIGOS DA KENDRA LITTLE E VIDEOS DO BRENT OZAR SOBRE ESTE TEMA.

-- https://www.youtube.com/watch?v=iEa6_QnCFMU
-- https://littlekendra.com/2016/10/13/should-you-rebuild-or-reorganize-indexes-on-large-tables-dear-sql-dba-episode-19/

-- https://www.sqlshack.com/how-to-identify-and-resolve-sql-server-index-fragmentation/
SELECT S.name as 'Schema',
T.name as 'Table',
I.name as 'Index',
DDIPS.avg_fragmentation_in_percent,
DDIPS.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN sys.tables T on T.object_id = DDIPS.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = DDIPS.object_id
AND DDIPS.index_id = I.index_id
WHERE DDIPS.database_id = DB_ID()
and I.name is not null
AND DDIPS.avg_fragmentation_in_percent > 0
ORDER BY DDIPS.avg_fragmentation_in_percent desc

--------------------------------------------------------------------------------

-- 5. Como verificar a utilização dos indices

-- 5.1

-- Com a query abaixo, é possível identificar a utilização dos índices no banco, exibindo leituras com Index Seek, 
-- Index Scan, Lookups e Updates (número de vezes que o índice foi atualizado com novos registros)

USE AUDITORIADBA
GO

SELECT
    ObjectName = OBJECT_SCHEMA_NAME(idx.object_id) + '.' + OBJECT_NAME(idx.object_id),
    IndexName = idx.name,
    IndexType = CASE WHEN is_unique = 1 THEN 'UNIQUE ' ELSE '' END + idx.type_desc,
    User_Seeks = us.user_seeks,
    User_Scans = us.user_scans,
    User_Lookups = us.user_lookups,
    User_Updates = us.user_updates
FROM
    sys.indexes idx
    LEFT JOIN sys.dm_db_index_usage_stats us ON idx.object_id = us.object_id AND idx.index_id = us.index_id AND us.database_id = DB_ID()
WHERE
    OBJECT_SCHEMA_NAME(idx.object_id) != 'sys'
ORDER BY
    us.user_seeks + us.user_scans + us.user_lookups DESC

--------------------------------------------------------------------------------

-- 6. Como verificar o tamanho dos indices. Pode ser uma oportunidade de 
-- compactação pelas tecnologias que já vimos.

-- 6.1 Exemplo 1
-- Atenção para os maiores indices, que tem muito quantidade de gravações e pouca leitura (reads)
-- ou nenhuma que pode significar que o indice não está sendo util, pior apenas prejudicando
-- as operações de write.

SELECT DISTINCT o.name, indexname=i.name, i.index_id, 
CASE WHEN  i.type_desc = 'nonclustered' THEN 'Nonclustered'
ELSE 'CLUSTERED_NOT_DELETE'
END AS TypeIndex
, reads=user_seeks + user_scans + user_lookups 
, writes = user_updates 
, rows = (SELECT SUM(p.rows) FROM sys.partitions p 
          WHERE p.index_id = s.index_id AND s.object_id = p.object_id)
, MBytessizeIndex = (SELECT ((8 * SUM(a.used_pages))/1024) FROM sys.allocation_units AS a
         where a.container_id = p.partition_id)
, CASE
 WHEN s.user_updates < 1 THEN 100
 ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
 END AS reads_per_write, 
 s.last_user_seek,s.last_user_scan, s.last_user_lookup,s.last_user_update,
 o.create_date, o.modify_date
, 'DROP INDEX ' + QUOTENAME(i.name) 
+ ' ON ' + QUOTENAME(c.name) + '.' + QUOTENAME(OBJECT_NAME(s.object_id)) as 'drop statement'
FROM sys.dm_db_index_usage_stats s 
INNER JOIN sys.indexes i ON i.index_id = s.index_id AND s.object_id = i.object_id 
INNER JOIN sys.objects o on s.object_id = o.object_id
INNER JOIN sys.schemas c on o.schema_id = c.schema_id
inner JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
AND s.database_id = DB_ID() 
AND (SELECT SUM(p.rows) FROM sys.partitions p 
WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000 -- trazer tabela com mais de 10000 linhas
 AND user_seeks + user_scans + user_lookups  < 10000 -- que teve no maximo 10000 reads, SE EU QUISR VER TODOS OS INDICES INCLUSIVE OS CLUSTER PARA SE CALHAR FAZER COMPRESS REMOVER COMENTARIO
 --AND i.name = 'I_1684AIFDOCUMENTTABLEIDX' -- SE EU QUISER PESQUISAR UM INDICE ESPECIFICO, REMOVER O COMENTARIO NO COMEÇO DESTA LINHA E COLOCAR O NOME DO INDICE
  and i.is_primary_key = 0 -- This condition excludes primary key constarint
  AND i.is_unique = 0 -- This condition excludes unique key constarint
ORDER BY MBytessizeIndex desc

------------------------------------------------------------------------------------------------------------------------