Retrospectiva 2019: Os posts mais acessados no Blog

     O ano passou e rolou tanta coisa no blog que, resolvemos fazer uma retrospectiva com as matérias que mais bombaram, em 2019. Antes de mais nada, queremos agradecer a todos da comunidade pelo suporte e apoio. Prometemos um 2020 com muitas novidades, informação, conteúdo, ao que nos interessa, os posts que mais acessados no blog em ordem descrente:

# 5

CDC – Change Data Capture SQL Server

# 4

SQL SERVER – Clear Cache plan, para um único banco de dados.

# 3

Confira as últimas atualizações disponíveis para cada versão do SQL Server

# 2

SQL SERVER - Dicas que todo DEV deveria conhecer

#1

E o nosso campeão de acessos em 2019:

Como reduzir o TempDB sem reiniciar o SQL Server ...

Espero tenham gostado e possa ter contribuindo a comunidade de alguma forma neste ano, Esperamos reencontrar todos vocês por aqui em 2020! Happy New Year! ¡Feliz Año Nuevo!

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.

Lançamento - Azure SQL Database Edge (preview)

     Uma pausa em nossos tópicos de " Dicas que todos desenvolvedores deveria conhecer", para falar sobre um novo produto. Em 6 de novembro (hoje rs) na PASS Summit 2019. No primeiro Keynote, Rohan Kumar que é Vice President of Azure Data, apresentou o lançamento público do SQL 2019 e também anunciou o preview do Azure SQL Database Edge, que tem menos de 300mb e pode rodar dentro de um Raspberry PI. Uma nova edição do SQL Server voltada para dispositivos de borda, a Internet das Coisas (IoT). Isso significa que o SQL Server agora pode ser executado em quase qualquer lugar. 

Em primeiro lugar, lembre-se de que o Azure significa "híbrido" agora; portanto, embora possamos esperar que se refira à computação em nuvem, ele também leva em conta a infraestrutura local. Em segundo lugar, esse é o mecanismo de banco de dados completo do SQL Server em execução em uma CPU ARM de 64 bits. Ele poderia ser executado em um Raspberry Pi ou - desde que houvesse suporte para outro hardware - dispositivos Android ou iOS, no entanto, ele é voltado para dispositivos de ponta que coletam dados de sensores IoT e outros pontos de dados. Pense nisso como um passo a partir dos dispositivos IoT ...

Um dos impedimentos mais significativos para aplicativos baseados na nuvem muito exigentes é a latência de fazer vários saltos na espinha dorsal da Internet para o data center do Microsoft Azure ou Amazon AWS. Uma vez lá, os dados são processados ​​e, em seguida, devem ser enviados de volta ao cliente. Esse é um paradigma de processamento de dados muito lento para uma ampla variedade de aplicativos, especialmente aplicativos de IoT, como carros conectados / autônomos ou sistemas de segurança com reconhecimento facial.

Como mencionei anteriormente, é o mecanismo completo do SQL Server. Então, por que não chamá-lo de SQL Server Edge? Isso porque não é apenas o SQL Server. Um recurso solicitado para inclusão no SQL Server por várias pessoas ao longo dos anos é o suporte a séries temporais . Em vez de incorporá-lo ao mecanismo de banco de dados, a Microsoft incorporou o suporte a séries temporais a partir do material de análise que eles já criaram para o Azure. É aí que entra a marca do Azure, porque ela se encaixa perfeitamente na esfera da oferta de serviço da IoT do Azure .

Ainda é uma versão completa do mecanismo de banco de dados do SQL Server (sim, incluindo column store e OLTP na memória), rodando em CPUs ARM de 64 bits, que se beneficiam da redução do uso de energia, da vida útil da bateria e de todos os outros motivos pelos quais gostamos do ARM. É apenas o SQL Server, no Linux, em pequenos computadores que podem ser executados em qualquer lugar. Isso reduz bastante o esforço de criação de software de gerenciamento de dados, porque agora podemos segmentar apenas uma plataforma de dados, desde a borda até a nuvem e tudo mais.

