/*==================================================================================
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: PARAMETER SNIFFING
==================================================================================*/

-- O QUE É PARAMETER SNIFFING?

-- VAMOS REALIZAR UM LAB QUE IRÁ ENTENDER...

-- Antes, vamos verificar nossa tabela NoIndex

Use auditoriaDBA
GO
SELECT TOP 1000 * FROM NOIndex
SELECT COUNT(*) FROM NOindex
GO

-- Agora vamos inserir mais alguns dados nesta tabela
Declare @Cont Int = 1
While @Cont <=4990
Begin
   Insert Into NoIndex (Numero,nomecliente,endereco, cidade, estado, pais )
   Values (Rand()*100000000000,'Joao','rua teste 666','Porto Alegre','Rio Grande do Sul','brasil')
 Set @Cont +=1
End
Go
SELECT COUNT(*) FROM NOindex
GO

-- E Vamos alterar todas as cidades diferentes de Porto Alegre para Vitoria
UPDATE Noindex
SET cidade = 'vitoria'
where cidade <> 'Porto Alegre'
go

-- Vamos criar um indice novo nonclustered na tabela Noindex de forma grafica pelo SSMS,
-- pelo campo cidade.

-- Agora ligue a funcionalidade para o SQL SERVER mostrar o plano de execuçao e rode os seguintes comandos

Use auditoriaDBA
GO

DBCC FREEPROCCACHE --VAMOS TIRAR O PLANO ATUAL DO CACHE
GO

select * from NOindex where cidade <> 'Porto Alegre'
go
select * from NOindex where cidade = 'Porto Alegre'
go

-- Veja quantas linhas cada querie trouxe e no plano repare no custo de cada uma delas.

-- Repare que na primeira querie o otimizar preferiu usar o operador scan porque a qt
-- de dados com dados diferentes da cidade Porto Alegre foi muito grande e se fosse usar seek, teria que usar o
-- o key lookup junto, porque eu quiz trazer todos os campos no select, 
-- e com seek teria que fazer 1 milhao de leituras no index clustered, para recuperar dados das outras 
-- colunas.

-- Vamos rodar novamente mas verificando quantas paginas de dados cada querie esta lendo.

Use auditoriaDBA
GO
SET STATISTICS IO ON
GO

select * from NOindex where cidade <> 'Porto Alegre'
go
select * from NOindex where cidade = 'Porto Alegre'
go

-- Existem muito menos cidade Porto Alegre mas o motivo que ainda assim teve que ler
-- muitas paginas foi devido ao operador key lookup.

-- AGORA VAMOS CRIAR UMA STORED PROCEDURE

DROP PROCEDURE IF EXISTS DadosUmaCidade
GO
CREATE PROCEDURE DadosUmaCidade @qualcidade nchar(30) as
select * from NOindex where cidade = @qualcidade 
GO
-- Coloquei o mesmo codigo, mas agora dentro de uma SP, que irá
-- receber como parametro para pesquisa a cidade

-- VAMOS LIGAR A FUNCIONALIDADE PARA DEMONSTRAR QUAL PLANO O SQL SERVER VAI USAR
-- PARA RODAR AS DUAS CHAMADAS DA STORE PROCEDURE. 
-- VAMOS RODAR AS DUAS JUNTAS.

SET STATISTICS IO ON
GO

exec DadosUmaCidade @qualcidade = 'Vitoria'
go
exec DadosUmaCidade @qualcidade = 'Porto Alegre'
go

-- VEJA NAS DUAS QUERIES O TOTAL DE PAGE READS. 
-- Foi usado o Operador Clustered Index Scan nas duas queries, por que?

-- Agora passando parametros diferentes, que antes sem a utilizacao de uma procedure, o sql server
-- usava planos diferentes de acordo com a pesquisa, em uma execucao de uma stored procedure
-- o sql server usa o mesmo plano para os dois parametros.
-- O Operador CLUSTER INDEX SCAN esta sendo usado nas duas consultas

