/*==================================================================================
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, INDICES
==================================================================================*/

use StackOverflow2010
go

select * from Users -- Vamos dar uma olhada na tabela no SSMS
go

-- Pense na lista de dados como uma folha de papel impresso, ou mesmo
-- dependendo da quantidade de linhas impressas, como uma grande quantidade
-- de folhas impressas

-- Vamos ligar a estatisticas de leituras realizadas 
set statistics io on
go

select * from Users
go

-- total logical reads 7405 x 8k = 59.000K ou seja, mais de 60 milhoes
-- de bytes lidos, entre 15 a 30 resmas de papel com 500 folhas cada.

-- Se recebesse uma tarefa, para tentar achar por exemplo quantas 
-- pessoas moram em uma localidade "San Diego, CA" teria que ler as 
-- informacoes contidas na coluna Location da primeira linha a ultima linha impressa.
-- ou seja, teria que ler 299.398 linhas.

-- Quantas linhas voce tiver que ler menos performance voce terá então o principal objetivo
-- quando estiver realizando perfomance tunning é buscar uma forma de otimizar suas operaçoes
-- para ler a menor quantidade de linhas possiveis, através de algumas técnicas, sendo uma das 
-- principais, a criaçao de indices que minimiza a quantidade de leituras realizadas pelo SQL Server.

-- A tabela users está ordenada por um campo chamado ID que é um cluster index e uma Primary Key, ou seja
-- a tabela está ordenada fisicamente por este campo. Náo precisamos dar order by no select para ordenar.

select * from Users -- logical reads 7405, 11 segundos
go

select * from Users order by Id asc  -- logical reads 7405, 11 segundos
go

select * from Users order by Id desc -- logical reads 7405, 11 segundos
go

-- Nenhuma alteracao na quantidade de paginas lidas e no tempo 

-- Agora, vamos executar o mesmo comando mas ordenando por um campo
-- que nao tem indice criado e vamos ver se aparece algum operador
-- novo e o custo

select * from Users order by downvotes asc -- logical reads 7405, 13 segundos
go

-- CLUSTER Index NAO TEM APENAS A COLUNA ID, MAS NA VERDADE
-- TEM TODAS AS COLUNAS. No caso da tabela USERS apenas uma coluna
-- fica em outra estrutura a parte (outra 8k page e ira fazer um link), 
-- AboutME por causa do tipo de dados
-- ser um nvarchar(max) que o SQL SERVER pode armazenar ate 2gb de dados por linha.

-- Agora Ligue o atual plano de execucao utilizado e execute os codigos abaixo
select * from Users

-- A leitura deve ser feita da direita para a esquerda no sentido das seta
-- Veja que utilizou o cluster index scan (clustered), e pela quantidade de leitura
-- efetiva executada leu a tabela toda, ou seja 299398 de uma estimativa de 299398
-- A relacao quantidade de leituras estimada e real eh muito importante
-- para que o otimizador de querie do sql server use o melhor plano possivel

-- No campo Output List voce consegue verificar todos os dados que serao passados
-- para o proximo operador trabalhar, no caso SELECT.

-- Se parar o mouse sobre a linha vera a quandade de linhas que passou de um operador
-- para o outro operador processar. Quanto mais fino esta seta melhor, porque quer
-- dizer que menos dados estão passando de um operador para o outro. Quanto
-- mais filtrar os dados logo no inicio, melhor, que pode ser feito através de bons
-- modelos de dados, bons codigos sql server, atualizacao de statisticas e por ultimo
-- pela criacao de bons indices.

-- Se parar o mouse sobre o operador SELECT verá o label Estimated subtree Cost
-- que é a soma do custo de IO (leituras) e CPU (ordenacao por exemplo e calculos).

-- Repare que o label degree of paralelism esta 0, isto quer dizer que na instancia
-- esta configurado para usar todos os nucleos da cpu para fazer transacoes em paralelo
-- mas neste caso nao foi feito. Repare que o custo é de cerca de 5%. Verifique
-- o parametro Cost Thersold for Paralelism na instancia. Está configurado
-- para 50, o que quer dizer que o sql server por padrao só irá fazer
-- multiparalelism se os operadores ultrapassarem 50%. Podemos alterar novamente
-- este parametro de forma geral para roda instancia ou alterar no codigo e rodar.