Claro, o SQLite é um banco de dados de usuário único realmente rápido, mas não é o mecanismo completo do SQL Server.

Here is what Azure SQL Database Edge offers:

• Crie uma vez e implante em qualquer lugar, incluindo SQL do Azure

DATABASE, SQL SERVER, AND AZURE SQL DATABASE EDGE

• Movimento bidirecional de dados entre a borda e o local ou a nuvem para um padrão inteligente de armazenamento e encaminhamento

• Segurança forte incorporada ao Banco de Dados SQL do Azure para dados totalmente criptografados em repouso e em movimento em nós de borda e gateways de borda

• Capacidade de combinar fluxo de dados e séries temporais com aprendizado de máquina no banco de dados para permitir análises de baixa latência

• Cenários de borda totalmente desconectados e conectados à nuvem com computação e armazenamento local. Gerenciamento de todo o sistema a partir de um portal de gerenciamento central do Azure IoT.

• Suporte para ferramentas de visualização de dados existentes, como Power BI e Tableau.

• Suporte programático completo para a linguagem T-SQL, bem como suporte analítico usando R, Python, Java e Spark.

• Suporte para processamento e armazenamento de dados de gráficos, JSON e séries temporais no banco de dados, juntamente com a capacidade de aplicar análises e recursos de aprendizado de máquina no banco de dados em tipos de dados não relacionais. Isso permite cenários em que você deseja treinar seus modelos de aprendizado de máquina na nuvem e pontuar no nó de extremidade.

Toda comunidade está extremamente empolgada com a perspectiva desta nova edição do SQL Server. Compartilhe seus pensamentos nos comentários abaixo.

Link Oficial, para maiores informações:
https://azure.microsoft.com/en-us/services/sql-database-edge/

Link Azure SQL Database Edge Whitepaper:
https://azure.microsoft.com/mediahandler/files/resourcefiles/azure-sql-database-edge-whitepaper/White%20Paper-AzureSQLDatabaseEdge-2019.pdf

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.

SQL Server - Pare de usar DBCC DBREINDEX & Use ALTER INDEX

Já faz mais de uma década que a instrução DBCC DBREINDEX foi descontinuado, no entanto, de vez em quando ainda os encontro em alguns clientes. Na semana passada, ao revisar o plano de manutenção de um dos clientes, notamos que eles ainda estão usando a sintaxe mais antiga, em vez de usar a nova sintaxe do ALTER INDEX.

Diga Não ao DBCC DBREINDEX

Microsoft sempre muito clara por anos que, se algum recurso é marcado como obsoleto e o recurso de substituição aparece, é preciso começar a planejar a transição. Realmente não faz sentido continuar usando o recurso que será removido pela equipe do produto nas futuras versões.

No entanto, geralmente recebo um pouco de resistência quando tentamos solicitar aos desenvolvedores ou analistas que usem um novo recurso, em vez do recurso que eles estão usando há muitos anos. Eu entendo totalmente a filosofia de Se não está quebrado, não conserte. 

Mas há muitos motivos para mudar do DBCC DBREINDEX e usar o ALTER INDEX. 
Estas são as três limitações principais do DBCC DBREINDEX.

• Ele não suporta a opção de reconstrução online
• Sem suporte para índices recuperáveis
• Não há suporte para compactação de dados

Não é que ele não suporte apenas as três opções acima, mas muitos outros aprimoramentos desde o lançamento do SQL Server 2008.

Sintaxe do ALTER INDEX

Aqui está a sintaxe do índice ALTER INDEX Rebuilding.

1	ALTER INDEX IndexName ON TableName REBUILD;

É uma sintaxe muito simples. Aqui está outra sintaxe para reorganizar o índice.

1	ALTER INDEX IndexName ON TableName REORGANIZE;

Bem é isso. Esta ainda é a minha pergunta para você - você ainda usa o DBCC DBREINDEX
para recriar seus índices. Existe algum motivo específico para continuar usando o recurso
que foi marcado como obsoleto por tantos anos? Será útil saber o motivo de todos
e podemos compartilhar se você publicar sua resposta como um comentário do blog.

