/*==================================================================================
Curso: SQL SERVER 2019
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, UPDATE STATISTICS
==================================================================================*/

use CLIENTES
go

set statistics io, time on

-- Antes de rodar o proximo sql, marcar o sql abaixo e 
-- clicar no botao Display Estimated Execution Plan
-- Vericar que antes mesmo de rodar o SQL SERVER o SQL SERVER criou uma 
-- estimativa para o otimizador de querie ja saber quais operadores
-- devera usar para resolver da melhor forma a querie e quanta
-- memoria devera usar e quantos cores (maxdop).
select * from customer where FirstName ='maria'
go

-- Vamos olhar o conteudo da estatistica criada
-- pelo SSMS ou pelo sql abaixo:
DBCC SHOW_STATISTICS ("Customer",firstName);  
GO 

-- Vamos verificar o plano de execucao. Note que mesmo tendo
-- uma estatistica que retornou apenas 2 registro e que 
-- bateu com a estimativa, o sql server usou o operador clustered index
-- scan, ou seja, leu o indice cluster (tabela) toda para 
-- trazer apenas 2 registros e verifique em messages que 
-- precisou ler 4 paginas de dados em logical reads.

-- Vamos criar um indice pelo campo firstName para dar
-- oportunidade do otimizar de querie usar um operador
-- seek que ira saltas diretamete ao primeiro registro maria e 
-- como o indice estará ordenado alfabeticamente por este campo,
-- o SQL SERVER irá percorrer e achará as marias existentes.

create index firstName_idx on customer(firstName)
go

-- Repare que foi criado um novo indice na tabela
-- customer e uma nova estatistica com o mesmo nome do indice.
-- Agora, toda vez que este foi reconstruido com rebuild
-- a estatistica sera recriada ou quando rodar o comando
-- update statistics.

-- AGORA vamos rodar novamente o comando abaixo
-- e vamos ver se o otimizador de querie mudou o operador
-- e a quantidade de paginas de dados lida
select * from customer where FirstName ='maria'
go

-- O otimizador de querie toma a decisao de quais operadores
-- vai usar, baseado nas estatisticas. Considerando a estimativa
-- do tempo de execucao, o SQL SERVER achou que seria menor custoso
-- ler a tabela toda que tem apenas 113 registros 
-- do que usar o operador seek, por causa do * no SELECT.
-- Ele teria que ler o campo firstname que estaria no indice
-- mas teria que fazer um key lookup no cluster index
-- para ler os outros dados. Neste caso, o tempo de execucao
-- foi de 0 ms e leu 4 paginas de dados.

-- VAMOS FORCAR para que o SQL SERVER use o Index e
-- use o operador SEEK

select * from customer with (INDEX(firstName_idx))
where FirstName ='maria'
go
-- veja o resultado em termos de pagina de dados lidas e tempo
-- caso exista, devido ao tamanho da tabela.

-- AGORA, vamos rodar novamente
-- mas trazendo apenas campo o firstname.

select FirstName from customer where FirstName ='maria'
go

-- Para deletar uma estatistica existente criado pelo SQL SERVER, 
-- de forma automatica, quando não existe indice, rodar este script:
-- DROP STATISTICS TABELA.nomeestatistica
use clientes
go
DROP STATISTICS customer._WA_Sys_00000002_01142BA1
go
-- Dar um refresh e verificar que a estatistica foi deletada

-- Se quiser criar uma estatistica nova via script, sem 
-- um indice relacionado:
use CLIENTES
GO
CREATE STATISTICS Customer_FirstName 
ON CLIENTES.dbo.Customer (FirstName);  
GO  
DBCC SHOW_STATISTICS ("Customer",Customer_FirstName );  
GO  

-- abrir a estatistica e verificar em detalhes a quantidade de 
-- craig que ele verificou que existe e depois rodar
select * from customer where FirstName ='craig'
go

-- rodar o comando abaixo com um novo campo  e verificar 
-- se foi criado uma nova estatistica
select * from customer where city ='berlin'
go

-- e agora se rodarmos com mais de um campo de filtro
select * from customer where country ='spain'
and city ='madrid'

-- Cada campo que é incluido é criado uma nova estatistica
-- que ocupa memória do servidor, mas repare que neste caso
-- ele criou mais 1 estatistica, referente ao campo contry.

-- Ao executar o comando vai verificar que o sql server
-- usou o operador Cluster Index Scan, porque nao existe
-- indice para resolver esta operacao com operador seek.

-- Vamos criar um indice pelo campo country e outro pelo campo
-- city
create index country_idx on customer(country)
go
create index city_idx on customer(city)
go

-- e vamos rodar novamente
select Id, Country from customer where country ='spain'
go
-- se deletarmos o indice e realizarmos a mesma consulta
DROP index country_idx on customer
go
select Id, Country from customer where country ='spain'
go

