/*==================================================================================
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: OPTIONS HINTS (DICAS)
==================================================================================*/

-- VAMOS AO LAB

-- Vamos ligar a opcao para ser demonstrado o plano utilizado pelo SQL SERVER

use AuditoriaDBA
go

update statistics NOindex with fullscan
go
set statistics io, time on
go

-- PADRAO DA MS (nivel de instancia SQL SERVER)
-- Cost Threshold for Paralellism = 5
-- Max Degree of Paraellismo = 0

-- COM ESTA CONFIGURACAO PRATICAMENTE TODOS OS COMANDOS SQL PODERAO
-- LEVAR O OTIMIZADOR DE QUERIES DO SQL SERVER UTILIZAR PARALELISMO USANDO TODOS 
-- OS NUCLEOS DA CPU, O QUE EM MUITOS CASOS TRAZ PROBLEMAS DE PERFORMANCE
-- PARA O AMBIENTE.

DBCC FREEPROCCACHE --VAMOS TIRAR O PLANO ATUAL DO CACHE
GO
SELECT  [Numero],[cidade], COUNT(numero), SUM(numero)    
  FROM [AuditoriaDBA].[dbo].[NOindex] 
  where 
  --(cidade <> 'porto alegre') or substring(cidade,1,7) = 'vitoria' 
  substring(cidade,1,12) = 'vitoria' or (cidade <> 'porto alegre')
  or cidade = 'porto alegre'
  and 
  (Numero >= 1 and numero < 10000000000000) 
 group by Numero, cidade
 having SUM(numero) > 100
 order by numero, cidade
GO
-- CPU TIME> 

-- ATRAVES DA UTILIZACAO DO OPTION NA QUERIE
-- INDICAMOS PARA O OTIMIZADOR DE QUERIE NAO
-- UTILIZAR PROCESSOS EM PARALELO.

DBCC FREEPROCCACHE --VAMOS TIRAR O PLANO ATUAL DO CACHE
GO
SELECT  [Numero],[cidade], COUNT(numero), SUM(numero)    
  FROM [AuditoriaDBA].[dbo].[NOindex] 
  where 
  --(cidade <> 'porto alegre') or substring(cidade,1,7) = 'vitoria' 
  substring(cidade,1,12) = 'vitoria' or (cidade <> 'porto alegre')
  or cidade = 'porto alegre'
  and 
  (Numero >= 1 and numero < 10000000000000) 
 group by Numero, cidade
 having SUM(numero) > 100
 order by numero, cidade
option (maxdop 1)
-- CPU TIME> 
GO

-- AGORA IMAGIME UMA SITUACAO QUE O DBA MUDOU TODO O PADRAO
-- DA INSTANCIA SQL SERVER PARA PERMITIR A OPCAO
-- DE PROCESSAMENTO EM PARALELO, DESDE QUE O CUSTO FOR MUITO ALTO

-- VAMOS MUDAR O PADRAO DA MS (nivel de instancia SQL SERVER)
-- Cost Threshold for Paralellism = 32767  -- maior valor possivel
-- Max Degree of Paraellismo = 0

-- Como neste caso, o custo das queries nao foram tao altos, mesmo dando a opcao
-- de utilizar processamento em paralelo, o SQL SERVER NAO VAI UTILIZAR ESTE RECURSO

DBCC FREEPROCCACHE --VAMOS TIRAR O PLANO ATUAL DO CACHE
GO
SELECT  [Numero],[cidade], COUNT(numero), SUM(numero)    
  FROM [AuditoriaDBA].[dbo].[NOindex] 
  where 
  --(cidade <> 'porto alegre') or substring(cidade,1,7) = 'vitoria' 
  substring(cidade,1,12) = 'vitoria' or (cidade <> 'porto alegre')
  or cidade = 'porto alegre'
  and 
  (Numero >= 1 and numero < 10000000000000) 
 group by Numero, cidade
 having SUM(numero) > 100
 order by numero, cidade
option (maxdop 0)
GO
-- CPU TIME> 

-- Para forçar o otimizador de querie do SQL SERVER para usar o processamento paralelo através dos vários núcleos
-- da CPU utilizamos a Option abaixo.