-- A PRIMEIRA VEZ QUE UMA Stored Procedure É EXECUTADA, O SQL SERVER COMPILA A STORED PROCEDURE
-- E ESTIMA QUANTAS LINHAS VAI RETORNAR, BASEADO NAS ESTATISTICAS E NESTE CASO, TAMBEM BASEADO
-- NA MEMÓRIA DISPONIVEL, JA ALOCA A MEMÓRIA NECESSÁRIA PARA RODAR A QUERIE, O PLANO A SER USADO
-- COM O TIPO DE OPERADOR (SEEK OR SCAN POR EXEMPLO), SE VAI USAR MULTI PROCESSAMENTO, ETC E 
-- ISTO FICA EM CACHE (MEMÓRIA) E ASSIM TODA A VEZ QUE ESTA MESMA SP RODAR, NAO IMPORTA OS PARAMETROS 
-- PASSADOS, O SQL SERVER PARA NAO PERDER TEMPO PARA COMPILAR NOVAMENTE (TEMPO DE CPU) VAI USAR O MESMO PLANO. 
-- SE SUA BASE DE DADOS TEM NESTA COLUNA DE BUSCA UMA QUANTIDADE DE DADOS PARECIDA EM TERMOS DE QUANTIDADE, 
-- O SQL SERVER VAI SEMPRE RODAR COM TEMPOS PROXIMOS, AGORA, SE A BASE DE DADOS
-- TEM DADOS EM QT MUITO DIFERENTES NESTA COLUNA, UMA HORA VAI RODAR RAPIDO E OUTRA HORA VAI RODAR LENTO, E AS
-- VEZES, MUITO LENTO.
-- COM CERTEZA JA DEVE TER OUVIDO RECLAMAÇÕES DO CLIENTE OU MESMO DO DEV QUE DESENVOLVEU A APLICAÇÃO QUE ESTÁ
-- USANDO O SQL SERVER QIUE NAO ENTENDE PORQUE UMA HORA RODA RÁPIDO UM RELATÓRIO
-- OU UMA CONSULTA, UPDATE, UM DELETE, ETC E OUTRA HORA DEMORA TANTO TEMPO.

-- AGORA VAMOS MUDAR A ORDER DA EXECUCAO E VAMOS FAZER COM QUE A PRIMEIRA A SER EXECUTADA SEJA 
-- A QUE O SQL SERVER VAI COMPILAR O PLANO COM SEEK E KEY LOOKUP E VAMOS VER 
-- A QUANTIDADE DE PAGINAS LIDAS E O NOVO PLANO.

DBCC FREEPROCCACHE --VAMOS TIRAR O PLANO ATUAL DO CACHE
GO

SET STATISTICS IO ON

exec DadosUmaCidade @qualcidade = 'Porto Alegre'
go
exec DadosUmaCidade @qualcidade = 'Vitoria' -- REPARE O NUMERO ESTIMADO DE EXECUCOES NO KEY LOOKUP E O EFETIVO
go

-- VERIFIQUE O NUMERO DE PAGINAS LIDAS AGORA EM MESSAGES E NO PLANO A QT DE EXECUÇOES
-- QUE O OPERADOR KEY LOOKUP TEVE QUE FAZER NA SEGUNDA QUERIE, EM RELACAO AO QUE ELE ESTIMOU.

-- DEVIDO A ESTE NOVO PARAMETRO PASSADO E UM OPERADOR ERRADO, O SQL SERVER
-- VAI GASTAR MAIS CPU PARA REALIZAR A OPERACAO
-- E NESTE MOMENTO PODE IMPACTAR VARIOS OUTROS PROCESSOS QUE ESTÃO RODANDO NESTE MOMENTO.

-- ISTO É PARAMETER SNIFFING!!!!!! 

-- Quando este problema ocorre e precisa tentar resolver rapidamente e não tem acesso a Stored Procedure ou
-- não pode altera-la, pode tentar de forma emergencial, algumas opcoes a fim de verificar se o otimizador
-- de querie remove um plano do cache e aplica outro plano melhor.

-- 1 - remover o plano especifico do cache e assim que a mesma querie rodar novamente, de acordo com o primeiro 
-- parametro a ser passado, o sql server vai aplicar outro plano. Pode dar sorte e ser um plano melhor
-- e resolver a emergencia, mas quando um outro parametro for passado pode voltar a dar o problema.
-- Existe uma sp_blitz do brent ozar que mostra as piores queries que estao no cache e os planos e podemos remover
-- plano especifico.

-- 2 Rodar um update statistics em um indice especifico de uma tabela para retirar o plano do cache.
-- Quando roda update statistics o sql server invalida todos os planos que estao em cache relacionados
-- aquele objeto.

-- 3 E a opcao mais pesada que pode trazer mais perda de performance devido ao tempo de execucao seria
-- um rebuild no indice que esta sendo usado. Nesta hora o SQL SERVER faz um update statistics full scan,
-- mas as opcoes anteriores ja devem resolver a emergencia
-- sem ter que alterar o codigo, mas mais uma vez, não é garantido porque dependendo da proxima chamada
-- da stored procedure com o parametro que ira passar o otimizador continuará a ser ineficiente, porque
-- continuará a usar o plano que está no cache, baseado na primeira compilação.