select * from Users order by downvotes asc -- veja os segundos
option (maxdop 0)
go

-- Vejo o plano de execucao, clique no operador Sort
-- e veja as informacoes na propriedade Actual number of Rows
-- Repare na quantidade de linhas que cada thread leu.
-- E repare na propriedade memory usage dos threads

-- Agora rode sem paralelismo
select * from Users order by downvotes asc -- veja os segundos
option (maxdop 1)
go

-- Repare que neste caso, a nao utilizacao de processamento em paralelo é
-- muito mais rápida, portanto cuidado com esta opcao que vem como padrao no 
-- sql server, degree of paralelism = 0

SET STATISTICS IO ON
SELECT ID FROM users where lastaccessdate > '2014-06-02'

-- O que teria que ser feito, teria que ser toda a tabela, e anotando em um
-- papel os id que tem a data lastaccessdate maior que '2014-06-02', TODA A TABELA.

-- Repare que mesmo trazendo apenas 149029 linhas, em messages ainda teve que ler
-- todas as paginas da tabela, ou seja, logical reads 7405.
-- Veja no plano, no operador Clustered Index Scan.

-- Vamos colocar agora para rodar estes dois codigos e vamos ver o custo
-- e a quantidade de paginas lidas

select * from Users
go
SELECT ID FROM users where lastaccessdate > '2014-06-02' option (maxdop 0)
go

-- O custo e e a quantidade de paginas lidas sera um pouco maior mesmo com apenas
-- o campo id e filtrando, porque ainda assim teve que ler toda as linhas
-- ja que nao existe indice pelo campo lastaccessdate ainda. Este custo e leitura
-- foi devido a utilizacao de multi threads que fez leituras em paralelo e teve custo
-- maior.

-- AGORA VAMOS IMAGINAR QUE QUEREMOS RECEBER A LISTA DOS ID ORDENADOS PELO CAMPO LASTACESSDATE, MAS 
-- IMAGINA SE O SQL SERVER FOSSE UM SER HUMADO E TIVESSE QUE FAZER ESTE TRABALHO. ELE TERIA QUE LER TODA A TABELA E TERIA QUE ANOTAR EM UM PAPEL
-- A PARTE TODOS OS IDS, lastaccessdate, e teria que ordenar a lista dos IDS pelo lastaccessdate .
SELECT ID, lastaccessdate  FROM users where lastaccessdate > '2014-06-02' 
order by lastaccessdate 
go

-- Vamos verificar o actual execute plan e verificar o que o otimizador de querie do sql server fez.

-- Curiosidade: verifique o custo do operador Sort em relacao ao primeiro operador que faz as leituras (cluster index scan)
Vamos verificar o tempo de CPU ligando esta opcao, E DEPOIS RODAR COM E SEM ORDER BY E VEJA O TEMPO DE EXECUCAO E O IMPACTO DE USAR O ORDER BY.

SET STATISTICS TIME ON
GO

SELECT ID, lastaccessdate  FROM users where lastaccessdate > '2014-06-02' 
go


-- E AGORA O QUE ACONTECE SE COLOCAR TOP 100 PARA TRAZER APENAS 100 LINHAS DO TOTAL?
SELECT top 100 ID, lastaccessdate  FROM users where lastaccessdate > '2014-06-02' 
order by lastaccessdate 
go

-- VEJA QUANTAS PAGINAS AINDA LEU E O PLANO. Note que mesmo solicitando para trazer apenas 100 linhas, o sql server ainda teve que ler toda a tabela 
-- para achar lastaccessdate > '2014-06-02', ENTRETANTO UMA REDUCAO DO CUSTO DE CPU QUE NAO TEVE QUE ORDENAR OS QUASE 300.000 REGISTROS MAS APENAS 100 
-- E AI O TEMPO DE UTILIZACAO DA CPU CAIU BASTANTE.

