Недавно выдалась минутка посмотреть почему старый тестовый сервер безбожно тормозил… К нему я не имел никакого отношения, но меня одолевал спортивный интерес разобраться, что с ним не так.

Первым делом открыл Resource Monitor и взглянул на общую нагрузку. Процесс sqlserv.exe нагружал ЦП под 100% и формировал большую дисковую очередь, которая была за 300… при том, что значение выше единицы уже считается проблемным.

При анализе дисковой активности заметил непрерывные IO операции в msdb:

D:\SQL_2012\SYSTEM\MSDBData.mdf
D:\SQL_2012\SYSTEM\MSDBLog.ldf

Посмотрел на размер msdb:

SELECT name, size = size * 8. / 1024, space_used = FILEPROPERTY(name, 'SpaceUsed') * 8. / 1024
FROM sys.database_files

и включил режим «рука-лицо»:

name         size           space_used
------------ -------------- ---------------
MSDBData     42626.000000   42410.374395
MSDBLog      459.125000     6.859375

Файл данных занимал 42 Гб… Взяв небольшую паузу я начал разбираться в чем причина такого нездорового объема msdb и как побороть проблемы с производительностью сервера.

Проверил ресурсоемкие запросы, которые выполнялись на сервере:

SELECT
      r.session_id
    , db = DB_NAME(r.database_id)
    , r.[status]
    , p.[text]
    --, sql_text = SUBSTRING(p.[text], (r.statement_start_offset / 2) + 1,
    --        CASE WHEN r.statement_end_offset = -1
    --            THEN 2147483647
    --            ELSE ((r.statement_end_offset - r.statement_start_offset) / 2) + 1
    --        END)
    , r.cpu_time
    , r.total_elapsed_time
    , r.reads
    , r.writes
    , r.logical_reads
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) p
WHERE r.[sql_handle] IS NOT NULL
    AND r.session_id != @@SPID
ORDER BY logical_reads DESC

На первом месте гордо расположилась системная хранимая процедура:

session_id db       status   text                                  cpu_time    total_elapsed_time reads   writes    logical_reads
---------- -------- -------- ------------------------------------- ----------- ------------------ ------- --------- ---------------
62         msdb     running  create procedure [sys].[sp_cdc_scan]  111638      6739344            618232  554324    2857923422

Из названия которой можно догадаться, что речь идет о CDC (Change Data Capture), который применяется как средство для отслеживания измененных данных. CDC основан на чтении журнала транзакций и всегда работает в асинхронном режиме за счет использования Service Broker.

Из-за проблем в конфигурации, при попытке отправить Event Notification для Service Broker, сообщение может не достигнуть места назначения и тогда архивируется в отдельной таблице… Сильно занудно сказано… В общем, если часто используется Service Broker, то нужно мониторить sys.sysxmitqueue. Когда в данной таблице идет постоянный прирост данных, то это либо баг, либо мы неправильно используем Service Broker.

Вот этим запросом можно вернуть список объектов и их размер:

USE msdb
GO

SELECT TOP(10)
      o.[object_id]
    , obj = SCHEMA_NAME(o.[schema_id]) + '.' + o.name
    , o.[type]
    , i.total_rows
    , i.total_size
FROM sys.objects o
JOIN (
    SELECT
          i.[object_id]
        , total_size = CAST(SUM(a.total_pages) * 8. / 1024 AS DECIMAL(18,2))
        , total_rows = SUM(CASE WHEN i.index_id IN (0, 1) AND a.[type] = 1 THEN p.[rows] END)
    FROM sys.indexes i
    JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
    JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
    WHERE i.is_disabled = 0
        AND i.is_hypothetical = 0
    GROUP BY i.[object_id]
) i ON o.[object_id] = i.[object_id]
WHERE o.[type] IN ('V', 'U', 'S')
ORDER BY i.total_size DESC

После выполнения получил следующие результаты:

object_id   obj                               type total_rows   total_size 
----------- --------------------------------  ---- ------------ -----------
68          sys.sysxmitqueue                  S    6543502968   37188.90
942626401   dbo.sysmail_attachments           U    70           2566.00
1262627541  dbo.sysmail_attachments_transfer  U    35           2131.01
1102626971  dbo.sysmail_log                   U    44652        180.35
670625432   dbo.sysmail_mailitems             U    19231        123.39
965578478   dbo.sysjobhistory                 U    21055        69.05
366624349   dbo.backupfile                    U    6529         14.09 
727673640   dbo.sysssispackages               U    9            2.98  
206623779   dbo.backupset                     U    518          1.88  
286624064   dbo.backupfilegroup               U    3011         1.84  