-- e aqui ira usar o indice noncluster e o operador seek
select Id, city from customer where city ='madrid'
go

-- e se rodarmos com os dois campos
select * from customer where country ='spain'
and city ='madrid' 

-- Repare que mesmo sendo criado os dois indices
-- o otimizar de queires do sql server preferiu usar
-- uma leitura sequencial em todo o indice cluster do
-- que seek+lookup

-- Vamos mudar o comando
select country, city from customer where country ='spain'
and city ='madrid' 

-- Repare que ainda assim o otimizar de querie usou cluster
-- index scan, e repare em propriedades que o sql server
-- estimou errado, mesmo tendo acabado de criar o indice
-- e por consequencia uma nova estatistica.
-- Ele estimou que iria trazer apenas 1 linha, mas na verdade
-- foram 3. 

-- Vamos forcar um update statistics de todos os indices
-- lendo toda a tabela customer
UPDATE STATISTICS CUSTOMER WITH FULLSCAN
go

select country, city from customer where country ='spain'
and city ='madrid'

-- NAO HOUVE MUDANCA

-- Vamos verificar a estatistica deste indice
DBCC SHOW_STATISTICS ("Customer",country_idx);  
GO  
DBCC SHOW_STATISTICS ("Customer",city_idx);  
GO 

-- Repare que existe duas estatisticas, uma delas 
-- o sql server inclui o campo id, que tornou uma 
-- estatistica mais seletiva, se usarmos este campo 
-- na clausula where, até porque é a PK.

-- Vamos tentar melhorar agora a seletividade e
-- vamos verificar se o otimizador de querie
-- consegue ter mais assertividade
-- Vamos cria um novo indice, agora composto

create index cityEcountry_idx on customer(city, country)
go

DBCC SHOW_STATISTICS ("Customer",cityEcountry_idx);  
GO 

-- Repare que no histograma, o sql server só 
-- registra o primeiro campo do indice criado city
-- porque tudo é limitado em apenas 8k
-- e ele nao tem espaco na pagina de 8k para salvar as combinacoes, 
-- dos dados, mas o sql server salva na densidade uma estimativa de seletividade
-- daquele indice e assim dependendo dos parametros pesquisados
-- consegue dar uma indicacao para o otimizador de
-- querie que um indice pode ser melhor do que outro pois vai fazer
-- com que o SQL SERVER tenha que ler menos dados e assim ser mais performatico, 
-- Se passarmos apenas o campo city na busca ele vai usar o indice
-- e vai usar o operador seek, da mesma forma se passarmos
-- o city e o country no where ou country e city.
-- Agora se passarmos apenas o filtro contry ele nao vai usar
-- o indice. Por que a ordem dos campos no indice é
-- fundamental.

-- VAMOS TESTAR
select country, city from customer where country ='spain'
and city ='madrid'
-- Repare que o SQL SERVER usou o novo indice, mas o otimizador ainda nao fez
-- o melhor trabalho de estimativa, mas quando esta estimativa
-- nao é maior que 10 ou menor que 10 vezes em média (ENTRE ESTIMADO E REAL), 
-- ainda assim,o sql server ainda vai usar um plano que retrata a melhor realidade.
-- Mas lembre-se que é uma estimativa, baseada em um conjunto de dados e que 
-- no caso de indice composto, o SQL SERVER trabalha com uma média
-- para estimar combinacao de dados entre as colunas, porque a estimativa
-- de fato registra apenas os dados da primeira coluna do indice, assim
-- mesmo limitado toda a tabela em apenas 1 pagina (8k).

-- REPARE ENTRETANTO NO LABEL SEEK PREDICATES
-- QUE TEM UMA INFORMACAO CONVERT_IMPLICIT
-- E ISTO TRAZ ALUNS PROBLEMAS DE CARDINALIDADE (ESTIMATIVA DO OTIMIZADOR)
-- REPARE QUE OS CAMPOS NA TABELA FORAM CRIADOS COM 
-- NVARCHAR(40), ou seja Unicode.
-- Cuidado em criar tipos de campos e fazer conversoes
-- explicitas usando convert, cast ou implicitas. Este
-- problema ocorre muito em chamadas de stored procedures
-- quando cria um campo nvarchar na tabela e no parametro
-- da stored procedure usa apenas varchar e ai o sql server
-- faz um convert_implicit, e confunde o otimizador de querie
-- levando mais tempo para ser executado.

-- Evite usar FUNCTIONS dentro de comandos SQL que geralmente
-- trazem problemas de cardinalidade.

-- Vamos testar agora assim
select country, city from customer where country = N'spain'
and city = N'madrid'

-- O otimizador de querie retirou o implict_convert 
-- levando um ganho de tempo (TABELAS GRANDES), muitas vezes mas a estatistica
-- ainda nao esta 100% correta.

-- Pode mudar a order que o otimizador vai usar o mesmo indice
-- e o mesmo plano, ou seja, nao importa a ordem dos campos
-- no where. A ordem importa apenas no indice.
select country, city from customer where city ='madrid'
and country ='spain'
go

-- Agora veja que para outras combinacoes, a estimativa esta certa
-- Muitas vezes valores que fogem a media da quantidade de dados
-- trazem distorcoes entre previsao e real quantidade de dados,
-- porque é uma estimativa. Quanto mais uniforme for a combinacao
-- dos dados mais correta estarã a estimativa. Valor muito diferentes
-- como por exemplo 1 pais com 100 cidades e todos os os paises com 5 cidades
-- podem trazer distorcoes. O problema comeca a afetar a performance
-- quando passa de 10X, entre previsao e leitura real, porque ai
-- o otimizador de plano esta se baseando em uma estatistica muito
-- errada e comeca a usar operadores errados ou alocar mais recursos
-- por exemplo memoria para executar um sql.

select country, city from customer where city ='Århus'
and country ='Denmark'
go
select country, city from customer where city ='Albuquerque'
and country ='USA'
go

-- Vamos rodar agora este codigo e vera que nao usou mais o 
-- operador seek, mas fez uma leitura sequencial em todo 
-- o indice nonclustered, porque o indice nao comecou por country, mas tinha 
-- o country e ai leu menos paginas de dados do que se tivesse que 
-- ler o country no indice cluster (tabela).
select country from customer where country ='spain'
go

-- e agora vamos usar o campo city no filtro
select city from customer where city ='madrid'
go
-- Repare que o otimizador estimou 100% certo.

-- AGORA VAMOS DESLIGAR O UPDATE STATISTICS AUTOMATICO PELO SSMS
-- NA BASE CLIENTES E VAMOS RODAR O MESMO CODIGO.
select city from customer where city ='madrid'
go
-- Nada mudou, porque dados novos nao foram alterados
-- e as estatisticas continuam atualizadas

-- VAMOS INSERIR MAIS DADOS NA TABELA COM CITY Madrid, ou seja,
-- de agora em diante nao teremos mais apenas 3 cidades de madrid
-- conforme o otimizador de querie acredita por estar baseado
-- na estatistica e assim usa o operador seek.
USE [CLIENTES]
GO

INSERT INTO [dbo].[Customer]
           ([FirstName]
           ,[LastName]
           ,[City]
           ,[Country]
           ,[Phone])
     VALUES
           ('sandro'
           ,'xpto'
           ,'madrid'
           ,'Spain'
           ,'99991111')
GO 100

-- e vamos ver, que agora temos mais de 300 registros
-- com cidade madrid, mas o otimizador está se baseando
-- ainda em uma estatistica antiga, com 3 registros o que
-- poderia levar a utilizar um operador errado como por exemplo
-- um seek quando na verdade um cluster index scan seria menos
-- custoso e assim mais rapido para ser executado. 
select city from customer where city ='madrid'
go

-- VAMOS FORCAR NO SELECT PARA LIMPAR DO CACHE 
-- COM O PLANO QUE ESTIVER NA MEMORIA
select city from customer where city ='madrid'
OPTION (RECOMPILE)
go
-- Vamos ligar novamente para o sql server
-- atualizar as estatisticas de forma automatica

-- Vamos agora usar uma base de dados maior
use StackOverflow2010
go
select * from Users
go

select id, CreationDate, DisplayName, LastAccessDate
from Users where CreationDate = '2009-11-17 16:02:15.320'
go
-- Verificar que o otimizador estimou que teria que ler mais de 
-- 299 mil linhas e acertou. Por causa disto, utilizou o 
-- operador cluster index scan, ou seja, leu todo o indice cluster
-- ou seja, toda a tabela para recuperar apenas 1 registro
-- e fez processamento em paralelo.
-- Em propriedade do operador, vera que leu 7778
-- e dividiu a leitura em 4 threads pelos 4 nucleos da
-- cpu (maxdop 0)

-- Vamos criar um indice pelo campo de filtro CreationDate
create index idx_creationdate on users (creationdate)
go

select * from Users 
where CreationDate = '2009-11-17 16:02:15.320'
go
-- Veja agora a quantidade de paginas lidas e o tempo. 
-- Repare que usou agora o operador seek com key lookup
-- devido ao * que traz dados que não estao no indice

-- Dependendo do custo do Key Lookup, quando ter que ir milhões
-- de vezes por exemplo na tabela e ler outros dados, e quando
-- nao sao muitos campos por exemplo, que estão em uma tabela
-- que não tem muito update, podemos colocar estes campos
-- fazendo parte do indice e ja ordenando, ou colocando
-- como parte do indice com o INCLUDE