-- VAMOS TENTAR A ALTERNATIVA MAIS RAPIDA, a OPCAO 1.
-- Vamos rodar 

sp_blitzcache @expertmode = 1
-- https://www.brentozar.com/blitzcache/  -- Pode baixar do site e instalar em sua base de dados de apoio do DBA
-- sp_blitzcache demonstra os queries mais pesadas que estão rodando e o plano que está no cache.

-- Na ultima coluna a direita tem um script que ao rodar o sql server
-- vai remover o plano atual do cache e a proxima vez que o mesmo comando rodar
-- se der sorte de acordo com o parametro passado, o sql pode utilizar um plano melhor
-- MAS ANTES DE REMOVER, SALVE O PLANO ATUAL QUE ESTÁ SENDO UTILIZADO PARA DEPOIS TENTAR
-- DESCOBRIR PORQUE ESTE PLANO ESTÁ COM PROBLEMA. Voce consegue ver o plano atual na coluna
-- Query Plan e salvar o plano em uma pasta do disco.

-- QUERIES QUE RODAM RAPIDO QUANDO PASSA UM DERMINADO PARAMETRO OU CONJUNTO DE PARAMETROS EM UMA 
-- CUNSULTA, RELATORIO, UPDATES COM WHERE, DELETES COM WHERE, E OUTRAS VEZES RODAM MUITO LENTO
-- É UM INDICIO MUITO FORTE QUE ESTA QUERIE PODE ESTAR TENDO PROBLEMAS DE PARAMETER SNIFFING NA CHAMADA
-- DESTAS STORED PROCEDURES.

-- O QUE MUITAS VEZES ACONTECE COM DBAS ou DEVS É TENTAR RECUPERAR O CODIGO DE UMA STORED PROCEDURE
-- E SIMULAR FORA DA STORED PROCEDURE, NO CLIENTE SSMS, MAS QUANDO FAZ ISTO O SQL SERVER NÃO USA
-- O MEMSO PLANO DA STORED PROCEDURE E AI O DBA OU DEV PODE ACHAR QUE DESCOBRIU O PROBLEMA, ALTERANDO
-- O CODIGO NO CLIENTE DO SSMS E SIMULANDO NOVAMENTE OU MESMO CRIANDO UM NOVO INDICE.

-- Pegue a querie que rodou e que teve acesso atraves do sp_blitzchache.
CREATE PROCEDURE DadosUmaCidade @qualcidade nchar(30) as
select * from NOindex where cidade = @qualcidade

-- Muitos devs e dbas pegam esta querie e fazem uma mudança conforme abaixo para testar a querie:

SET STATISTICS IO ON
GO
DECLARE @qualcidade nchar(30) = 'Vitoria'
select * from NOindex where cidade = @qualcidade
-- olhe em message a quantidade de paginas lidas e o plano. 
-- PRONTO, o DBA ou dev
-- acha que consertou o novo codigo, muitas vezes colocando um plan guide (ainda iremos ver),
-- um novo indice, mas na realidade o sql server nao esta usando o mesmo plano da stored procedure.
-- esta usando agora um plano completamete diferente do que a chamada da procedure em producao
-- E o DEV OU DBA acha que agora com este novo codigo a ser alterado na SP irá resolver o problema.
-- Envia para a producao para alterar a SP, e o problema continua ou piora.

-- Vamos confirmar rodando a SP que está com plano em cache para comprovar que o SQL SERVER
-- está usando um plano diferente do novo codigo?
-- OLHE A PLANO E A QUANTIDADE DE PAGINAS LIDAS COM O MESMO PARAMETRO, NADA MUDOU.

SET STATISTICS IO ON
GO
exec DadosUmaCidade @qualcidade = 'vitoria'
go

-- Se quer testar seu novo codigo e quer simular com uma stored procedure mas nao tem permissao de criar 
-- uma stored procedure no servidor, pode criar uma temporaria stored procedure na tempdb, assim poderá
-- comprovar antes de colocar na producao, se o novo codigo, dentro da SP, será efetiva e vai haver melhoria.

-- Rodando o codigo abaixo, iremos criar uma SP na tempdb e depois podemos simular a execução e vai
-- verificar que agora sim está usando o mesmo plano que a SP de produção está usando, ou seja, 
-- voce altera o novo codigo mas mao roda no cliente, coloca dentro de uma temporaria SP na tempdb
-- faz a chamada com o parametro e verifique se houve melhorias efetivas em termos de leitura de paginas e ai sim, pode
-- enviar para homologacao e depois producao.

