Automações de tarefas no SQL Server

Prévia do material em texto

Administração de Banco de Dados II
Aula 10: Automações de tarefas administrativas no SQL Server
Apresentação
A décima e última aula da disciplina Administração de Bancos de Dados II revisita as aulas anteriores para implementar as
principais tarefas do dia a dia do administrador do SQL Server, como automações no SQL Server Agent.
Ao �nal da aula, você será capaz de planejar, implementar e agendar tarefas administrativas que sejam executadas
regularmente, auxiliando na monitoração e na administração das suas instâncias SQL Server.
Objetivos
Identi�car as principais tarefas administrativas para automação com o SQL Server Agent;
Empregar automações para Administração de Bancos de Dados, Segurança e Auditoria, Gerenciamento de
Processos, Transações e Bloqueios por meio do SQL Server Agent;
Empregar automações para Tuning, para desempenho máximo contínuo nas instâncias SQL Server.
Automações no SQL Server
Chegamos à última aula com uma bagagem repleta de ferramentas. Sabemos como implantar nossos bancos de dados de
acordo com as necessidades e as expectativas dos usuários; monitorar as cargas de trabalho submetidas às instâncias SQL
Server; garantir cópias de segurança dos bancos de dados; e, gerenciar os processos, as transações e os bloqueios, entre
outras tantas tarefas que aprendemos durante a disciplina.
Mas dá para fazer tanta coisa ao mesmo tempo?
Sem dúvida, o dia a dia de um Database Administrator (DBA) passa bem rápido. Mesmo com um bom planejamento das
tarefas, muitas vezes não há tempo para realizar todas as veri�cações necessárias. O ideal é criar automações para executar
regularmente as checagens e as tarefas repetitivas.
Dica
Toda vez que você realizar uma mesma tarefa repetidamente, é hora de automatizá-la. No SQL Server, a ferramenta para
implementar automações é o SQL Server Agent.
Tarefas repetitivas, como realização de backups e checklists diários, muitas vezes se comportam diferentemente do esperado.
Nessa hora, o DBA deve ser avisado da anormalidade a ser tratada. É preciso construir uma solução que seja capaz não só de
realizar as repetições mas também de distinguir entre um comportamento normal de um comportamento anormal.
Antes, porém, de começar a construir nossos jobs, prepararemos alguns recursos básicos: um banco de dados, uma caixa
postal, uma pasta para scripts e uma pasta para outputs dos jobs. Um banco de dados auxiliar para a execução dos trabalhos é
importante porque muitas vezes você precisará armazenar stored procedures e tabelas para os dados gerados pelos jobs. É o
banco de dados do DBA.
Atenção
O principal objetivo da utilização de ferramentas de automação é avisá-lo dos comportamentos observados que estão fora do
padrão. Para isso, crie uma caixa postal para receber e-mails. A ideia é utilizar essa conta somente para e-mails que realmente
demandam atenção do DBA.
Além das stored procedures a serem armazenadas no banco de dados dos DBAs, você precisará construir um script Powershell
ou Bash e chamá-los a partir do SQL Server Agent. Para isso, tenha uma pasta dedicada exclusivamente ao armazenamento
dos scripts que serão executados pela conta do Agent. Você precisará conceder as permissões adequadas à credencial do
Agent, de�nida durante a instalação do SQL Server.
Por último, em muitos trabalhos, você precisará armazenar o output fora do SQL Server para utilizar ferramentas do sistema operacional sobre
esses dados. Faz sentido, então, ter uma pasta específica para o Agent salvar os outputs quando necessário.
SQL Server Agent
A ferramenta do SQL Server para construção e agendamento de trabalhos é o SQL Server Agent. Um job do Agent é composto
por steps (etapas) para os quais o administrador determina o que vai ser executado. Podem ser executados não somente
scripts SQL Server mas também scripts Powershell e utilitários do sistema operacional, entre outros.
No SQL Server Management Studio, você pode acessar o SQL Server Agent a partir do menu da instância. Ao clicar com o
botão direito sobre a opção, você pode escolher Novo >> Trabalho.
Você deve nomear o job e fornecer a categoria e a descrição dele. Se precisar desabilitar o trabalho, a qualquer momento, basta
desmarcar a caixa Habilitado. Entre as opções à esquerda, estão as Etapas. É por meio dela que vamos de�nir todos os passos
do trabalho.
Para cada passo, você deve especi�car qual o tipo de
instrução. No exemplo, foi de�nido que o passo Faz Backup
é um script escrito em T-SQL cujo conteúdo está informado
na caixa Comando.
Para cada passo, você também deve informar, na aba
Avançado, o que deve acontecer caso o passo seja
executado com sucesso e o que deve acontecer caso o
passo falhe. Por padrão, um passo executado com sucesso
avança para o próximo passo, e um passo com falha na
execução encerra o trabalho.
Você pode ajustar esses comportamentos de acordo com a necessidade. Por exemplo, você pode, após a execução correta de
determinado passo, avançar para o último passo do job. Ou ainda, em caso de falha de um passo, desviar a execução do
trabalho para a execução de um passo que registre o erro em um sistema de controle de jobs.
Em seguida, você deve de�nir os agendamentos.
No exemplo, está sendo de�nido um agendamento que ocorre diariamente às 22 horas, sem prazo de término. Portanto, todos
os dias às 22 horas serão executados os passos de�nidos. É possível con�gurar alertas e noti�cações para alimentar um
sistema de monitoração ou mesmo enviar um e-mail. Veja na imagem a seguir.
Para con�gurar um e-mail e receber noti�cações em uma caixa postal, é preciso con�gurar um operador no SQL Server Agent.
Saiba mais
Veja um passo a passo completo para a con�guração de um operador no SQL Server Agent em How to setup SQL Server alerts
and email operator noti�cations. 
Para mais detalhes sobre o próprio Agent, visite SQL Server Agent.
Administração de bancos de dados
Começaremos a preparar nossos jobs pela seção de Administração de Bancos de Dados. Uma tarefa imprescindível para todo
DBA é garantir a execução e a integridade do backup dos seus bancos de dados. Que tal agendar a execução periódica desses
backups, tanto dos arquivos de dados quanto dos arquivos de log de transações? Além disso, que tal promover testes
automáticos de restauração desses backups para checar sua integridade?
Backup
javascript:void(0);
javascript:void(0);
Os primeiros jobs que você deve con�gurar em suas instâncias, logo que
cria um database, são aqueles relacionados ao backup do banco de dados e
do log de transações.
Conforme vimos na aula de Administração de Bancos de Dados, um backup do banco de dados pode ser disparado pelo
comando a seguir.
 
