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

PERFORMANCE: PLAN GUIDES
==================================================================================*/

-- VAMOS AO LAB

-- CRIAR ESTA PROCEDURE

USE [AuditoriaDBA]
GO
DROP PROCEDURE IF EXISTS DadosUmaCidadeNOVO
GO
CREATE PROCEDURE DadosUmaCidadeNOVO @qualcidade nchar(30) as
select * from NOindex where cidade = @qualcidade and (Numero >= 1 and numero < 10000000000000) and @qualcidade <> 'guarapari'
GO

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

-- CRIAR INDICE NONCLUSTER PELO CAMPO REGISTRO

CREATE NONCLUSTERED INDEX [NonClusteredIndex-20210501-032329] ON [dbo].[NOindex]
([Registro] ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = ON)
GO

select top 10 * from noindex order by registro
go

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

-- Rode este codigo e veja os tipos de planos usados e a quantidade de 
-- leituras realizadas. Repare que já houve um problema
-- de Parameter Sniffing na segunda execucao, por que o sql server usou o
-- mesmo plano que foi usado na primeira execucao, com o operador seek com key lookup.
-- DEMONSTRE O PLANO DE EXECUCAO QUE SERÁ USADO 

set statistics io on
DBCC FREEPROCCACHE --VAMOS TIRAR O PLANO ATUAL DO CACHE
GO

EXEC SP_EXECUTESQL N'select * from NOindex where (registro >= @P1 and registro < @P2) and cidade = @P3', N'@P1 bigint, @P2 bigint, @P3 nchar(30)', @P1 = 1, @P2 = 4990, @P3 = 'VITORIA'
go
EXEC SP_EXECUTESQL N'select * from NOindex where (registro >= @P1 and registro < @P2) and cidade = @P3', N'@P1 bigint, @P2 bigint, @P3 nchar(30)', @P1 = 1, @P2 = 4990000000000000000, @P3 = 'VITORIA'
go

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