DROP PROCEDURE IF EXISTS #DadosUmaCidade 
GO
CREATE PROCEDURE #DadosUmaCidade @qualcidade nchar(30) as  -- para criar a SP na TEMPDB crie #nomeprocedure
select * from NOindex where cidade = @qualcidade

-- e para fins de comprovacao, a execute e assim vera o mesmo plano que esta sendo usado na procedure em producao
-- No mundo real, altere seu codigo dentro da temporaria SP e teste.
exec #DadosUmaCidade @qualcidade = 'Porto Alegre'
go
exec #DadosUmaCidade @qualcidade = 'Vitoria'
go

-- NOTA: VEJO MUITAS VEZES DBAS CRIANDO PLAN GUIDES baseado nesta técnica de rodar no cliente
-- e achando que resolveu o problema de uma SP que veio de um fabricante externo, quando na verdade
-- não resolveu e até piorou a execução da SP.

-- AGORA FINALMENTE, COMO PODEMOS CORRIGIR PROBLEMAS DE PARAMETER SNIF?
-- SINCERAMENTE, ATÉ HOJE NÃO ENCONTREI FORMA MAIS EFETIVA E DEFINITIVA DO QUE USAR OPTION (RECOMPILE).

ALTER PROCEDURE DadosUmaCidade @qualcidade nchar(30) 
as  
select * from NOindex where cidade = @qualcidade
option (recompile)  
-- coloque vinculado ao sql que voce quer que seja recompilado e nao em toda SP, 
-- senao vai perder toda rastro de quantas vezes a stored procedure rodou e ainda
-- o sql server vai perder mais tempo compilado uma SP muito complexa com muitos comandos
-- quando muitas vezes quer apenas um determinado sql dentro da SP.

-- Vamos testar novamente e verificar que agora para cada chamado com parametros diferentes
-- o SQL SERVER usou plano diferente, o melhor plano.
SET STATISTICS IO ON
exec DadosUmaCidade @qualcidade = 'Porto Alegre'
go
exec DadosUmaCidade @qualcidade = 'Vitoria' 
go

-- OPTION (RECOMPILE) é muito eficaz principalmente quando não faz chamadas a procedure muitas vezes dentro de 
-- 1 minutos e ainda tem muitas consultas e relatórios ou update/deletes baseados em parametros que muda sempre
-- e acessa tabelas muito grandes. Usando recompile sempre estará usando o melhor plano possivel, mas existe
-- um pequeno gasto em termos de cpu, porque a cada vez que roda, o sql server vai tirar o plano do cache
-- e vai compilar novamente a fim de verificar o melhor plano possivel para todo parametro passado.

-- CUIDADO QUE NAS VERSOES 2008 E 2012 TINHA BUG ja corrigido (cus) com option (recompile) que fazia voce
-- pesquisar cidade vitoria e como resultado ele trazia dados de outra cidade. Verifique se usa esta versão 
-- se ja aplicou todos os CUs.

-- MUITOS DEVS USAM DYNAMIC SQL para montar uma querie a quente com variaveis e mandar para o SQL SERVER.
-- Ja vi muitos relatorios sendo construidos desta forma, onde o usuário vai escolhendo os campos de busca
-- e o programa monta a quente e manda para o servidor. Isto vai evitar o problema de Parameter Snif, mas
-- pode ter problemas de sql injection como ja visto no curso e a cada select novo construido e enviado
-- para o servidor o sql server vai criar um novo plano no cache e vai gastar memória para mante-lo. 
-- Se o seu servidor tem muita memória pode ser uma excelente abordagem, mas muitas vezes, 
-- quando vai olhar os planos na memória, pode ter centenas ou milhares, ao inves de apenas 1 que seria 
-- a SP e assim pode ser que não tenha memória suficiente para guardar mais dados em memória e assim evitar
-- trocas com disco. Cada caso é um caso, mas é sempre uma opção.
-- Vamos simular com sp_blitzcache. Não irei criar dynamic sql mas vamos simular o que ocorre quando varios
-- codigos diferentes são executados


DBCC FREEPROCCACHE --VAMOS TIRAR O PLANO ATUAL DO CACHE
GO

select top 10 * from NOindex where cidade <> 'Porto Alegre'
go
select top 11 * from NOindex where cidade = 'Porto Alegre'
go
select top 1 * from NOindex where cidade = 'VITORIA'
go

sp_blitzcache

-- Agora olhe isto

select top 1 * from NOindex where cidade = 'VITORIA' /* teste */
go
SELECT top 11 * FROM NOindex where cidade = 'Porto Alegre'
go

sp_blitzcache


-- FIM