Recentemente iniciei um projeto de restruturação de
servidores de produção SQL Server, onde em praticamente todos os servidores
convivem bases de produção, homologação e desenvolvimento. Foram criados dois servidores novos, destinados
para homologação e o outro para desenvolvimento. O trabalho consiste em mover os bancos de
homologação e desenvolvimento para os novos servidores, além de distribuir os
bancos de produção de modo a equilibrar o consumo de recursos.
Resolvi escrever um post mostrando as duas consultas
que utilizei para elaborar o documento com informações de atividades de IO nas
bases dos servidores.
Consulta
1
Relação de bases com informações de configuração e
tamanho.
WITH CTE_TamanhoBD as (
SELECT database_id, sum((size * 8) / 1024) as TamanhoMB
FROM sys.master_files
GROUP BY database_id)
SELECT a.name as Banco,'' as Prod,a.recovery_model_desc as [Recovery],
case a.compatibility_level
when 80 then 'SQL2000'
when 90 then 'SQL2005'
when 100 then 'SQL2008'
when 110 then 'SQL2012'
when 120 then 'SQL2014'
when 130 then 'SQL2016'
when 140 then 'SQL2017'
else ltrim(str(compatibility_level)) end as Versão,
a.collation_name as Collation,
case a.is_auto_close_on when 1 then 'AUTOCLOSE' else a.state_desc end as 'Status',
b.TamanhoMB
FROM master.sys.databases a
JOIN CTE_TamanhoBD b ON a.database_id = b.database_id
WHERE a.database_id > 4
ORDER BY 1,2
Esta primeira consulta utiliza duas visões de sistema:
sys.master_files e sys.databases. A
visão sys.databases retorna a lista
dos bancos de dados gerenciados pela instância, assim como várias propriedades.
Selecionei algumas destas propriedades, importantes para o processo de tomada
de decisão no remanejamento dos bancos.
name:
retorna o nome do banco de dados.
recovery_model_desc:
retorna o Recovery Model, importante informação com
impacto direto na estratégia de backup.
Pode conter um destes três valores: SIMPLE, FULL ou BULK_LOGGED.
compatibility_level:
nível de compatibilidade do banco de dados, algumas versões recentes do SQL
Server já são incompatíveis com versões antigas.
is_auto_close_on:
a propriedade AUTO CLOSE coloca o banco em OFFLINE quando ninguém estiver
utilizando. Banco com esta propriedade
habilitada retorna NULL na coluna “collation_name”
state_desc:
retorna o status atual do banco de dados, ex.: ONLINE, SUSPECT, OFFLINE, etc.
Link com a lista completa de atributos da visão de
sistema sys.databases:
Já a visão de sistema sys.master_files mantém registro dos arquivos (dados e log) de
todos os bancos de dados da instância, incluindo o tamanho atual de cada
arquivo no disco.
Link com a lista completa de atributos da visão de
sistema sys.master_files:
Consulta
2
Atividade de IO por base de dados.
WITH IO_Per_DB AS (
SELECT DB_NAME(database_id) AS Banco,
CONVERT(DECIMAL(12,2), SUM(num_of_bytes_read + num_of_bytes_written) / 1024 / 1024) AS IO_Mb,
CONVERT(DECIMAL(12,2), SUM(num_of_bytes_read) / 1024 / 1024) AS Leituras_Mb,
CONVERT(DECIMAL(12,2), SUM(num_of_bytes_written) / 1024 / 1024) AS Escritas_Mb
FROM sys.dm_io_virtual_file_stats(NULL, NULL) dmivfs
where database_id > 4
GROUP BY database_id)
SELECT Banco,
Leituras_Mb, Escritas_Mb, IO_Mb,
CAST(IO_Mb / SUM(IO_Mb) OVER() * 100 AS DECIMAL(5,2)) AS [%IO]
FROM
IO_Per_DB
ORDER BY 4 DESC
Esta segunda Consulta utiliza a função de sistema sys.dm_io_virtual_file_stats para totalizar
o I/O de todos os arquivos de cada banco de dados da instância. A coluna “%IO”
retorna o percentual da carga de trabalho de IO por banco de dados, informação
importante para analisar e comparar a carga de trabalho.
Link com a lista completa de atributos da função de
sistema sys.dm_io_virtual_file_stats:
Até o próximo post.
Saudações Tricolores,
Landry
Nenhum comentário:
Postar um comentário