BACKUP DATABASE CRM TO DISK = 'H:\MSSQL.1\MSSQL\Backup\CRM.BAK' WITH INIT
Dependendo da sua estratégia de backup, você pode con�gurar os backups diferenciais com a regularidade necessária. É
importante também con�gurar o backup do log de transações, o que pode ser feito pelo comando a seguir.
 
BACKUP LOG CRM TO DISK = 'H:\MSSQL.1\MSSQL\Backup\CRM_Log.BAK' WITH INIT
Então, crie dois jobs para cada banco de dados: Um para o backup do banco, outro para o backup do log. Você não deve se
esquecer de incluir o passo de erro para enviar um e-mail de alerta sobre o backup que deixou de ser realizado, o que deve
provocar uma reação imediata no time de DBAs.
Atenção
Um ponto de atenção nos jobs de exemplo é que os backups estão sendo realizados para o mesmo arquivo em disco, logo, os
arquivos são sobrepostos a cada execução.
É preciso garantir que a ferramenta de backup corporativa realize a cópia desses arquivos na mesma regularidade que os
trabalhos do SQL Server, ou utilize alguma técnica para nomear os arquivos de acordo com a data e hora. Veja um exemplo:
 
DECLARE @nome_arquivo varchar(300)
SELECT @nome_arquivo = (SELECT 'C:\Temp\CRM_' + convert(varchar(500),GetDate(),112) + '.bak')
BACKUP DATABASE CRM TO DISK = @nome_Arquivo WITH INIT, COPY_ONLY
Com esse passo, você consegue gerar um arquivo para cada data nomeando-o como CRM_20200706.BAK,por exemplo. Para
incluir o horário de execução do backup, muito útil quando você tiver a execução do mesmo job várias vezes ao dia, utilize o
formato 120, conforme a seguir.
 
SELECT @nome_arquivo = 'C:\Temp\CRM_' + 
REPLACE(CONVERT(nvarchar(20),GetDate(),120),':','-') + '.BAK'
No comando apresentado, o nome do arquivo gerado é CRM_2020-07-06 20-16-07.BAK.
Testes de restore
Tão importante quanto fazer os backups é testá-los.
Um job para teste de restauração de banco de dados pode selecionar um ou mais backups e restaurá-los em bancos
temporários de uma instância especi�camente preparada para esse �m. Prepare o trabalho de forma que uma noti�cação seja
enviada ou um alerta seja emitido caso o comando SQL disparado não seja executado com sucesso. Você pode começar
executando um RESTORE FILELISTONLY ou um RESTORE HEADERONLY.
 
RESTORE FILELISTONLY FROM DISK='C:\Temp\CRM.bak'
Para ir mais além, seu job pode ter dois passos, a saber, um para restaurar o database com um nome TESTE_RESTAURACAO,
por exemplo, e outro para rodar uma veri�cação de integridade lógica e física em todos os objetos do banco de dados.
 
Step 1:
RESTORE DATABASE TESTE_RESTAURACAO FROM DISK='C:\Temp\CRM.bak' WITH STATS=5
Step 2:
DBCC CHECKDB (‘TESTE_RESTAURACAO’);
Note que sua instância deve conter as mesmas pastas dos arquivos de
dados e log de transações, caso contrário você terá um erro de caminho não
encontrado no sistema operacional. Se for necessário, você pode utilizar a
cláusula WITH MOVE para alterar os caminhos dos arquivos.
O output de um DBCC pode ser bem grande. O ideal é tomá-lo como entrada para um passo que execute uma pesquisa no
output e só envie a noti�cação caso haja erros.
Uma alternativa clássica para tratar as saídas de passos intermediários é armazená-las em arquivos do sistema operacional e
realizar pesquisas sobre eles, utilizando para isso algum utilitário do próprio sistema operacional, como grep ou awk.
No caso em estudo, você pode extrair a linha que contém o total de erros de alocação e consistência do banco de dados, como
a mostrada a seguir.
 
CHECKDB found 0 allocation errors and 0 consistency errors in database 'CRM'.
Atenção
Se você procurar por 0 allocation erros e 0 consistency erros e não encontrar as duas, há algo errado com seu backup e,
possivelmente, com o banco de dados original.
Volumes de bancos de dados
Existem respostas que todo DBA deve ter na ponta da língua. Uma
delas é Qual é o volume atual dos seus bancos de dados?
Para ter esse tipo de informação sempre à mão, vamos criar um job no Agent que será executado diariamente e registrará, para
cada banco de dados da instância, o volume de dados e o volume de log. Assim, você poderá consultá-lo sempre que quiser, ou
ainda mostrá-los em um painel de monitoração.
No código a seguir, criamos uma tabela temporária para armazenar os dados e utilizamos sp_msforeachdb para inserir nessa
tabela o volume do arquivo de dados (type=0) e o volume do arquivo de log (type=1) de cada banco de dados da instância.
 
if object_id('tempdb..#volume_bancos') is not null
drop table #volume_bancos
CREATE TABLE #volume_bancos (
Data datetime,
Banco sysname,
Volume_Dados decimal(13,2),
Volume_Log decimal(13,2)
)
 
