O Cinto de Utilidades do DBA

Uma das grandes vantagens do SQL Server em relação aos demais bancos relacionais, é que dificilmente você irá se deparar com um problema que outra pessoa já não tenha enfrentado. E com algum tempo gasto pesquisando na internet, irá perceber que alguém já implementou uma solução genial para esse problema. A série de scripts e ferramentas abaixo, tem o intuito de ajudar a identificar os possíveis problemas do dia a dia que acontecem com o banco de dados, assim como, fornecer dados para uma análise posterior do problema. Um dos diferenciais do SQL Server para outros bancos de dados, é que ele armazena as informações das consultas que foram executadas, que podem ser acessadas através de DMVs (Dynamic Management View). O infortúnio disso é que decorar cada um desses objetos para minerar essas informações, é uma trabalho penoso e demorado. Esse é um dos motivos da adoção dos scripts/ferramentas abaixo. As informações necessárias para as análises estão todas esmiuçadas, restando apenas o trabalho de saber interpretá-las corretamente.

Melhores práticas de configuração de um Servidor SQL Server

usp_bpcheck: Esse script foi implementado pelo Tiger Team (time engenheiros de SQL Server dentro da própria Microsoft). Ao executá-lo ele retorna uma visão geral das configurações do servidor do banco de dados, mostrando no detalhe algumas configurações que podem ser ajustadas para uma melhor utilização dos recursos, Além disso, aborda o uso das melhores práticas no que se refere a configuração do servidor do banco de dados e que nas quais não são habilitadas na instalação padrão do servidor.

Exemplo de uso:

exec usp_bpcheck

Quando existem vários bancos de dados no servidor, convém refinar a chamada passando o parâmetro @dbScope, para que a execução do script não demore muito. O valor para um database específico pode ser obtido através de uma consulta a tabela sys.databases.

exec usp_bpcheck
    @dbScope = '11', --databaseID
    @diskfrag = 0

Manutenção do banco de dados

Maintenance Solution: Tudo que se faz necessário para a manutenção dos banco de dados, foi implementado nesses scripts disponibilizados por Ola Hallengren. A solução contempla desde atualização das estatísticas, reindexação dos índices, scripts de backup do banco de dados e teste de integridade. Para isso, basta executar o script de criação dos objetos, e agendar os horários nos jobs que o script cria automaticamente. A partir de então isso torna-se uma preocupação a menos para o DBA. A vantagem de adotar essa solução é que ela é mais leve do que os Planos de Manutenção nativos do SQL Server, além de ser de fácil customização para qualquer ambiente. Uma outra vantagem é que possui a capacidade de logar os comandos executados nas manutenções, o que facilita as análises posteriores. Isso é bastante útil para encontrar erros ocorridos nos jobs, além de mostrar exatamente qual o motivo da duração de uma janela de manutenção se estender além do esperado.

Abaixo, um exemplo que pode ser utilizado para a rotina de reindexação e atualização das estatísticas do banco de dados:

exec dbo.IndexOptimize
    @Databases = N'USER_DATABASES',
    @FragmentationLow = null,
    @FragmentationMedium = N'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationHigh = N'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FillFactor = 90,
    @PadIndex = N'Y',
    @LOBCompaction = N'Y',
    @UpdateStatistics = N'ALL',
    @OnlyModifiedStatistics = N'Y',
    @StatisticsSample = 100,
    @LogToTable = N'Y'

O que está acontecendo com o banco de dados neste momento?

sp_WhoIsActive: Sempre que algum problema está acontecendo em um exato momento no servidor do banco de dados, uma das primeiras coisas que tenho o hábito de verificar é o resultado desse script desenvolvido por Adam Mechanic. Ele basicamente mostra um snapshot de tudo que está sendo executado no servidor do banco de dados, mostrando uma série de informações para ajudar a identificar o motivo de uma possível lentidão. É uma das principais ferramentas para ser utilizada no dia a dia, pois mostra em um único script todas as informações necessárias para mitigar o problema de forma assertiva.

Exemplo de uso:

exec sp_WhoIsActive

A execução padrão (sem informar nenhum parâmetro), já retorna uma gama de informações relevantes, mas em alguns casos se faz necessário incluir algumas informações adicionais para facilitar a análise:

exec sp_whoisactive
    @get_plans = 1,
    @show_sleeping_spids = 1,
    @get_outer_command = 1

Quais as consultas que são problemáticas para o banco de dados?

First Responder Kit: desenvolvimento por Brent Ozar, se trata de uma suíte de scripts, que abrangem desde alguns pontos falhos na configuração do servidor, bem como a análise de consultas onerosas por diversas métricas diferentes, análise geral dos índices do banco de dados e eventuais locks ou deadlocks que acontecem nele. Para cada situação, existe um script específico que traz informações bem detalhadas sobre o ambiente.

Exemplo de uso:

-- mostra diversas informações a respeito do ambiente do servidor do banco de dados, segurança, configurações, etc...
exec sp_Blitz 

-- mostra um overview do cache do plano de execução
exec sp_BlitzCache 

-- overview específico do banco AdventureWorks2019, levando em conta o uso de CPU
exec sp_BlitzCache
    @databasename = 'AdventureWorks2019',
    @SortOrder = 'CPU'

-- overview específico do banco AdventureWorks2019, levando em conta o tempo de duração das consultas
exec sp_BlitzCache
    @databasename = 'AdventureWorks2019',
    @SortOrder = 'Duration'

-- overview sobre os índices no banco AdventureWorks2019
exec sp_BlitzIndex
    @databaseName = 'AdventureWorks2019'

-- overview sobre deadlocks ocorridos no banco de dados
exec sp_BlitzLock

Analisando as consultas armazenadas

Query Store: Esta é uma ferramenta nativa do SQL Server que também pode ajudar com a análise da performance do ambiente, bastando habilitá-la para isso. Infelizmente, somente à partir da versão 2016 está disponível. A grande vantagem dela é que além de ser uma ferramenta gráfica,  pode ser customizada em relação ao período da análise e qual das métricas de desempenho das consultas será feito a análise. Diferentemente de algumas DMVs que ao reiniciar o serviço do SQL Server as informações são perdidas (resetadas), o Query Store mantém essas informações, podendo inclusive ser configurado quanto espaço em disco irá ser disponibilizado para isso.

O script abaixo habilita o Query Store, que é uma configuração específica por database:

use [master]
go

alter database [StackOverflow2013] set query_store = on
go

alter database [StackOverflow2013] set query_store
(
    operation_mode = read_write,
    max_storage_size_mb = 1000
)
go 

Quais as consultas que estão sendo executadas no banco de dados?

Profiler: também é uma ferramenta bastante utilizada para capturar as consultas que estão sendo executadas naquele momento, no banco de dados. Ele fornece informações desde a duração da consulta, até o uso de CPU, número de linhas retornadas, páginas lidas, etc…. Sempre que o profiler for habilitado nos ambientes de produção, é preciso ter cuidado pois o mesmo tende a degradar a performance do servidor. É imprescindível que sejam definidos filtros seletivos eficazes na hora de configurar a captura da carga de trabalho acontecendo no banco de dados. A recomendação é deixá-lo executando apenas para coletar as informações pertinentes e após desligá-lo. Infelizmente somente para ambientes OnPremisse ele pode ser utilizado.

Para bases do SQL Azure, que estão na categoria de PaaS (plataforma como serviço), uma alternativa viável é utilizar o Azure Data Studio, instalando uma extensão chamada SQL Server Profiler. Esse plugin efetua de forma automática, o trabalho manual (e penoso) que o DBA teria para criar um Extended Event e mostrar o resultado em tela de uma forma muito mais simples. Atualmente, o uso de Extended Events é a recomendação da Microsoft como método de captura das consultas. Além de ser mais leve que o próprio Profiler, este tende a ser depreciado no futuro.

Testando o impacto de uma consulta no banco de dados?

SQLQueryStress: essa é uma ferramenta leve e muito útil para efetuar testes de performance em consultas individuais. Uma das principais funcionalidades, é a de configurar várias threads em paralelo, para simular o acesso simultâneo de vários usuários executando a mesma consulta no banco de dados. Com isso é possível mensurar como o servidor do banco de dados irá se comportar mediante tal demanda.