SQL SERVER - Dicas que todo DEV deveria conhecer part-5

Nesta sequencia de tópicos abordaremos algumas dicas, que todo desenvolvedor deveria ter a oportunidade de conhecer. Todo desenvolvedor que programa em T/SQL, precisa estar atento a algumas "tips" do SQL Server que podem facilitar em seu dia-a-dia na empresa.

Dicas part-1 Transaction Log
Dicas part-2 Variáveis ​​table e JOIN's & Criação de tabelas dentro de stored procedures
Dicas part-3 Keywords performance
Dicas part-4 Ad Hoc Queries (Forced Parameteeterization)

Parte 5 - SET DEADLOCK_PRIORITY

Evite deadlock's para transações mais importantes, com a mínima alteração de código.

Hoje vamos aprender hoje como reduzir o impasse em transações importantes, com a mínima alteração de código. O requisito, não importa o que aconteça, não desejamos que essa transação entre em conflito (bloqueio). Outro requisito importante é, que não podemos alterar a regra de negocio, modificações profundas no código do sistema, sem compreender a lógica de negócios.

Nosso primeiro objetivo é, entender o que está criando o deadlock?
Depois de investigar, sempre descobrimos concorrências simultâneas em uma ou mais tabelas especificas.

De fato, a melhor solução é SEMPRE é reescrever o código para que não exista o deadlock,
mas nem sempre é possível como uma solução imediata.

Isso nos leva a apenas uma solução rápida; Definir a prioridade do deadlock.

1	SET DEADLOCK_PRIORITY HIGH;

Esta instrução quando "setada", especifica a real importância da sessão atual.

Se a sessão estiver em conflito com outra transação. Esta declaração SET certifica-se de que
a transação importante deste conteudo, não entrará em deadlock, porem as outras transações ficam em espera com uma maior frequência.

Neste cenário, o hint descrito nesta postagem reduziu o impasse para a transação importante,
mas não reduziu o número total do bloqueios. Portanto, use a técnica somentos nos casos extremamente especiais, quando não é possivel alteração de codigo, conforme detalhamos anteriormente.

Curiosidade:

• Se ambas as sessões tiverem a mesma prioridade de deadlock, a instância do SQL Server escolhe a sessão que é menos dispendiosa para ser revertida como a vítima de deadlock. Por exemplo, se ambas as sessões tiverem definido sua prioridade de deadlock como HIGH, a instância escolherá como uma vítima a sessão que calcula ser menos dispendiosa para reverter. O custo é determinado comparando o número de bytes de log gravados naquele ponto em cada transação.

Você usa deadlock priority na sua lógica de negócios?
Compartilhe sua experiência nos comentários.

Grande abraços

SQL SERVER - Dicas que todo DEV deveria conhecer part-4

    Nesta sequencia de tópicos abordaremos algumas dicas, que todo desenvolvedor deveria ter a oportunidade de conhecer. Todo desenvolvedor que programa em T/SQL, precisa estar atento a algumas "tips" do SQL Server em seu dia-a-dia na empresa.

Dicas part-1 Transaction Log
Dicas part-2 Variáveis ​​table e JOIN's & Criação de tabelas dentro de stored procedures
Dicas part-3 Keywords performance

Parte 4 - Ad Hoc Queries (Forced Parameterization)

Uma Consulta Ad Hoc é um tipo de consulta SQL em um banco de dados que é criada na hora,
no momento em que surge uma necessidade, a partir de um requisito específico.

Ad hoc é uma expressão em latim que significa “para este propósito“.  Ou seja, a consulta é criada apenas para satisfazer aquela necessidade específica, aquele propósito, em um momento específico.

(Que não seja generalista, Que não seja utilizável em mais de um caso) e que não é salva no cache do SGBD como por exemplo fazemos com estored procedures, funções ou scripts, para que sejam reutilizados posteriormente.


É interessante notar que em ambientes onde queries Ad Hoc representam um percentual considerável do workload total,  quantidade de compilações pode se tornar um problema, independente do tamanho do servidor.

O motivo é simples, o SQL Server, por padrão, produz diferentes planos de execução para cada query Ad Hoc

Por exemplo:

Query 1

SELECT ColunaA, ColunaB, ColunaC FROM Tabela WHERE ColunaA = 'X'

Aqui o SQL compilaria e guardaria no cache o plano de execução utilizado para encontrar registros onde o valor = X.

Query 2

SELECT ColunaA, ColunaB, ColunaC FROM Tabela WHERE ColunaA = 'Y'