EXEC sp_msforeachdb 'USE [?];
INSERT INTO #volume_bancos
SELECT GETDATE() Data,
DB_NAME() AS Banco,
DADOS.Volume_Dados AS Volume_Dados,
LOG.Volume_Log Volume_Log
FROM (
SELECT SUM(CAST((f.size/128.0) AS DECIMAL(13,2))) AS Volume_Dados
FROM sys.database_files AS f WITH (NOLOCK) where type = 0) AS DADOS
CROSS JOIN (
SELECT SUM(CAST((f.size/128.0) AS DECIMAL(13,2))) AS Volume_Log
FROM sys.database_files AS f WITH (NOLOCK) where type = 1) AS LOG
'
SELECT * FROM #volume_bancos;
GO
Dica
Note que estamos utilizando uma tabela temporária, mas você pode persistir esses dados em uma tabela não temporária, de
preferência armazenado em um banco de dados de controle.
Para isso, você pode ter algo do tipo INSERT INTO DBA..volume_bancos.
Outro ponto interessante é que estamos considerando o volume ocupado pelos arquivos, em vez do volume utilizado
efetivamente pelos dados.
É uma decisão que cabe a você. Se quiser considerar o volume utilizado, basta recuperar a propriedade SPACEUSED em uma
construção como a mostrada a seguir.
 
SUM(CAST(CAST(FILEPROPERTY(f.name, ''SpaceUsed'') AS int)/128.0 AS DECIMAL(13,2))) AS Volume_Usado
Atividade
1. Como você faria para ter uma única tabela de controle em uma instância central, a qual pudesse armazenar os volumes de
todos os bancos de dados de todas as instâncias SQL Server do seu ambiente? Que recursos vistos durante a disciplina
poderiam ser utilizados para compor essa solução?
Segurança e auditoria
Há muitos relatórios a respeito de Segurança e Auditoria que podem ser agendados pelo Agent. Vimos alguns deles na aula
correspondente.
Do ponto de vista do DBA, o ideal é checar regularmente em busca de falhas de segurança que possam comprometer os
bancos de dados. Além disso, é importante veri�car itens frequentes de auditoria e cuidar para não ser pego de surpresa.
Falhas de login
Um alerta frequente para DBAs ocorre quando há sucessivas falhas de login de um usuário. É preciso checar o que está
acontecendo, pois esse tipo de comportamento pode signi�car uma tentativa de invasão.
Falhas de login são registradas no log de erros do SQL
Server. É importante veri�car se essa captura está
habilitada, por meio da opção Segurança das Propriedades
da instância, conforme a imagem a seguir.
Deixe marcado Failed Logins Only para capturar somente as falhas de login. Os registros dessas falhas são gravados no error
log do SQL Server e você pode utilizar a procedure xp_readerrorlog para ler esses registros. Veja um exemplo a seguir.
 
EXEC sp_readerrorlog 0, 1, 'Login failed'
O comando mostrado retornará todos os registros de falha de login, o que deve ser enviado para análise.
Validação de logins
A validação é fundamental para checar se há algum login do SQL Server mapeado para um usuário ou grupo do Windows que
não existe. Como vimos em aulas anteriores, você pode carregar o output de uma procedure em uma tabela criada por você.
Veja um exemplo de passo do job.
 
INSERT INTO DBA..sec_validatelogins
EXEC sp_validatelogins;
Se a tabela auxiliar não estiver vazia, é porque existem logins não válidos que devem ser checados e eventualmente removidos
da instância.
Administradores das instâncias
É importante checar regularmente quem são os membros das roles �xas das instâncias SQL Server, especialmente os
sysadmins.
 
INSERT INTO DBA..sec_helpsrvrolemember
EXEC sp_helpsrvrolemember;
Estabeleça seus critérios de pesquisa segundo os padrões de suas instâncias. Diferentemente do resultado anterior, essa
tabela conterá os administradores da instância. O job deverá realizar uma pesquisa nas linhas da tabela, procurando por
alguma ocorrência que esteja fora do padrão.
Privilégios em bancos de dados
Da mesma forma, veri�car os membros das roles �xas dos bancos de dados da instância também é uma boa prática para
garantir a segurança tanto dos bancos de dados, quanto dos dados propriamente ditos. Você não vai querer usuários indevidos
nos grupos db_datawriter de seus bancos de dados, certo?
Podemos esquematizar o job con�gurando três passos, a saber, um para drop da tabela auxiliar, um para create e outro para
passear pelos bancos de dados da instância e recuperar os membros das roles �xas dos bancos de dados da instância.
 
Step 1:
DROP TABLE DBA..sec_databaserolemember;
Step 2:
CREATE TABLE DBA..sec_databaserolemember (DatabaseName sysname, DatabaseRoleName sysname, DatabaseUserName
sysname)
Step 3:
EXEC sp_msforeachdb '
USE [?]
INSERT INTO DBA..sec_databaserolemember
SELECT DB_NAME() AS DatabaseName, DP1.name AS DatabaseRoleName,
isnull (DP2.name, ''No members'') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DP1
ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN sys.database_principalsAS DP2
ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = ''R''
ORDER BY DP1.name;
Mais uma vez, você deve estabelecer seus critérios de pesquisa segundo os padrões de suas instâncias.
Podemos concluir, adicionalmente, que um padrão para nomes de usuários é fundamental para uma boa administração das
instâncias SQL Server.
Exemplo
As checagens para validação de logins e concessão de privilégios, por exemplo, são baseadas no padrão de�nido para suas
instâncias.
Monitoração e gerenciamento de processos
Na aula de Monitoração e Gerenciamento de Processos, aprendemos como funcionam as �las de execução e como se dá a
transição dos estados de um processo.
Vimos como monitorar esses estados e os tempos de espera associados, construindo técnicas para avaliar quando
determinados comportamentos começam a impactar o desempenho geral da instância e quando é hora de atuar.
Vamos agora fazer o SQL Server Agent nos avisar quando esses comportamentos começarem a acontecer.
Atenção
Não adianta construir relatórios imensos que ninguém vai ler. Trata-se de construir monitorações dinâmicas e disparar
noti�cações somente quando houver necessidade de atuação.
Volume de tarefas em execução x Volume de tarefas em espera
Utilizamos, na aula de Monitoração e Gerenciamento de Processos, a construção a seguir para recuperar a quantidade de
tarefas por scheduler e estado.
 