DBCC FREEPROCCACHE --VAMOS TIRAR O PLANO ATUAL DO CACHE
GO
SELECT  [Numero],[cidade], COUNT(numero), SUM(numero)    
  FROM [AuditoriaDBA].[dbo].[NOindex] 
  where 
  --(cidade <> 'porto alegre') or substring(cidade,1,7) = 'vitoria' 
  substring(cidade,1,12) = 'vitoria' or (cidade <> 'porto alegre')
  or cidade = 'porto alegre'
  and 
  (Numero >= 1 and numero < 10000000000000) 
 group by Numero, cidade
 having SUM(numero) > 100
 order by numero, cidade
 OPTION (USE HINT ('ENABLE_PARALLEL_PLAN_PREFERENCE'))
GO
-- CPU TIME> 

-- Com o option abaixo poderá usar uma versao anterior 
-- do otimizador de querie. Em algumas poucas situacoes
-- para queries especificas o otimizador de querie da 
-- versao anterior do sql server é mais eficiente.

DBCC FREEPROCCACHE --VAMOS TIRAR O PLANO ATUAL DO CACHE
GO
SELECT  [Numero],[cidade], COUNT(numero), SUM(numero)    
  FROM [AuditoriaDBA].[dbo].[NOindex] 
  where 
  --(cidade <> 'porto alegre') or substring(cidade,1,7) = 'vitoria' 
  substring(cidade,1,12) = 'vitoria' or (cidade <> 'porto alegre')
  or cidade = 'porto alegre'
  and 
  (Numero >= 1 and numero < 10000000000000) 
 group by Numero, cidade
 having SUM(numero) > 100
 order by numero, cidade
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'))
GO
-- CPU TIME> 

-- Com este Option o otimizador vai utilizar de todos os meios (inclusive alocando mais memória)
-- para enviar os 100 primeiros registros para a aplicacao, por exemplo
-- para montar a primeira tela de uma grid, mas a performance constuma ser pior para
-- o restante dos dados, então para um report por exemplo pode ser péssimo e muitas aplicacacoes
-- compradas de terceiros ja vem com esta opção configurada em varios SELECTS.

DBCC FREEPROCCACHE --VAMOS TIRAR O PLANO ATUAL DO CACHE
GO
SELECT top 2000 [Numero],[cidade], COUNT(numero), SUM(numero)    
  FROM [AuditoriaDBA].[dbo].[NOindex] 
  where 
  --(cidade <> 'porto alegre') or substring(cidade,1,7) = 'vitoria' 
  substring(cidade,1,12) = 'vitoria' or (cidade <> 'porto alegre')
  or cidade = 'porto alegre'
  and 
  (Numero >= 1 and numero < 10000000000000) 
 group by Numero, cidade
 having SUM(numero) > 100
 order by numero, cidade
OPTION (FAST 100)
GO
-- CPU TIME> 

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

-- COMBINANDO HINTS

DBCC FREEPROCCACHE --VAMOS TIRAR O PLANO ATUAL DO CACHE
GO
SELECT  [Numero],[cidade], COUNT(numero), SUM(numero)    
  FROM [AuditoriaDBA].[dbo].[NOindex] 
  where 
  --(cidade <> 'porto alegre') or substring(cidade,1,7) = 'vitoria' 
  substring(cidade,1,12) = 'vitoria' or (cidade <> 'porto alegre')
  or cidade = 'porto alegre'
  and 
  (Numero >= 1 and numero < 10000000000000) 
 group by Numero, cidade
 having SUM(numero) > 100
 order by numero, cidade
OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION'), maxdop 0, recompile)
GO
-- CPU TIME> 

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

-- EM ALGUMAS MOMENTOS É INTERESSANTE INCLUIR UM OPTION, TROCAR E EM MUITOS CASOS REMOVER
-- MAS COMO FAZER SE NAO TEM O CODIGO FONTE DE UMA APLICACAO QUE FOI COMPRADA DE UM TERCEIROS,
-- MUITAS VEZES ESTANDO O CODIGO SELECT COMPILADO DENTRO DA APLICAÇÃO.

-- AQUI ENTRA O PLAN GUIDE !!!!

-- SEGUE OUTROS EXEMPLOS
https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver15