Недавно выдалась минутка посмотреть почему старый тестовый сервер безбожно тормозил… К нему я не имел никакого отношения, но меня одолевал спортивный интерес разобраться, что с ним не так.
Первым делом открыл 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. Старые записи о созданных бекапах можно удалять следующей командой и проблем тут никаких не возникает:
DECLARE @DateBefore DATETIME SET @DateBefore = DATEADD(DAY, -120, GETDATE()) EXEC msdb.dbo.sp_delete_backuphistory @oldest_date = @DateBefore Но нужно помнить об одной особенности SQL Server. При удалении базы данных записи о ее резервных копиях не удаляются из 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 стал 200 Мб против изначальных 42 Гб.
Надеюсь из этого поста вышла поучительная история о пользе постоянного администрирования…
ссылка на оригинал статьи http://habrahabr.ru/post/273633/