SELECT [ot].[scheduler_id], [task_state], COUNT (*) AS [task_count]
FROM sys.dm_os_tasks AS [ot]
INNER JOIN sys.dm_exec_requests AS [er] ON [ot].[session_id] = [er].[session_id]
INNER JOIN sys.dm_exec_sessions AS [es] ON [er].[session_id] = [es].[session_id]
WHERE [es].[is_user_process] = 1
GROUP BY [ot].[scheduler_id], [task_state]
ORDER BY [task_state], [ot].[scheduler_id];
GO
Uma discussão importante no escopo do gerenciamento de processos está relacionada ao volume de tarefas que estão em
execução na instância e ao volume de tarefas que estão suspensas, aguardando algum tipo de recurso. Queremos maximizar o
número de tarefas RUNNING ou RUNNABLE, minimizando o número de tarefas que estão no estado SUSPENDED.
Naturalmente, não há um número previamente estipulado que você deve perseguir, pois as instâncias, bancos de dados e
cargas de trabalho têm suas próprias características e recursos disponíveis. O que se propõe é que você acompanhe os
números relacionados às suas instâncias com o objetivo de criar faixas de referência para o que é normal. Assim, quando as
taxas estiverem fora desse normal, uma noti�cação deve ser disparada.
Comece criando um job que armazene, em intervalos curtos de tempo, os
contadores por estado em uma tabela auxiliar. Essa tabela deve conter uma
coluna Normal com valor padrão 1. Acompanhe os números. Quando houver
uma degradação de desempenho geral na instância, veri�que os últimos
números armazenados. Atualize a coluna Normal com o valor 0 para as
observações do período de degradação. Repetindo o procedimento, você
constrói uma base de observações da instância que, mais tarde, servirá de
insumo para a de�nição das faixas de referência.
Essa prática é comum e você pode utilizá-la não só nessa automação, mas em todas aquelas para as quais seja necessário
construir uma referência. Comece sempre conhecendo suas instâncias e bancos de dados para, somente depois que estiver
seguro quanto às faixas, implantar um job que emita um alerta quando observar um comportamento anormal.  
A construção a seguir agrupa por estado:
 
CREATE TABLE DBA..taskstate (datahora datetime, estado varchar(20), contador int)
INSERT INTO DBA..taskstate
SELECT GETDATE() AS datahora, [task_state], COUNT(*) AS [task_count]
FROM sys.dm_os_tasks AS [ot]
INNER JOIN sys.dm_exec_requests AS [er] ON [ot].[session_id] = [er].[session_id]
INNER JOIN sys.dm_exec_sessions AS [es] ON [er].[session_id] = [es].[session_id]
WHERE [es].[is_user_process] = 1
GROUP BY [task_state]
ORDER BY [task_state]
GO
Veja um exemplo de saída do SELECT para uma instância que suporta uma carga de trabalho com poucas transações. Há 7
tarefas suspensas em um total de 20, em uma relação frequentemente normal.
 
datahora task_state task_count
2020-07-06 11:31:43.413 RUNNABLE 2
2020-07-06 11:31:43.413 RUNNING 11
2020-07-06 11: 31:43.413 SUSPENDED 7
Veja outro exemplo de saída do SELECT, desta vez para uma instância de Data Warehouse que suporta um volume maior de
transações simultâneas. Há 337 tarefas suspensas em um total de 546, com cerca de 61% em SUSPENDED e quase ¼ das
transações em RUNNABLE, algo frequentemente apontado como anormal.
 
datahora task_state task_count
2020-07-06 11:31:43.413 RUNNABLE 128
2020-07-06 11:31:43.413 RUNNING 81
2020-07-06 11: 31:43.413 SUSPENDED 337
Atenção
Mais uma vez, somente por meio de observações regulares em suas instâncias é que você vai chegar a uma conclusão para
estabelecer uma faixa normal de volume de tarefas por estado.
Tarefas em espera de usuários
Outra ferramenta importante no universo do gerenciamento de processos é a visão dinâmica de tarefas em espera waiting
tasks. Essa visão fornece dados de tempo de espera associados ao tipo de espera, como CXPACKET e PAGEIOLATCH. Veja a
seguinte construção:
 
SELECT [owt].[session_id], [owt].[exec_context_id], [owt].[wait_duration_ms], [owt].[wait_type],
[owt].[blocking_session_id], [owt].[resource_description], [es].[program_name], [est].[text],
[est].[dbid], [eqp].[query_plan], [es].[cpu_time], [es].[memory_usage]
FROM sys.dm_os_waiting_tasks [owt]
INNER JOIN sys.dm_exec_sessions [es] ON [owt].[session_id] = [es].[session_id]
INNER JOIN sys.dm_exec_requests [er] ON [es].[session_id] = [er].[session_id]
OUTER APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est]
OUTER APPLY sys.dm_exec_query_plan ([er].[plan_handle]) [eqp]
WHERE [es].[is_user_process] = 1
ORDER BY [owt].[session_id], [owt].[exec_context_id]
GO
A ideia de um trabalho de monitoramento de tempos de espera é avisar quando esses tempos �carem muito altos. A análise
para a de�nição de faixas de tempos deve ser realizada por tipo de espera; por esse motivo, a construção a seguir faz o
agrupamento por wait_type.
 
SELECT [owt].[wait_type], SUM([owt].[wait_duration_ms]) AS total_time
FROM sys.dm_os_waiting_tasks [owt]
INNER JOIN sys.dm_exec_sessions [es] ON [owt].[session_id] = [es].[session_id]
INNER JOIN sys.dm_exec_requests [er] ON [es].[session_id] = [er].[session_id]
OUTER APPLY sys.dm_exec_sql_text ([er].[sql_handle]) [est]
OUTER APPLY sys.dm_exec_query_plan ([er].[plan_handle]) [eqp]
WHERE [es].[is_user_process] = 1
GROUP BY [owt].[wait_type]
ORDER BY [owt].[wait_type]
GO
Dica
Use a mesma estratégia. Crie uma tabela auxiliar no banco DBA e registre regularmente os tempos por data e hora e tipo de
bloqueio. Entenda quais são os tempos razoáveis para CXPACKET, PAGEIOLATCH e todos os tipos LCK_* correspondentes aos
bloqueios.
Gerenciamento de transações e bloqueios
Tempos de bloqueios podem ser muito grandes. Veja um resultado possível da construção apresentada anteriormente:
 
wait_type total_time
LCK_M_IX 11917
LCK_M_S 20593
LCK_M_X 707803343
Comentário
O tempo de espera por um bloqueio exclusivo é muito maior, mas isso é absolutamente comum. Tempos de espera por
bloqueios exclusivos tendem a ser muito maiores que tempos de espera por bloqueios compartilhados, principalmente em
bancos de dados transacionais, nos quais há muitas atualizações.
Com um comportamento diferente, em bancos de dados analíticos há muito mais consultas e, portanto, não há muita espera
por bloqueio exclusivo. Veja um exemplo retirado de uma instância desse tipo:
 
wait_type total_time
ASYNC_IO_COMPLETION 11083
BACKUPBUFFER 3561
BACKUPIO 37255
CXPACKET 14484632
IO_COMPLETION 4769
SP_SERVER_DIAGNOSTICS_SLEEP 910
TRACEWRITE 1065
Os maiores tempos estão em esperas do tipo CXPACKET, normalmente encontradas na execução de threads paralelas, como
vimos na aula de Gerenciamento de Transações e Bloqueios.
É fundamental acostumar-se com os temposde bloqueio das suas instâncias. Eles tendem a se repetir no dia a dia.
Naturalmente, se o banco apresenta alguma característica de sazonalidade, então esses tempos vão variar, assim como o
próprio volume de transações. Nesses casos, você vai construir duas ou mais faixas de referência e basear seus trabalhos
nestas.
Cadeia de bloqueios
Tão importante quanto o monitoramento dos tempos de bloqueio é o monitoramento dos bloqueios propriamente ditos.
Acompanhar o volume de bloqueios das suas instâncias para criar faixas de referências é uma primeira abordagem. Algo que
pode ser muito interessante também é acompanhar a profundidade das cadeias de bloqueio.
Você se lembra de que utilizamos um script clássico que representa
gra�camente as cadeias de bloqueio? Para cada cadeia, é mostrada a
cabeça, como no seguinte exemplo:
 
HEAD - 60 BEGIN TRANSACTION UPDATE CRM.Usuarios SET id_resp=4 WHERE id_usuario=4 
|---------|------ 56 (@1 int,@2 tinyint)UPDATE [CRM].[Usuarios] set [id_resp] = @1 WHERE [id_usuario]=@2 
|---------|-------|------ 61 (@1 int,@2 tinyint)UPDATE [CRM].[Usuarios] set [id_resp] = @1 WHERE [id_usuario]=@2 
|---------|-------|------ 65 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; SELECT * FROM CRM.Usuarios
No exemplo, a cabeça da cadeia é a sessão 60 e a cadeia tem uma profundidade de 3, pois são três as sessões bloqueadas. A
ideia é acompanhar as cadeias de bloqueio para determinar a profundidade média nas suas instâncias. Vejamos como fazê-lo.
A primeira parte do script gera uma tabela temporária #T com a lista
dos processos em execução e os bloqueios correspondentes.
 
SET NOCOUNT ON
GO
SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH
INTO #T
FROM sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T
GO
WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)
AS
(
SELECT SPID,
BLOCKED,
CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,
BATCH FROM #T R
WHERE (BLOCKED = 0 OR BLOCKED = SPID)
AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)
UNION ALL
SELECT R.SPID,
R.BLOCKED,
CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,
R.BATCH FROM #T AS R
INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID
)
SELECT SPID, COUNT(BLOCKED) AS Profundidade
FROM BLOCKERS
GROUP BY SPID
ORDER BY SPID
DROP TABLE #T
GO
A segunda parte gera a cadeia completa, incluindo os níveis (LEVEL)
de cada sessão na cadeia. A terceira parte faz a contagem para
determinar a profundidade.
 
SPID Profundidade
344 1
349 4
Comentário
É esperado que não haja longas cadeias de bloqueio em suas instâncias. Se elas estão acontecendo com frequência, em uma
profundidade maior que a faixa de referência, então é melhor investigar para entender a causa raiz. É possível que uma
reescrita das consultas resolva o problema. Lembre-se das técnicas e das boas práticas aprendidas na aula sobre Bloqueios.
Tuning
Atualização de estatísticas
Boa parte dos problemas relacionados a desempenho em bancos de dados é oriundo de estatísticas desatualizadas.
Como vimos na aula sobre Tuning, o otimizador do SGBD utiliza as cardinalidades e os histogramas constantes das
estatísticas para selecionar os melhores operadores em planos de execução.
Naturalmente, quanto mais se mantiverem atualizadas, maior a chance de termos planos de execução com melhor
desempenho. É o que vamos fazer.
O procedimento a seguir percorre todos os objetos do banco de dados corrente e prepara um comando UPDATE STATISTICS
para cada tabela encontrada.
 
SELECT o.NAME, sc.NAME, i.NAME, sp.last_updated, sp.rows, sp.rows_sampled,
'UPDATE STATISTICS [' + sc.NAME + '].[' + o.NAME + '] WITH FULLSCAN' AS [Comandos]
FROM sys.objects AS o
INNER JOIN sys.schemas sc ON (o.schema_id = sc.schema_id)
INNER JOIN sys.indexes AS i ON (o.object_id = i.object_id)
INNER JOIN sys.stats AS s ON (i.object_id = s.object_id)
CROSS apply sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE sp.rows <> sp.rows_sampled
AND o.type = 'U'
AND i.is_unique = 1
AND s.stats_id = 1
Note que os objetos selecionados são aqueles para os quais o total de linhas é diferente do total de linhas utilizadas como
amostra para a geração das estatísticas.
Se forem iguais, não é necessária nova atualização das estatísticas. O resultado da execução do procedimento mostra os
seguintes comandos:
 