-- Vamos recriar o mesmo indice, sem criar um novo
-- incluindo os outros campos, e vamos realizar uma compressao
-- deste indice para deixar o indice com menor tamanho
-- possivel para ganho de performance.

CREATE NONCLUSTERED INDEX [idx_creationdate]
ON [dbo].[users] ([CreationDate])
INCLUDE ([DisplayName], [LastAccessDate])
WITH (DATA_COMPRESSION = PAGE, 
SORT_IN_TEMPDB = OFF, 
DROP_EXISTING = ON, ONLINE = ON, 
FILLFACTOR = 100) ON [PRIMARY]
GO

select id, CreationDate, DisplayName, LastAccessDate
from Users where CreationDate = '2009-11-17 16:02:15.320'
go
-- Veja que agora a quantidade de leitura caiu porque nao
-- o otimizador de plano nao precisou mais usar o key lookup

-- VAMOS AGORA fazer uma outra consulta
select id, CreationDate, DisplayName, LastAccessDate
from Users where CreationDate 
between '2009-11-17 16:02:15.320' and '2013-11-17 16:02:15.320'
go
-- Veja que leu 216.961 registros de um total de cerca de 299.000
-- registros na tabela, mas usou o SEEK. Seek nao quer dizer
-- necessariamente pouca quantidade de leitura mas que 
-- o sql server vai achar muito rapidamente o primeiro elemento
-- e depois vai varrer o indice que ja estara ordenado por este
-- campo. Agora se fosse menos custoso ler toda a tabela, o 
-- otimizador de querie iria ler toda a tabela, a não ser
-- que a estatistica estivesse errada, enganando o otimizador
-- por falta de atualizacao das estatisticas depois de uma grande
-- quantidade de alteracao da tabela ou por problema de cardinalidade
-- devido a conversoes explicitas (cast, convert, etc), implicita
-- quando uma tabela tem um campo do tipo por exemplo int
-- e no codigo voce trata este campo como bigint ou por uso
-- de uma function por exemplo.

-- NAO FOI O CASO. O OTIMIZADOR FEZ SEU PAPEL COM 100% DE 
-- ASSERTIVIDADE NESSE CASO.

-- Vamos agora fazer uma conversao explicita de dados no campo de busca
-- usando functions simples do sql server
select id, CreationDate, DisplayName, LastAccessDate
from Users where year(CreationDate) = 2009
go
select id, CreationDate, DisplayName, LastAccessDate
from Users where day(CreationDate) = 4
go
-- Conseguiu usar indice fazendo index scan por faixa de dados

--  Agora vamos fazer uma conversao explicita, que pode trazer problemas
-- de cardinalidade com o otimizador.
select id, CreationDate, DisplayName, LastAccessDate
from Users where substring(convert(varchar(50),CreationDate), 5,2) = 4
go
-- COMPARE O GASTO DO TEMPO DE CPU entre os ultimos dois codigos
-- e veja como um problem de cardinalidade gera mais pressao na cpu

-------		AGORA VAMOS VER COMO ESTAO NOSSAS ESTATISTICAS
USE CLIENTES
GO
SELECT schema_name(schema_id) AS NomeSchema
	,object_name(o.object_id) AS NomeTabela
	,i.NAME AS NomeIndex
	,index_id IndexId
	,o.type Tipo
	,STATS_DATE(o.object_id, index_id) AS DataEstatistica
FROM sys.indexes i
JOIN sys.objects o ON i.object_id = o.object_id
WHERE o.object_id > 100
	AND index_id > 0
	AND is_ms_shipped = 0 
	and object_name(o.object_id)  = 'customer'

-- Vamos ver agora as varias forma de atualizar as estatisticas

-- Example 1: SQL Server UPDATE STATISTICS for all statistics in an object
Update STATISTICS customer

-- Example 2: SQL Server UPDATE STATISTICS for specific index
Update STATISTICS customer firstName_idx

-- Example 3: SQL Server UPDATE STATISTICS with FULL Scan
Update STATISTICS customer firstName_idx with fullscan
-- ou podemos usar percentual
Update STATISTICS customer firstName_idx WITH SAMPLE 50 PERCENT
-- ou podemos usar quantidade de linhas
Update STATISTICS customer firstName_idx WITH SAMPLE 1000 ROWS

-- e finalmente o comando que atualiza todas as estatisticas de um banco de dados
-- Utilize com cautela nas grandes bases de dados, porque pode gerar pressão
-- no disco (read) e cpu. O fullscan tambem pode em grandes tabelas mas
-- é melhor do que um rebuild index que causa lock table por exemplo no sql server standard
sp_updatestats