Grande abraços

Referência:  Pinal Dave

SQL SERVER - Opções de energia, planos de energia e desempenho do banco de dados.

     Recentemente acabei por um cenário muito interessante, o servidor de dados estava funcionando perfeitamente por um tempo considerável e não estava enfrentando nenhum problema de desempenho. No entanto, de repente, sem fazer nenhuma alteração, o desempenho do servidor caiu. Eles imediatamente nos acionaram e, começamos sistematicamente a verificar o desempenho do SQL Server e percebemos que eles não tinham problemas de CPU, I/O ou muito menos memória. Além disso, todos os trabalhos de manutenção também estavam funcionando de forma eficiente (jobs noturnos - reindexação - etc). Isso nos levou a finalmente considerar problemas do sistema operacional (Windows Server).

Opções de energia de alto desempenho - Power Option (High performance)

O maior quebra-cabeça à nossa frente era estranho. Tivemos uma query que era executada em 1 segundo e agora leva mais de 8 segundos. Nós também investigamos mais de 10 querys diferentes e descobrimos que todas elas estavam rodando muito lentas.

Como todos os benchmarks de minhas verificações de integridade do SQL Server estavam limpos. Eu decidi olhar para a configuração do Windows. Depois de olhar para o antivírus, abrimos a Opção de Energia para então descobrir que ela foi alterada de Alto Desempenho / Balanceada.

No Windows Server 2008 e superiores, defina o plano de energia " Alto Desempenho " no  Painel de Controle -> Opções de Energia -> OK . Por padrão, o Windows Server define o plano de energia “Equilibrado”, que permite a conservação de energia dimensionando o desempenho do processador com base na utilização atual da CPU.

Se um servidor exigir uma latência ultra baixa, frequência de CPU INvariável ou os níveis de desempenho mais altos, como servidores de banco de dados, talvez não seja útil que os processadores continuem alternando para estados de desempenho inferior (Equilibrado).

Por fim depois que mudamos a opção de energia para o alto desempenho, conseguimos restaurar o desempenho do sistema para um desempenho de referência anterior.

Eu recomendo fortemente que todos que estão executando o SQL Server em servidores gerenciados Windows Server mantenham seu Plano de Energia default como um Plano de "Alto Desempenho" para evitar qualquer degradação de performance não desejada.

Os principais problemas de desempenho no SQL SERVER

Quais foram as causas dos últimos problemas de desempenho de SQL Server, que você enfrentou?

"Como podemos observar, os 2 problemas mais comuns são o código T-SQL e a indexação.
4 dos 6 problemas mais comuns estão todos diretamente relacionados ao T-SQL, índices, código,
e estrutura dos dados. Para obtermos melhorias em desempenho devemos olhar primeiro para área de acesso a dados, incluindo design de banco de dados, design de consulta, e design de índice.

Claro, se considerar a configuração de hardware e atualizações, podemos obter um ganho de desempenho satisfatório. No entanto, uma consulta SQL incorreta enviada pela aplicação pode consumir todos os recursos de hardware disponíveis, não importa o quanto recurso tenha."

Fonte: Grant Fritchey
Grafton, Massachusetts,

SQL Studio Management - Expanding Databases muito lento.

SQL Studio Management - Expanding Databases muito lento.

Em um de nossos clientes durante a implementação, identificamos uma situação atípica,uma performance muito lenta, abaixo do aceitável. Em atividades simples, apenas dentro do SQL Server Studio Management. Notamos que simples ações, como por exemplo de expandir o menu Databases.

Demorou em torno de 2 a 3 minutos, para exibir o nó de Bancos existentes.

Não me lembro exatamente quando começou. Mas eu acho que já fazem vários meses.
Era fácil demais ignorar, já que eu poderia usar o alt-tab e fazer outras atividades enquanto aguardava o atraso.

Hoje decidi pesquisá-lo e concluímos em duas opções:

Opção 1) Ocorre porque alguns bancos têm a opção de auto_close ativado;
Isso faz com que o servidor SQL tenha que inicializar cada banco de dados, antes de poder renderizá-lo no Nó menu de bancos. Isso cria um atraso muito significativo, quando você tem vários bancos de dados configurados para auto_close.

Uma maneira rápida de corrigir todos eles de uma só vez, quando possível ....
É configurar o modo de recuperação para = simple (qualquer coisa mais é inútil no ambiente de desenvolvimento), use este script:

USE MASTER

Declare

    @isql varchar(2000),

    @dbname varchar(64)

    declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb')

    open c1

    fetch next from c1 into @dbname

    While @@fetch_status <> -1

        begin

        select @isql = 'ALTER DATABASE @dbname SET AUTO_CLOSE OFF'

        select @isql = replace(@isql,'@dbname',@dbname)

        print @isql

        exec(@isql)

        select @isql = 'ALTER DATABASE @dbname SET RECOVERY SIMPLE'

        select @isql = replace(@isql,'@dbname',@dbname)

        print @isql

        exec(@isql)

        select @isql='USE @dbname checkpoint'

        select @isql = replace(@isql,'@dbname',@dbname)

        print @isql

        exec(@isql)

        fetch next from c1 into @dbname

        end

    close c1

    deallocate c1

Opção 2) Um configuração pré definida, do proprio SQL Studio gerou problemas não apenas em expandir databases. Mas em todas as ações executadas dentro do Management.

No Management Studio, no Menu (Tools)Ferramentas, selecione (Options) Opções e clique em "Designers".  Há uma opção chamada "Override connection string time-out value for table designer updates:"

Valor de tempo limite, da cadeia de conexão para atualizações de designer de tabela:
Transaction time-out after: modifique para 0 seconds

Este segunda opção foi a mais eficiente em diversos clientes, principalmente em ambiente de produção.Quando não é possível modificações em modo de recuperação. Não há relação entre as opções mas foram alternativas funcionais.

Sugestões e explicações mais detalhadas estamos a disposição.
Fontes referencias: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms190181(v=sql.105)

CHECKDB rodando a cada minuto? SQL-Server

Dias atrás eu me deparei com uma pergunta nos fóruns onde o usuário estava recebendo essa mensagem no log de erro do SQL Server a cada minuto.

CHECKDB for database 'DBName' finished without errors on [date and time].
This is an informational message only; no user action is required.

Starting up database 'DBName'

Ele não agendou o CHECKDB para rodar a cada minuto e queria saber o que significa esta mensagem? Uma rápida olhada na mensagem informativa indica claramente que o SQL Server não está reportando os resultados do DBCC CHECKDB . Essa mensagem é relatada no log de erros sempre que um banco de dados é iniciado. Este é um recurso adicionado no antigo SQL Server 2005 em diante. Na terceira linha na mensagem acima confirma que o banco de dados está realmente iniciando a cada minuto.

Por que o banco de dados é iniciado a cada minuto?

Isso ocorre porque a propriedade AutoClose para esse banco de dados é definida como True .

Com essa propriedade definida como True , quando a última conexão do usuário é desconectada, o banco de dados é fechado. Quando um usuário se conecta de volta ao banco de dados, o banco de dados é iniciado novamente e a mensagem informativa é registrada no Log de Erros do SQL Server. Quando um banco de dados é inicializado, os recursos são atribuídos a ele e, quando ele é fechado, os recursos são liberados. A opção AutoClose é útil em um banco de dados que não é usado com frequência, como em um banco de dados em execução no SQL Server Express Edition. Mas se essa propriedade estiver configurada como True em um banco de dados OLTP ocupado, isso terá um impacto negativo no desempenho da instância.

Até mesmo eu encontrei alguns bancos de dados no ambiente do meu cliente onde a propriedade AutoClose estava definida como True . Como esses bancos de dados eram pequenos em tamanho e não tinham muita importância, não houve impacto. Essa propriedade pode ser desativada usando o diálogo Propriedades do Banco de Dados no SSMS ou usando a consulta a seguir.

ALTER DATABASE [DBName] SET AUTO_CLOSE OFF