UPDATE STATISTICS [CRM].[Usuarios] WITH FULLSCAN
UPDATE STATISTICS [CRM].[Usuarios_Documentos] WITH FULLSCAN
UPDATE STATISTICS [CRM].[Usuarios_Historico] WITH FULLSCAN
UPDATE STATISTICS [CRM].[Usuarios_Historico_Part] WITH FULLSCAN
Com base nesse procedimento, podemos preparar um job que percorra todos os bancos de dados da instância por meio de
sp_msforeachdb e, para cada banco de dados, gere um comando UPDATE STATISTICS para cada objeto do banco de dados,
armazenando esse comando em uma tabela temporária.
Ao término da busca em todos os bancos de dados, podemos executar os comandos armazenados na tabela temporária. Veja
como podemos fazê-lo:
 
EXEC sp_msforeachdb 'IF ''?'' NOT IN ('CRM'') AND databasepropertyex(''?'',''Updateability'') = ''READ_WRITE''
BEGIN
USE [?]
INSERT INTO #update_stats
SELECT ''?'', o.NAME, sc.NAME, i.NAME, sp.last_updated, sp.rows, sp.rows_sampled,
''UPDATE STATISTICS [?].['' + sc.NAME + ''].['' + o.NAME + ''] WITH FULLSCAN'' AS [CMD]
FROM sys.objects AS o
INNER JOIN sys.schemas sc ON (o.schema_id = sc.schema_id)
INNER JOIN sys.indexes AS i ON (o.object_id = i.object_id)
INNER JOIN sys.stats AS s ON (i.object_id = s.object_id)
CROSS apply sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE AND sp.rows <> sp.rows_sampled
AND o.type = ''U''
AND i.is_unique = 1
AND s.stats_id = 1
END
Trata-se do mesmo SELECT utilizado anteriormente, sendo que o resultado é inserido na tabela temporária #update_stats. Para
executar esses comandos, podemos utilizar o seguinte cursor:
 
DECLARE cur_stats CURSOR LOCAL FORWARD_ONLY FAST_FORWARD READ_ONLY FOR
SELECT DatabaseName, CMD FROM #update_stats
OPEN cur_stats
FETCH NEXT FROM cur_stats INTO @DatabaseName, @cmd
WHILE @@fetch_status = 0
BEGIN
PRINT @cmd
EXEC sp_executesql @cmd
FETCH NEXT FROM cur_stats INTO @DatabaseName, @cmd
END
CLOSE cur_stats
DEALLOCATE cur_stats
O cursor percorre a tabela temporária e, para cada linha selecionada pelo FETCH, executa o comando correspondente em
@cmd. O loop WHILE continua enquanto houver novas linhas (@@fetch_status=0).
Note que estamos usando FULLSCAN na atualização das estatísticas. Nem sempre isso é possível, pois o tempo para atualizar
as estatísticas percorrendo a tabela inteira pode ser proibitivo.
Para esses casos, você utilizará abordagens alternativas, como selecionar algumas tabelas de acordo com rows_sampled, ou
ainda utilizar a cláusula SAMPLE para considerar somente parte da tabela.
Atenção
Novamente, estatísticas desatualizadas são a fonte mais frequente de problemas de desempenho. Não é fácil encontrar janela
para atualização FULLSCAN em tabelas muito grandes. Logo, a saída será utilizar amostras. Aí é que mora o perigo. Estatísticas
atualizadas com SAMPLE são a segunda fonte mais frequente de problemas de desempenho. Lute bravamente para atualizar
suas estatísticas com o maior número possível de amostras. Monitore constantemente. Invista seu tempo nisso.
Índices não utilizados
Por incrível que pareça, você frequentemente encontrará índices não utilizados ou pouco utilizados em bancos de dados. Se
levarmos em consideração o custo de manutenção desses índices, principalmente nas cargas de tabelas grandes, é possível
que você opte por destruir índices. A query a seguir mostra o total de varreduras (scans), buscas (seeks), pesquisa (lookups) e
atualizações (updates).
 
SELECT OBJECT_NAME(S.[OBJECT_ID]) AS [OBJECT NAME],
I.[NAME] AS [INDEX NAME], USER_SEEKS,USER_SCANS, USER_LOOKUPS, USER_UPDATES
FROM SYS.DM_DB_INDEX_USAGE_STATS AS S
INNER JOIN SYS.INDEXES AS I ON I.[OBJECT_ID] = S.[OBJECT_ID] AND I.INDEX_ID = S.INDEX_ID
WHERE OBJECTPROPERTY(S.[OBJECT_ID],'IsUserTable') = 1
AND S.database_id = DB_ID()
Se você observar valores zero, é sinal de que não estão sendo utilizados e podem ser removidos. Se você observar valores
perto de zero, é sinal de que estão sendo utilizados, mas talvez seu custo de manutenção não se justi�que com pouco uso.
Reconstrução de índices
Como vimos na aula sobre Tuning, índices e índices parciais têm um papel fundamental no desempenho das consultas
submetidas ao SGBD.
O otimizador se baseia nas estatísticas das tabelas e nas visões indexadas para selecionar um ou outro índice. Para que toda a
cadeia de otimização funcione adequadamente, é preciso manter os índices em bom estado para que eles atendam ao
propósito de bene�ciar a execução das consultas.
Mantê-los em bom estado signi�ca monitorar o armazenamento, detectar rapidamente quando houver sinais de fragmentação
e automatizar a reorganização ou a recompilação do índice.
Durante a vida útil de um índice, linhas vão sendo inseridas, atualizadas e
removidas. Esta dinâmica provoca naturalmente uma desorganização das
páginas que contêm os as linhas do índice, fazendo a sua ordenação lógica
(a que é baseada na chave) começar a �car diferente da ordenação física
das páginas do índice. Esse processo é conhecido como Fragmentação.
Não queremos índices fragmentados porque ocorre aumento expressivo no volume de I/O e, muito provavelmente, nos tempos
de espera do tipo PAGEIOLATCH, que corresponde às esperas por leituras de páginas do disco.
Como, em um índice fragmentado, é necessário ler um número maior de páginas, há uma chance maior de contenção nessas
páginas que cresce junto com o nível de fragmentação.
A próxima consulta retorna, para cada par tabela/índice do banco de dados selecionado, o número de páginas e o percentual de
fragmentação.
 