Bem, numa execução parametrizada (Uma stored procedure, por exemplo), o SQL Server simplesmente poderia reutilizar o plano da primeira query já que a mudança foi mínima (apenas o valor utilizado no filtro), porém a engine relacional entende como uma nova query, ou seja,
durante o processo de otimização da query o Query Optmizer recebe um cache miss, que basicamente indica que não há um plano de execução existente para esta query e ele então é responsável por gerar e armazenar esse novo plano no cache, mesmo sendo praticamente igual ao plano anterior.

Quais opções eu tenho para diminuir o número de compilações?

Bem, se você acredita que a grande maioria das suas queries utilizam planos similares para serem executadas, Não faça o simplista SELECT * FROM puro, você poderia utilizar a opção system stored procedure sp_executesql. Quando utilizada, você está basicamente forçando a parametrização da query especifica.


** Por complexidade, estamos detalhando aos desenvolvedores apenas o Forced Parameterization, sem a necessidade de ativar o "optimize for ad hoc workloads" por Database, que ficaria sob responsabilidade mais detalhadas dos encarregados pela Base de dados (DBA's).

Por exemplo:

 EXECUTE sp_executesql  N'SELECT ColunaA, ColunaB, ColunaC FROM Tabela WHERE ColunaA = @valor', N'@valor varchar(1)', @valor = 'X'; 

Nesta execução, se observarmos o plano armazenado em cache seria algo mais ou menos assim:

SELECT ColunaA, ColunaB, ColunaC FROM Tabela WHERE ColunaA = @valor.

Ou seja, não há um valor específico armazenado junto ao plano, o que faz com que a próxima execução, independente do parâmetro utilizado, encontre um plano satisfatório já armazenado em cache.

Há uma pequena linha entre essa opção ser boa ou não para um determinado ambiente,
já que um plano pode ser realmente bom para várias queries e aí reduzir o número de compilações e consequentemente o tempo de CPU (e memória consumida) ou impactar várias das queries porque cada uma gosta de um plano específico tudo uma questão de Testar.

Boa sorte, uma reflexão por Jack Li;

"If things don’t work out, it’s easy to back it out. Over the course of troubleshooting performance issues, I have used this trick many times." Jack Li


Fique por dentro, e acompanhas essas e outras dicas aqui no blog.
Grande abraços a todos.

SQL SERVER - Dicas que todo DEV deveria conhecer part-3

    Nesta sequencia de tópicos abordaremos algumas dicas, que todo desenvolvedor deveria ter a oportunidade de conhecer. Todo desenvolvedor que programa em T/SQL, precisa estar atento a algumas "tips" do SQL Server em seu dia-a-dia na empresa.

Dicas part-1 Transaction Log
Dicas part-2 Variáveis ​​table e JOIN's & Criação de tabelas dentro de stored procedures

Part-3 Keywords que você deveria esquecer (ao menos evitar ao máximo) rs

SELECT * FROM – Evite asterisco, especifique as colunas necessárias, asterisco deve ser evitado pelo simples motivo de que é muito inútil retornar mais colunas do que o necessário, usando esse recurso. Pense no volume de dados, e aumento exponencial deste. Sendo retornados em toda chamada sem necessidade, é sobrecarregar a base de dados atoa. Outro problema é que, o interpretador SQL deve buscar no esquema da tabela as colunas que deveram de ser retornadas, antes de realizar a consulta propriamente dita.

IN – se você quer retornar os registros cujas condições são múltiplos identificadores, como todos os empregados cujos IDs sejam 1,3,7,45,100, você irá usar um IN, certo?


O problema aqui é que o IN é interpretado pelo motor de busca como uma junção de OR

Ou seja, WHERE ID IN (1,3,7,45,100)
é a mesma coisa que WHERE ID=1 OR ID=3 OR ID=7 OR ID=45 OR ID=100

Isto Não chega a ser um problema em uma consulta com poucas dezenas de valores no IN, mas tome cuidado quando você chega nas centenas deles. Já experimentamos outras abordagens com tabelas temporárias, tabelas em memória, entre outras, com resultados semelhantes. Por estes motivos muitos casos são mais eficientes tratamento na camada de aplicação e filtro, do que a sobrecarga no banco de dados fugindo do uso excessivo de IN.


Bônus: Dica rápida ...

Qual instrução é melhor para o desempenho - SELECT ou SET? SQL Server
Confere lá https://scriptsemsql.blogspot.com/2018/03/qual-e-melhor-para-o-desempenho-select.html

Fique por dentro, acompanhe essas e outras dicas aqui no blog.
Grande abraço.

SQL SERVER - Dicas que todo DEV deveria conhecer part-2

    Nesta sequencia de tópicos abordaremos algumas dicas, que todo desenvolvedor deveria ter a oportunidade de conhecer. Todo desenvolvedor que programa em T/SQL, precisa estar atento a algumas "tips" do SQL Server em seu dia-a-dia na empresa.

Part-2 Variáveis ​​table e JOIN's (DECLARE @table_variable_name TABLE)

Não use variáveis ​​de tabela em conjunto com JOIN. Use tabelas temporárias, CTEs (Common Table Expressions) em JOIN.


Embora as variáveis ​​table sejam muito rápidas e eficientes em muitas situações, o mecanismo do SQL Server a vê como uma única linha. Devido a isso, eles apresentam um desempenho horrível quando usados ​​em JOIN's. Tabelas temporárias apresentam melhor desempenho com JOIN's em comparação com as variáveis ​​da tabela.

Dica complementar: Criação de tabelas dentro de stored procedures? Cuidado!

Quando uma tabela é criada e utilizada dentro de uma mesma stored procedure, o otimizador não tem conhecimento das suas estatísticas, e assume que esta tabela tem 100 linhas e 10 páginas. Se a tabela criada é muito grande, esta suposição pode levar o otimizador a calcular um plano de acesso não otimizado / Errado. Para evitar este problema, crie a tabela em uma rotina anterior e utilize-a em outra.

Variáveis Locais ou Parâmetros na cláusula WHERE ? 

O otimizador não tem informações sobre o valor de uma variável, mas, em tempo de compilação, sabe o valor de um parâmetro. Isso posto, a utilização de parâmetros em cláusula where, leva o otimizador a produzir um plano de acesso mais eficiente.

mais recente, detalhamos esse hint no topico especifico: Dicas que todo DEV deveria conhecer
Parte 4 - Ad Hoc Queries (Forced Parameterization)

Exemplo sugestivo, observe que na primeira procedure

a Variavel @x recebe = b1 e logo em sequencia a variavel é passada como filtro WHERE b1 = @x
(O otimizador não tem informações sobre o valor de uma variável)

Na segunda procedure, em duas estapas: @x recebe = b1

Que é passado como parâmetro pra uma segunda procedure Exec s_p2 (@x) --solução
(Que força o otimizador a produzir um plano de acesso mais eficiente)

* Imagine no exemplo, uma tabela (t2) já criada anteriormente no escopo da procedure.

SQL SERVER - Dicas que todo DEV deveria conhecer part-1

    Nesta sequencia de tópicos abordaremos algumas dicas, que todo desenvolvedor deveria ter a oportunidade de conhecer. Todo desenvolvedor que programa em T/SQL, precisa estar atento a algumas "tips" do SQL Server em seu dia-a-dia na empresa.

Serão abordados diversos temas sequenciais, e depois da conclusão listaremos todos em um menu completo aqui.

Part-1 Transaction Log 
Part-2 Variáveis ​​table e JOIN's & Criação de tabelas dentro de stored procedures

Part-1 Transaction Log 

De modo simplista, todos imaginamos que quando executamos algum DML insert, update ou delete.
O SGBD registra em disco Transaction Log (faremos referencia com T-Log, daqui pra frente) para depois efetuar commit em mdf, certo?

Não - Errado.

Neste caminho simplificado, existe um camada de Log Buffer. Uma região em memoria responsável pelo buffer das informações, que somente apos check irá "commitar" esses dados em memoria para disco log. De maneira simplificada, esse é o WAIT mais comum relacionado ao T-Log.
Ele está relacionado ao tempo que SQL Server está esperando para escrever no disco.

Como podemos otimizar essa escrita ?

Nossa primeira Dica, neste tópico será a otimização do Transaction log, imaginem um loop de 10mil transações:

Já imaginou o trabalho que o SQL Server teria para abrir e fechar 10mil transações em loop?
Então imagine como seria muito mais simples, fazer tudo em uma única transação!

Padrão:

WHILE @id < 10000

   BEGIN

    INSERT INTO TABELA_TESTE VALUES(...)

   END

Melhoria:

BEGIN TRAN

   WHILE @id < 10000

   BEGIN

    INSERT INTO TABELA_TESTE VALUES(...)

   END

COMMIT

*

Por favor não confundam transaction begin tran  / while if begin de blocos de comando ok

Essa falha é muito comum em select into, update from também, onde indiferente da transação 

Já existiria um tratamento de erro no final If (@@Error > 0). O que torna totalmente adaptável o controle transacional no bloco de repetições.

No exemplo nosso tempo de execução baixou de forma considerável de mais de 2 minutos para 45 segundos! Ou seja, foi reduzido mais da metade do tempo! Essa melhora ocorreu por que o SQL Server processou as 10 mil inserções em uma única transação. Nem sempre é possível realizar essa otimização, mas ela pode ser uma boa solução.