-- Rode este codigo (*) em outra sessao para ver o que rodou e esta no plan cache
-- RODE EM OUTRA SESSAO
SELECT
  (SELECT cast(st.text as nvarchar(max)) FOR XML PATH(''), TYPE) AS [TSQL],
  qs.execution_count AS [#],
  CAST(qs.total_worker_time/1000./1000. AS numeric(30,1)) AS [cpu sec],
  CASE WHEN execution_count = 0 THEN 0 ELSE
       CAST(qs.total_worker_time / execution_count / 1000. / 1000. AS numeric(30,1))
       END AS [avg cpu sec],
  qs.total_logical_reads as [logical reads],
  CASE WHEN execution_count = 0 THEN 0 ELSE
       CAST(qs.total_logical_reads / execution_count AS numeric(30,1))
  END AS [avg logical reads],
  qp.query_plan as [plan]
FROM sys.dm_exec_query_stats AS qs
OUTER APPLY sys.dm_exec_sql_text (plan_handle) as st
OUTER APPLY sys.dm_exec_query_plan (plan_handle) as qp
WHERE st.text like '%NOindex%'
     OPTION (RECOMPILE)
GO 

-- Se não conseguir pegar o comando com o sql acima, pode usar esta sp_blitz em outra sessão.
sp_BlitzWho @expertmode = 1 
-- Na coluna query_text, verificar o comando e as variaveis 

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

-- AGORA VEJA, SE COLOCAR O RECOMPILE NA CHAMADA, NEM PRECISA LIMPAR O CACHE
-- REPARE QUE AGORA COLOCANDO A OPTION RECOMPILE ACABOU O PROBLEMA
-- DE PARAMETER SNIFFING

set statistics io on

EXEC SP_EXECUTESQL N'select * from NOindex where (registro >= @P1 and registro < @P2) and cidade = @P3 OPTION(RECOMPILE)', N'@P1 bigint, @P2 bigint, @P3 nchar(30)', @P1 = 1, @P2 = 4990, @P3 = 'VITORIA'
go
EXEC SP_EXECUTESQL N'select * from NOindex where (registro >= @P1 and registro < @P2) and cidade = @P3 OPTION(RECOMPILE)', N'@P1 bigint, @P2 bigint, @P3 nchar(30)', @P1 = 1, @P2 = 4990000000000000000, @P3 = 'VITORIA'
go

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

----- AGORA, COMO RESOLVER ESTE PROBLEMA SE VOCE NAO CONSEGUE MUDAR O CODIGO FONTE DA APLICACAO
----- PARA COLOCAR O OPTION (RECOMPILE) OU OUTRO TIPO DE OPTION
----- OU REMOVER ALGUM TIPO DE OPTION PREJUDICIAL ?

----- AQUI ENTRA O PLAN GUIDE !!!!

/*===
Plan Guide parametros

@Name: O nome que você escolher para o plan guide, pode ser encontrado pesquisando em sys.plan_guides
@stmt: Toda a instrucao SELECT
@Type: PRINCIPAIS TIPOS
       OBJECT (NOME DA stored procedure, function, trigger, tvf, svf)
       SQL (SELECTS) 
@module_or_batch: Pode ser NULL se o TYPE for SQL ou pode colocar o nome do OBJECT (exemplo nome de uma stored procedure)
@params = Parametros exatos colocados na querie.
@hints= Os hints que voce quer adicionar na querie ou remover
======*/

-- Drop the plan guide 
EXEC sp_control_plan_guide N'DROP', N'PLAN_GUIDE_CLIENTE1';  
GO  

-- PEGUE O SELECT DA QUERIE (*), REMOVA AS VARIAVEIS DA QUERIE E COLOQUE O CODIGO TODO NO @STMT
sp_create_plan_guide   
@name = N'PLAN_GUIDE_CLIENTE1',   
@stmt = N'select * from NOindex where (registro >= @P1 and registro < @P2) and cidade = @P3', -- o comando precisa ser 100% igual, inclusive espaços e tamanho das letras
@type = N'SQL',  
@module_or_batch = NULL,   
@params = '@P1 bigint, @P2 bigint, @P3 nchar(30)', -- os parametros precisam ser 100% iguais
@hints = N'OPTION (RECOMPILE)';
GO

-- RODE NOVAMENTE A CHAMADA DA PROCEDURE SEM OPTION (RECOMPILE) E VEJA SE AGORA O PROBLEMA DE
-- PARAMETER SNIFFING ACABOU

DBCC FREEPROCCACHE --VAMOS TIRAR O PLANO ATUAL DO CACHE
GO
EXEC SP_EXECUTESQL N'select * from NOindex where (registro >= @P1 and registro < @P2) and cidade = @P3', N'@P1 bigint, @P2 bigint, @P3 nchar(30)', @P1 = 1, @P2 = 4990, @P3 = 'VITORIA'
go
EXEC SP_EXECUTESQL N'select * from NOindex where (registro >= @P1 and registro < @P2) and cidade = @P3', N'@P1 bigint, @P2 bigint, @P3 nchar(30)', @P1 = 1, @P2 = 4990000000000000000, @P3 = 'VITORIA'
go

-- Vamos deletar o plan guide criado e rodar novamente a chamada dos 2 procedimentos para comprovar que o 
-- problema retornou

EXEC sp_control_plan_guide N'DROP', N'PLAN_GUIDE_CLIENTE1';  
GO 

DBCC FREEPROCCACHE --VAMOS TIRAR O PLANO ATUAL DO CACHE
GO
EXEC SP_EXECUTESQL N'select * from NOindex where (registro >= @P1 and registro < @P2) and cidade = @P3', N'@P1 bigint, @P2 bigint, @P3 nchar(30)', @P1 = 1, @P2 = 4990, @P3 = 'VITORIA'
go
EXEC SP_EXECUTESQL N'select * from NOindex where (registro >= @P1 and registro < @P2) and cidade = @P3', N'@P1 bigint, @P2 bigint, @P3 nchar(30)', @P1 = 1, @P2 = 4990000000000000000, @P3 = 'VITORIA'
go

-- obs: Quando tiver variveis com aspas simples com erro, precisa colocar '', exemplo = 'x', trocar por = ''x''
-- e variaveis @gt tem que trocar por > e @lt tem que trocar por <
-- Se estiver assim &lt;&gt; 0 ficará dentro deste exemplo assim: <> 0.

---FIM LAB -----------------------------------------------------------------------------------------------------------------------------------------------------------------