USE CRM
GO
SELECT DB_NAME() As 'Banco',
S.name as 'Esquema',
T.name as 'Tabela',
I.name as 'Indice',
ISTAT.page_count AS [N Pag],
ISTAT.avg_fragmentation_in_percent AS [Perc Frag]
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ISTAT
INNER JOIN sys.tables T on T.object_id = ISTAT.object_id
INNER JOIN sys.schemas S on T.schema_id = S.schema_id
INNER JOIN sys.indexes I ON I.object_id = ISTAT.object_id
AND ISTAT.index_id = I.index_id
WHERE ISTAT.database_id = DB_ID()
and I.name is not null
AND ISTAT.avg_fragmentation_in_percent > 0
ORDER BY ISTAT.avg_fragmentation_in_percent desc
O resultado da execução da consulta no banco CRM mostra a seguinte lista:
 
Banco Esquema Tabela Indice N Pag Perc Frag
CRM CRM Usuarios_Historico ind_usuarios_historico_data 350 99,4285714285714
CRM CRM Usuarios PK__Usuarios__4E3E04ADE0DEE07B 9 11,1111111111111
CRM CRM Usuarios_Historico PK__Usuarios__4E3E04AD1063EF69 521 0,57581573896353
Veja que a maior parte dos índices do banco não apareceram na listagem porque possuem percentual de fragmentação igual a
0.
O índice clustered da tabela Usuarios_Historico está muito pouco fragmentado e o índice clustered da tabela Usuarios já
apresenta alguma fragmentação, entretanto, o índice non-clustered ind_usuarios_historico_data está muito fragmentado.
Nesse caso, devemos atuar para reduzir a fragmentação e temos duas opções para isso: Reorganização e Recompilação.
A reorganização consiste em movimentar páginas e linhas na organização interna do índice para reduzir a fragmentação. A
recompilação reconstrói completamente o índice e pode ser feita online ou o�ine. No modo o�ine, a tabela �ca indisponível
até que o processo termine.
Segundo a Microsoft, em Resolver a fragmentação do índice reorganizando ou recompilando índices , você deve utilizar a
referência a seguir, para determinar se o índice deve ser reorganizado ou recompilado.
Valoravg_fragmentation_in_percent Instrução corretiva
> 5% e < = 30% ALTER INDEX REORGANIZE
> 30% ALTER INDEX REBUILD WITH (ONLINE = ON)
1
1 2
O job Agent que monitora a fragmentação pode usar uma tabela temporária para armazenar semanalmente, por exemplo, os
níveis de fragmentação dos índices. Esse seria o primeiro passo.
Em um segundo passo, pode montar um ou outro comando SQL para reorganizar ou recompilar o índice, dependendo do nível
de fragmentação.
Em nosso exemplo, vamos fazer um REORGANIZE no índice clusterizado da tabela Usuarios e um REBUILD no índice não
clusterizado.
javascript:void(0);
 
ALTER INDEX PK__Usuarios__4E3E04ADE0DEE07B ON CRM.Usuarios REORGANIZE;
ALTER INDEX ind_usuarios_historico_data ON CRM.Usuarios_Historico REBUILD WITH (ONLINE=ON);
Após o término do processo, você pode checar novamente os índices de fragmentação. Veja o resultado:
 