Сразу скажу, что все таблицы в этом списке мы не оставим без внимания. Но сперва нужно очистить sys.sysxmitqueue.

Удалить данные напрямую из sys.sysxmitqueue не получится, поскольку эта таблица является системным объектом (S). После непродолжительных поисков я нашел способ как заставить SQL Server очистить эту таблицу. При создании нового Service Broker автоматически удаляются все ассоциированные со старым брокером сообщения.

USE msdb
GO

ALTER DATABASE msdb SET NEW_BROKER WITH ROLLBACK IMMEDIATE

Но перед выполнение команды настоятельно рекомендуется отключить SQL Server Agent и перевести SQL Server в Single-User Mode. Удаление существующих сообщений во всех очередях Service Broker заняло у меня минут десять. По завершению выполнения я получил следующее сообщение:

Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

После перезагрузки службы SQL Server все проблемы с производительностью ушли… душа радовалась и на этом можно было бы поставить точку. Но вспомним, что это была не единственная большая таблица в msdb. Давайте разберемся с остальными…

Для тех, кто любит отправлять почту через Database Mail нужно знать, что SQL Server всю почтовую рассылку логирует и хранит в msdb. Все почтовые вложения, которые отправляются с телом письма там аккуратненько сохраняются… Поэтому очень рекомендуется периодически очищать эту информацию. Делать это можно руками, т.е. смотреть какие таблицы нужно почистить:

SELECT o.name, p.[rows]
FROM msdb.sys.objects o
JOIN msdb.sys.partitions p ON o.[object_id] = p.[object_id]
WHERE o.name LIKE 'sysmail%'
    AND o.[type] = 'U'
    AND p.[rows] > 0

Либо использовать уже готовые хранимые процедуры sysmail_delete_mailitems_sp и sysmail_delete_log_sp:

DECLARE @DateBefore DATETIME 
SET @DateBefore = DATEADD(DAY, -7, GETDATE())

EXEC msdb.dbo.sysmail_delete_mailitems_sp @sent_before = @DateBefore --, @sent_status = 'sent'
EXEC msdb.dbo.sysmail_delete_log_sp @logged_before = @DateBefore

История выполнения заданий SQL Server Agent также сохраняется в msdb. Когда записей в логе становится много с ним становится не сильно удобно работать, поэтому я стараюсь его периодически чистить sp_purge_jobhistory:

DECLARE @DateBefore DATETIME 
SET @DateBefore = DATEADD(DAY, -7, GETDATE())

EXEC msdb.dbo.sp_purge_jobhistory @oldest_date = @DateBefore

Еще нужно упомянуть, про информацию о резервных копиях, которая логируются в msdb. Старые записи о созданных бекапах можно удалять sp_delete_backuphistory:

DECLARE @DateBefore DATETIME 
SET @DateBefore = DATEADD(DAY, -120, GETDATE())

EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @DateBefore

Но нужно помнить про один нюанс — при удалении базы данных записи о ее резервных копиях не удаляются из msdb:

USE [master]
GO

IF DB_ID('backup_test') IS NOT NULL BEGIN
    ALTER DATABASE [backup_test] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE [backup_test]
END
GO

CREATE DATABASE [backup_test]
GO

BACKUP DATABASE [backup_test] TO DISK = N'backup_test.bak'
GO

DROP DATABASE [backup_test]
GO

SELECT *
FROM msdb.dbo.backupset
WHERE database_name = 'backup_test'

В моем случае, когда базы часто создаются и удаляются это может приводить к росту msdb. В ситуации, когда информация о бекапах не нужна, ее можно удалить хранимкой sp_delete_database_backuphistory:

EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'backup_test'

Небольшие выводы...

Системная база msdb используется многими компонентами SQL Server, например, такими как Service Broker, SQL Server Agent и Database Mail. Стоит отметить, что нет готового плана обслуживания, который бы учитывал написанное выше, поэтому важно периодически проводить профилактические меры. В моем случае, после удаления лишней информации и усечения файла размер msdb стал 200 Мб против изначальных 42 Гб.

Надеюсь из этого поста вышла поучительная история о пользе постоянного администрирования… не только пользовательских, но и системных баз данных.

Если хотите поделиться этой статьей с англоязычной аудиторией, то прошу использовать ссылку на перевод:
How to reduce MSDB size from 42Gb to 200Mb