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.
• collation_name: Collation do banco de dados, controla armazenamento e manipulação de strings.
• 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:
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-databases-transact-sql?view=sql-server-2017

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:
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-master-files-transact-sql?view=sql-server-2017

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:
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-io-virtual-file-stats-transact-sql?view=sql-server-2017

Até o próximo post.

Saudações Tricolores,
Landry