Banco Esquema Tabela Indice N Pag Perc Frag
CRM CRM Usuarios PK__Usuarios__4E3E04ADE0DEE07B 9 11,111111111111
CRM CRM Usuarios_Historico ind_usuarios_historico_data 347 0,5763688760806
CRM CRM Usuarios_Historico PK__Usuarios__4E3E04AD1063EF69 521 0,5758157389635
Veja que não só o nível de fragmentação do índice não clusterizado diminuiu, como também diminuiu o número de páginas
necessárias para armazenar o índice. Isso se deve ao fato de que, na reconstrução, uma maior concentração de linhas foi
utilizada nas páginas e, portanto, menos páginas foram alocadas.
Podemos observar também que não houve redução no nível de fragmentação do índice PK__Usuarios__4E3E04ADE0DEE07B.
Isso pode acontecer em algumas situações, por exemplo, em estruturas muito pequenas. O índice clusterizado em questão
possui 9 páginas e sua composição de páginas e linhas não permite uma desfragmentação maior.
Saiba mais
Para ver um passo a passo focado na resolução de problemas de fragmentação, acesse How to identify and resolve SQL Server
Index Fragmentation.
javascript:void(0);
Conclusão
Chegamos ao �nal da disciplina com a certeza de termos construído um bom ferramental para encarar a administração de
instâncias SQL Server. Especialmente nesta aula, revisitamos os tópicos abordados durante todo o percurso e construímos
automações para nos ajudar na tarefa desa�adora de prover bancos de dados SQL Server com disponibilidade, segurança,
monitoração e desempenho adequados aos requisitos de negócio e às expectativas dos usuários.
Automações são a convergência da experiência do DBA e da necessidade de executar tarefas repetitivas que fazem toda a
diferença no seu dia a dia. É bastante comum ter centenas de jobs con�gurados no Agent. Vimos apenas alguns deles. Log
Shipping, Replicações Transacionais e Mirroring são fortemente apoiados por trabalhos. Identi�que-os. Consulte regularmente
seus históricos.
Não hesite em investir tempo para construir a própria caixa de ferramentas. Existem muitos sites, muitos scripts, muitas
técnicas, mas nem todas serão adequadas às suas instâncias, que terão características muito próprias de disponibilidade de
recursos, aplicações, usuários e consultas. Portanto, construa você mesmo os seus arquivos.sql!
Sinta-se em casa com suas ferramentas. DBAs são tentados a baixar scripts e ferramentas de sites especializados para
resolver problemas. Não faça isso. Muito embora uma grande parte dos sites ofereçam ferramentas e�cientes e seguras, é
importante que você entenda os códigos que serão executados nas suas instâncias. Certamente você vai querer fazer
adequações.
Seja você mesmo o DBA das suas instâncias SQL Server.
Atividades
1. Entre os jobs a seguir, assinale os que podem ser implantados por um administrador por meio do SQL Server Agent.
a) Monitoração de volumes de bancos de dados
b) Reconstrução de índices não clusterizados
c) Geração de cópias de segurança e realização de testes de restauração
d) Todas as alternativas anteriores
e) Nenhuma das alternativas anteriores
2. Assinale a alternativa INCORRETA a respeito de automações no SQL Server.
a) É fundamental construir um job de verificação de falhas de login. Sucessivas falhaspodem indicar uma tentativa de invasão por força
bruta e, nesse caso, é preciso investigar a origem dessas tentativas.
b) Um job de monitoração de tempos de espera pode enviar uma notificação para a caixa postal dos DBAs informando, por exemplo, que
há uma degradação nos tempos associados a operações de leitura de páginas do disco para memória.
c) É possível criar um job no Agent para realizar os backups de todos os bancos de dados da instância e transferir os arquivos de backup
para a nuvem.
d) É possível criar um job para executar regularmente SHRINKFILE de todos os arquivos dos bancos de dados da instância, com o objetivo
de manter os volumes alocados pelos arquivos próximos dos volumes realmente ocupados pelos dados.
e) É fundamental construir um job para a monitoração das transações em execução na instância. Nos casos em que cadeias de bloqueio se
formam, é possível identificar a cabeça do bloqueio e interromper a sessão, liberando todas as transações que voltam a executar
imediatamente.
3. Assinale a(s) alternativa(s) INCORRETA(S) a respeito de jobs como ação de sintonia �na no SQL Server.
a) A análise da fragmentação de índices é fundamental para a manutenção do bom desempenho das consultas submetidas ao banco de
dados. Caso seja constatado um nível de fragmentação entre 5% e 30%, deve-se utilizar a técnica de reorganização. Caso seja um nível
maior que 30%, deve-se utilizar a técnica de rebuild.
b) A atualização das estatísticas é imprescindível. Mantê-las atualizadas é garantia de bom desempenho da carga de trabalho da instância
c) Quando atualizamos as estatísticas de tabelas ou visões indexadas, é preciso ter em mente que a opção FULLSCAN pode ser proibitiva
por causa da janela necessária para a execução do comando. Nesses casos, pode-se estabelecer um percentual de sample.
d) Índices não utilizados devem ser removidos porque são prejudiciais às cargas de trabalho submetidas aos bancos de dados SQL Server,
principalmente se contêm muitas atualizações.
e) Analisar as estatísticas de tempos de espera por meio de um job do Agent é fundamental. Se este job indicar que há um volume muito
alto de esperas do tipo CXPACKET, é hora de verificar o tráfego nas interfaces de rede conectadas às instâncias, pois esses tipos de espera
não são muito comuns.
Notas
Referências
ASSAF, W.; WEST, R.; AELTERMAN, S.; CURNUTT, M. SQL Server 2017 Administration Inside Out. Pearson Education, 2018.
BIRD, D. How to setup SQL Server alerts and email operator noti�cations. 2019. Disponível em:
https://www.mssqltips.com/sqlservertip/1523/how-to-setup-sql-server-alerts-and-email-operator-noti�cations/. Acesso em: 23
jul. 2020.
DELANEY, K.; FREEDMAN, C. Microsoft SQL Server 2012 Internals. Microsoft Press, 2013.
KELLEY, K. B. Auditing Failed Logins in SQL Server. 2009. Disponível em:
https://www.mssqltips.com/sqlservertip/1735/auditing-failed-logins-in-sql-server/. Acesso em: 23 jul. 2020.
MICROSOFT. Resolver a fragmentação do índice reorganizando ou recompilando índices. 2020. Disponível em:
https://docs.microsoft.com/pt-br/sql/relational-databases/indexes/reorganize-and-rebuild-indexes?view=sql-server-ver15.
Acesso em: 23 jul. 2020.
MICROSOFT. DBCC CHECKDB (Transact-SQL). 2017. Disponível em: https://docs.microsoft.com/pt-br/sql/t-sql/database-
console-commands/dbcc-checkdb-transact-sql?view=sql-server-ver15. Acesso em: 23 jul. 2020.
MICROSOFT. SQL Server Agent. 2017. Disponível em: https://docs.microsoft.com/pt-br/sql/ssms/agent/sql-server-agent?
view=sql-server-ver15. Acesso em: 23 jul. 2020.
RAIYANI, J. How to identify and resolve SQL Server Index Fragmentation. 2020. Disponível em: https://www.sqlshack.com/how-
to-identify-and-resolve-sql-server-index-fragmentation/. Acesso em: 23 jul. 2020.
RAMAKRISHNAN, R.; GEHRKE, J. Database management systems. 3. ed. New York: McGraw-Hill, 2002.
javascript:void(0);
javascript:void(0);
javascript:void(0);
javascript:void(0);
javascript:void(0);
javascript:void(0);
Próxima aula
Explore mais
Durante esta última aula da disciplina, nós revisitamos as aulas anteriores e criamos automações para compor nosso arsenal
de ferramentas de trabalho. Uma dessas automações respondeu à pergunta: Qual o volume atual dos seus bancos de dados?.
Existe outra pergunta, sobre esse mesmo assunto, que certamente também farão a você: Qual será o volume dos seus bancos
de dados daqui a 5 anos?.
Para responder, você analisará os dados históricos gerados pelo seu job de volumes de bancos de dados. Note que, depois de
algum tempo, você terá criado um histórico de crescimento dos seus bancos de dados. Você pode exportá-los para um Excel,
por exemplo, e gerar grá�cos de acompanhamento do crescimento dos seus bancos.
Mas, como utilizar esses dados históricos para emitir uma estimativa de crescimento dos seus bancos de dados? Pesquise
uma solução para esse problema. Implemente a solução. Tenha certeza de que você vai carregar essa ferramenta para onde
for.
Dicas: Os dados históricos podem ser convertidos em uma série temporal. As linguagens Python e R são ótimas para esse tipo
de análise, isto é, gerar forecasts de séries temporais.