-- Agora vamos rodar estes duas queries e vamos verificar o custo devido ao uso do * e principalmente pelo order by

set statistics io, time on
go

SELECT ID FROM users where lastaccessdate > '2014-06-02' 
order by lastaccessdate 
go

SELECT * FROM users where lastaccessdate > '2014-06-02' 
order by lastaccessdate 
go

-- Vamos agora criar nosso primeiro index
CREATE INDEX Lastaccessdata_idx on users(lastaccessdate, id)
go

-- E vamos executar o comando
select lastaccessdate, id from users order by lastaccessdate, id 
go

-- e vamos ver agora a quantidade de leitura executadas e o novo operador SEEK
SELECT ID FROM users where lastaccessdate > '2014-06-02' 
order by lastaccessdate 
go

-- E vamos simular este estes selects
SELECT Id, LastAccessDate,age, aboutme FROM users 
where lastaccessdate > '2014-06-02' 
go

SELECT Id, LastAccessDate,age, aboutme FROM users 
where lastaccessdate > '2014-06-02' 
order by lastaccessdate 
go

SELECT Id, LastAccessDate,age, aboutme FROM users WITH (INDEX (PK_Users_id))
where lastaccessdate > '2014-06-02' 
order by lastaccessdate 

-- e estes

sp_helpindex users

SELECT Id, LastAccessDate FROM users 
where 
Id = 17 and
lastaccessdate = '2018-01-22 01:35:37.993' 

go

drop index Lastaccessdata_idx on users
go

create index Lastaccessdata_idx on users(id, lastaccessdate, age)
go

SELECT Id, LastAccessDate FROM users 
where 
lastaccessdate = '2018-01-22 01:35:37.993' 
go

SELECT Id FROM users 
order by lastaccessdate
go

SELECT Id, LastAccessDate FROM users 
where 
Id = 17
--and lastaccessdate = '2018-01-22 01:35:37.993' 
go

-- VAMOS FAZER MAIS ESTE LABORATORIO AGORA E VAMOS VER A DIFERENCA DO PLANO DE EXECUCAO UTILIZADA PELO SQL SERVER PARA RESOLVER ESTAS QUERIES

set statistics io on
go

SELECT Id, displayname, age, LastAccessDate FROM users 
where lastaccessdate between '2018-08-27' and '2018-08-28'
order by lastaccessdate

SELECT Id, displayname, age, LastAccessDate FROM users 
where CAST(lastaccessdate AS date) = '2018-08-27'
order by lastaccessdate

sp_helpindex users
go

-- Vamos criar um indice pelo campo lastaccessdate
create index newidslastacessdate on users(lastaccessdate)

-- e vamos rodar o codigo novamente e ver o numero estimado e linhasX
-- numero real de linhas
SELECT Id, displayname, age, LastAccessDate FROM users 
where CAST(lastaccessdate AS date) = '2018-08-27'
order by lastaccessdate


-- e vamos agora rodar o mesmo codigo mas com outro periodo de tempo
SELECT Id, displayname, age, LastAccessDate FROM users 
where lastaccessdate between '2018-08-31' and '2018-09-01'
order by lastaccessdate

SELECT Id, displayname, age, LastAccessDate FROM users 
where CAST(lastaccessdate AS date) = '2018-08-31'
order by lastaccessdate

-- Vamos para um outro LAB

set statistics io on

SELECT Id, displayname, age, LastAccessDate FROM users 
where CAST(lastaccessdate AS date) = '2018-08-27'
order by lastaccessdate

  index newidslastacessdate2 on users(lastaccessdate)
include(displayname, age)
go

SELECT Id, displayname, age, LastAccessDate FROM users 
where CAST(lastaccessdate AS date) = '2018-08-27'
order by lastaccessdate

use [StackOverflow2010]
go
exec sp_BlitzIndex @tablename = 'users' -- https://www.brentozar.com/blitzindex/
go