Name	Name	Last commit message	Last commit date
parent directory ..
Doc	Doc
Migrations	Migrations
CreateServiceDatabaseScript.sql	CreateServiceDatabaseScript.sql
Readme.md	Readme.md

Служебная база обслуживания и мониторинга

База данных предоставляет набор служебных объектов для выполнения настраиваемых операций обслуживания и мониторинга.

Служебная база обслуживания и мониторинга

Далее рассмотрим примеры работы с этой базой данных.

Создание базы

Имеется два пути создания служебной базы.

Простой скрипт

Самый простой и быстрый способ - это использовать скрипт, создающий все необходимые объекты в пустой базе данных.

Таким образом, алгоритм создания служебно базы будет следующим:

Создаем вручную пустую базу с именем SQLServerMaintenance.
В котнексте созданной базы выполняем скрипт.

Все, база готова для использования.

Плюсы:

Простота создания базы с нуля.

Минусы:

Нет возможности простой установки обновлений на эту базу данных. При необходимости обновления чаще всего нужно будет "вычленять" изменения вручную из новых версий скриптов.

Миграции с поддержкой версионирования и обновления

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

V1_3_1_1__MigrationName.sql.

или

V<ВерсияМиграции>__<ИмяМиграции>.sql

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

Запускать последовательно скрипты мигарций на пустой базе. А при появлении новых миграций - запускать их уже на существующей базе для обновления.
Использовать решение Evolve, которе авматизирует процесс применения данных миграций на базе данных. Вместо ручного запуска можно выполнить команду вида:

# **migrate** - говорим, что нужно применить миграции.
# **sqlserver** - указываем тип СУБД (есть поддержка PostgreSQL и многих других)
# **-c** (строка подключения) - параметры подключения к базе.
- **-l** - каталог со списком миграций.

./evolve migrate sqlserver -c "Server=localhost;Database=SQLServerMaintenance;User Id=sa;Password=<ПарольПользователя>;" -l "/home/ypermitin/Develop/SQLServerTools/SQL-Server-Maintenance/Service-Database/Migrations"

Таким образом, с помощью миграций можно в простой манере создать и поддерживать актуальность базы данных. При этом версия изменений базы данных будет наглядна сохранена в системе контроля версий.

Плюсы:

Прозрачный процесс создания и обновления базы данных.
Возможность автоматизации обновления базы. CI/CD и все дела.

Минусы:

Сложнее, чем один единственный скрипт.

Рекомендую использовать именно миграции.

Доступные операции

Служебная база содержит следующие доступные операции:

Обслуживание индексов
Обслуживание статистик
Сбор информации о размерах таблиц баз данных на сервере
Служебные действия по настройке и исправлению некоторых проблем
Бэкапирование
Очистка файлов

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

Рассмотрим примеры работы с ними.

Примеры использования

Сбор информации о размере таблиц

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

EXECUTE [SQLServerMaintenance].[dbo].[sp_SaveDatabasesTablesStatistic]

В результате будет собрана подробная информация о размерах таблиц каждой базы на сервере. Результаты записываются в таблицу "DatabasesTablesStatistic". Вот пример данных: |

Period	DatabaseName	SchemaName	TableName	RowCnt	Reserved	Data	IndexSize	Unused
2022-01-03 10:31:36.1000000	SomeDatabase	dbo	_AccRgED9823	18917980	4461736	1645064	2816440	232
2022-01-03 10:31:36.1033333	SomeDatabase	dbo	_AccRg9786	4659752	3003232	1637968	1364824	440
2022-01-03 10:31:36.1033333	SomeDatabase	dbo	_AccRgAT92820	647989	327520	313936	9560	4024
2022-01-03 10:31:36.1033333	SomeDatabase	dbo	_AccRgAT93821	6251900	3011736	2957568	54112	56
2022-01-03 10:31:36.1033333	SomeDatabase	dbo	_Document9239_VT95716	1278	264280	263832	120	328

Таблица содержит информацию о каждой таблице базы данных на определенную дату:

Количество записей (RowCnt)
Размер данных, КБ (Data)
Размер индексов, КБ (IndexSize)
Неиспользованное место, КБ (Unused)
Места зарезервировано всего, КБ (Reserved). Reserved = Data + IndexSize + Unused.

Сбор информации о состоянии объектов базы данных

Анализ состояния объектов базы перед обслуживанием можем быть дорогостоящей и долгой операцией. Эта функция позволяет предварительно сохранить сотояние объектов в служебную базу, а потом использовать эту информацию при запуске скриптов обслуживания. Или для анализа состояния базы и эффективности самого обслуживания.

EXECUTE [SQLServerMaintenance].[dbo].[sp_FillDatabaseObjectsState] 
   @databaseName = 'bsl_2_4_2'

Таблица DatabaseObjectsState с сохраненными данными содержит:

Период сохранения записи
Имя базы
Имя таблицы
Имя объекта (обычно индекса)
Размер объекта в страницах (1 страница = 8 КБ)
Количество измененных строк с момента последнего обслуживания статистики
Средний процент фрагментации индекса
Признак поддержки онлайн-перестроения индекса

В общем, применению этой информации место всегда можно найти.

Сбор информации о соединения с базой данных

Процедура сохраняет срез всех соединения с базой данных в таблицу ConnectionsStatistic для последующего анализа.

EXECUTE [SQLServerMaintenance].[dbo].[sp_FillConnectionsStatistic]

Таблица ConnectionsStatistic с сохраненными данными содержит всю ту информацию, которая возвращается из системного объекта sys.dm_exec_requests.

Сбор информации о соединениях

Может потребоваться сбор информации о соединениях, например, для аудита доступа к базе или для получения информации о выполняемых запросах по конкретным соединениям на момент времени.

Для сбора имеется процедура sp_FillConnectionsStatistic, для вызова которой достаточно выполнить:

EXECUTE @RC = [dbo].[sp_FillConnectionsStatistic]

В результате в таблицу "ConnectionsStatistic" будут сохранены следующие данные:

id - идентификатор записи
Period - дата сохранения записи
InstanceName - имя инстанса SQL Server
QueryText - текст запроса
RowCountSize - количество возвращаемых записей
SessionId - номер сессии соединения с сервером баз данных
Status - статус выполнения запроса
Command - тип команды (INSERT, UPDATE, DELETE и другие)
CPU - затрачено времени ЦП
TotalElapsedTime - всего затрачено времени
StartTime - дата запуска операции
DatabaseName - имя базы данных
BlockingSessionId - идентификатор блокирующей сессии с сервером баз данных
WaitType - тип ожидания запроса
WaitResource - тип ожидания ресурса
OpenTransactionCount - количество открытых транзакций
Reads - количество прочитанных страниц
Writes - количество записанных страниц
LogicalReads - количество прочитанных страниц из кэша
GrantedQueryMemory - объем выделенной памяти
UserName - имя пользователя

Информация может быть использована для анализа работу пользователей на момент времени, количества соединений с базой данных и др.

Это лишь обобщенная информация. С помощью расширенных событий можно собрать более детальную информацию о происходящем.

Обслуживание индексов

Для обслуживания индексов используется процедура "sp_IndexMaintenance", которая позволяет указать:

Конкретную базу для обслуживания (@databaseName)
Интервал времени, в который доступна работа скрипта (@timeFrom и @timeTo)
Режим обслуживания индексов (@useOnlineIndexRebuild):
- (0) - стандартное обслуживание
- (1) - онлайн обслуживания и только для тех объектов, которые это поддерживают. Таблицы с устаревшими типами могут не поддерживать онлайн-перестроение и будут пропущены.
- (2) - обслуживание только тех объектов, в которых онлайн-перестроение не поддерживается.
- (3) - обслуживание индексов с онлайн-перестроением. Так где операция недоступна будет выполнено классическое перестроение индексов.
Указать минимальный (@minIndexSizePages) и максимальный (@maxIndexSizePages) размер индекса при обслуживании в количестве страниц (1 страница = 8 КБ, @minIndexSizePages и @maxIndexSizePages, по умолчанию не заданы)
Минимальный размер процента фрагментации (@fragmentationPercentMinForMaintenance, по умолчанию 10%)., с которого начинается любое обслуживание. Обычно это операция реорганизации, если процент не превышает процента, с которого начинается перестроение (по умолчанию 30%).
Максимальный размер индекса в страницах, для которых может применяться операция реорганизации (@maxIndexSizeForReorganizingPages). По умолчанию 50 ГБ, то есть 6553600 страниц. Все что большего размера будет использовать только перестроение индекса, чтобы процесс обслуживания не подвисал на долгое время.
Максимальный размер фрагментации (@fragmentationPercentForRebuild, по умолчанию 30%), с которого начинается перестроение (до этого операция реорганизации).
Степень параллелизма (@maxDop), т.к. количество потоков для операции обслуживания (@maxDop, по умолчанию 8).
Использование сохраненных ранее данных о состоянии объектов для операции обслуживания индекса (@usePreparedInformationAboutObjectsStateIfExists). Если включено и есть ранее сохраненные данные о состоянии объектов за последние 12 часов, то повторный анализ базы данных не будет выполнен. Позволяет избежать излишних операций ввода/вывода.
Условие для отбора таблиц базы для обслуживания (@ConditionTableName, по умолчанию “LIKE ''%''“).
Условие для отбора индексов базы для обслуживания (@ConditionIndexName, по умолчанию “LIKE ''%''“).
Режим ожидания при онлайн перестроении индексов (@onlineRebuildAbortAfterWaitMode):
- 0 - операция обслуживания будет бесконечно ожидать другие запросы.
- 1 - операция обслуживания завершит себя по истечении таймаута ожидания.
- 2 - операция обслуживания завершит блокирующий процесс по истечени таймаута ожидания.
Максимальный процент заполнения журнала транзакций, после которого операции обслуживания останавливаются (@maxTransactionLogSizeUsagePercent).
Использование возобновляемого перестроения индексов, если эта функциональность доступна (@useResumableIndexRebuildIfAvailable). Позволяет перестраивать индексы в несколько шагов с прерыванием операции, что позволяет останавливаться при перестроении и не заполнять полностью лог транзакций или разносить нагрузку от обслуживания на разное время. Подробнее можно прочитать здесь. По умолчанию не используется.
Указание явного процента заполнения страниц при перестроении индексов (fill factor). Для разных индексов можно установить разный коэф. заполнения страниц в зависимости от ситуации. Если не заполнен, то используется общая настройка на уровне сервера.

Вот несколько простых примеров обслуживания индексов.

Обслуживание индексов во всех базах

Вот простой скрипт для обслуживания индексов во всех базах стандартным способом.

DECLARE @command varchar(max) 
SELECT @command = 
'USE [?]
EXECUTE [SQLServerMaintenance].[dbo].[sp_IndexMaintenance]
 @databaseName =''?''
'
EXEC sp_MSforeachdb @command

Другие параметры не указываются, т.к. значения по умолчанию для простых случаев подходят без изменений.

Обслуживание индексов в конкретной базе

Сделаем обслуживание для коркретной базы.

EXECUTE [SQLServerMaintenance].[dbo].[sp_IndexMaintenance] 
   @databaseName = 'bsl_2_4_2'
   -- Разрешаем запуск скрипта с 01:00:00 до 04:00:00
  ,@timeFrom = '01:00:00'
  ,@timeTo = '04:00:00'
   -- Убираем мин. процент фрагментации для обслуживания, чтобы
   -- скрипт обслуживал все индексы вне зависимости от фрагментации
  ,@fragmentationPercentMinForMaintenance = 0
   -- Переопределяем степень параллелизма на 4
  ,@maxDop = 4
   -- Включаем использвоание онлайн-перестроения где это возможно.
   -- Для объектов, где операция недоступна, будет использоваться стандартная операция перестроения
  ,@useOnlineIndexRebuild = 3

Этот вариант больше походит на реальную задачу настройки обслуживания.

Обслуживание индексов по конкретной таблице с учетом сохраненных данных о состоянии объектов

Добавим условие на обслуживание конкретной таблицы и использование, если есть, ранее сохраненной информации о состоянии объектов базы данных. Так мы избежим запуска анализа объектов базы данных "на горячую". Может пригодиться, если есть несколько запусков скриптов обслуживания и каждый раз анализировать состояния объектов нет смысла, а иногда и долго.

EXECUTE [SQLServerMaintenance].[dbo].[sp_IndexMaintenance] 
   @databaseName = 'bsl_2_4_2'
   -- Разрешаем запуск скрипта с 01:00:00 до 04:00:00
  ,@timeFrom = '01:00:00'
  ,@timeTo = '04:00:00'
   -- Убираем мин. процент фрагментации для обслуживания, чтобы
   -- скрипт обслуживал все индексы вне зависимости от фрагментации
  ,@fragmentationPercentMinForMaintenance = 0
   -- Переопределяем степень параллелизма на 4
  ,@maxDop = 4
   -- Включаем использвоание онлайн-перестроения где это возможно.
   -- Для объектов, где операция недоступна, будет использоваться стандартная операция перестроения
  ,@useOnlineIndexRebuild = 3,
  -- Использовать сохраненное состояние объектов из служебной базы
  ,@usePreparedInformationAboutObjectsStateIfExists = 1,
   -- Только индексы одной таблицы
  ,@ConditionTableName = 'LIKE ''_InfoRg1234'''

Своего рода точечное обслуживание для особых объектов.

Обслуживание индексов с отбором по таблице и индексу

Можно сделать отбор обслуживаемых объектом более точечным.

EXECUTE [SQLServerMaintenance].[dbo].[sp_IndexMaintenance] 
   @databaseName = 'bsl_2_4_2'
   -- Разрешаем запуск скрипта с 01:00:00 до 04:00:00
  ,@timeFrom = '01:00:00'
  ,@timeTo = '04:00:00'
   -- Убираем мин. процент фрагментации для обслуживания, чтобы
   -- скрипт обслуживал все индексы вне зависимости от фрагментации
  ,@fragmentationPercentMinForMaintenance = 0
   -- Переопределяем степень параллелизма на 4
  ,@maxDop = 4
   -- Включаем использвоание онлайн-перестроения где это возможно.
   -- Для объектов, где операция недоступна, будет использоваться стандартная операция перестроения
  ,@useOnlineIndexRebuild = 3,
  -- Использовать сохраненное состояние объектов из служебной базы
  ,@usePreparedInformationAboutObjectsStateIfExists = 1,
   -- Только индексы одной таблицы
  ,@ConditionTableName = 'LIKE ''%_InfoRg%'''
  ,@ConditionIndexName = 'LIKE ''%_1%'''

Таким образом, можно делать обслуживание только определенных индексов у определенных таблиц. Выше мы обслуживаем таблицы с именем, содержащим "_InfoRg", при этом индексы должны включать "_1".

Возобновляемое перестроение индексов

Начиная со SQL Server 2017 появился функционал возобновляемых операций перестроения индексов.

Для использования этого функционала нужно указать следующие параметры.

EXECUTE [SQLServerMaintenance].[dbo].[sp_IndexMaintenance] 
   @databaseName = 'bsl_2_4_2'
   -- Разрешаем запуск скрипта с 01:00:00 до 04:00:00
  ,@timeFrom = '01:00:00'
  ,@timeTo = '04:00:00'
   -- Убираем мин. процент фрагментации для обслуживания, чтобы
   -- скрипт обслуживал все индексы вне зависимости от фрагментации
  ,@fragmentationPercentMinForMaintenance = 0
   -- Переопределяем степень параллелизма на 4
  ,@maxDop = 4
   -- Включаем использвоание онлайн-перестроения где это возможно.
   -- Для объектов, где операция недоступна, будет использоваться стандартная операция перестроения
  ,@useOnlineIndexRebuild = 3
  -- Если флаг установлен, то для онлайн-операций обслуживания будет выполняться возобновляемое перестроение.
  -- Если операция перестроения была прервана, то при следующем запуске она будет продолжена 
  -- с того же места, где была остановлена.
  ,@useResumableIndexRebuildIfAvailable = 1

Состояние всех возобновляемых операций перестроения индексов можно посмотреть запросом:

SELECT 
  total_execution_time, 
  percent_complete, 
  name,
  state_desc,
  last_pause_time,
  page_count
FROM sys.index_resumable_operations;

Условия выполнения обслуживания

Для стабильной процедуры обслуживания можно установить условия выполнения, предотвращающие аварии и контролирующие стабильность выполнения.

EXECUTE [SQLServerMaintenance].[dbo].[sp_IndexMaintenance] 
   @databaseName = 'bsl_2_4_2',
   -- Минимальный размер объекта в кол. страниц ()
   @minIndexSizePages = 6400, -- 6400 * 8 КБ = 51200 КБ = 50 МБ
   @maxIndexSizePages = 6553600, -- 6553600 * 8 КБ = 52428800 КБ = 51200 МБ = 50 ГБ
   -- Макс. размер объекта, для которых выполняется реорганизация
   @maxIndexSizeForReorganizingPages = 6553600, -- (50 ГБ)
   -- Процент использования лога транзакций, до которого позволено выполнять
   -- операции обслуживания
   @maxTransactionLogSizeUsagePercent = 50,
   -- Сколько разрешено использовать в логе транзакций в МБ
   @maxTransactionLogSizeMB = 76800 -- 75 ГБ

Эти условия позволяют более тонко обслуживать объекты базы в зависимости от ситуации.

Указание процента заполнения страниц

В некоторых случаях имеет смысл уменьшить стандартный процент заполнения страниц (fill factor) при перестроении индексов со 100% до 90% или даже ниже. Это увеличит размер занимаемого дискового пространства объектами, но снизит увеличение фрагментации индексов при изменении данных в таблице. Позволяет снизить частоту и необходимость обслуживания объектов.

Для указания процента заполнения страниц в скрипте обслуживания индексов нужно установить параметр @fillFactorForIndex (от 1 до 100). По умолчанию используется настройка, заданная на уровне инстанса SQL Server.

EXECUTE [SQLServerMaintenance].[dbo].[sp_IndexMaintenance] 
   @databaseName = 'bsl_2_4_2',
   -- Процент заполнения страниц (fill factor)
   @fillFactorForIndex = 80

Если комбинировать эту настройку с условиями на индексы и таблицы, то можно устанавливать ее точечно для разных объектов, в зависимости от ситуации.

Логи обслуживания индексов

Лог операций обслуживания индексов хранится в таблице "MaintenanceActionsLog" и имеет примерно такой вид.

Id	Period	TableName	IndexName	Operation	RunDate	StartDate	FinishDate	DatabaseName	IndexFragmentation	SQLCommand	TransactionLogUsageBeforeMB	TransactionLogUsageAfterMB
1	03.01.2022 11:36	_InfoRg931551	_InfoRg931551_1	REBUILD INDEX	03.01.2022 11:36	03.01.2022 11:36	03.01.2022 11:36	SomeDatabase	99	ALTER INDEX [_InfoRg931551_1] ON [dbo].[_InfoRg31551] REBUILD WITH (MAXDOP=8)	10	230
2	03.01.2022 11:36	_InfoRg931551	_InfoRg931551_2	REBUILD INDEX	03.01.2022 11:36	03.01.2022 11:36	03.01.2022 11:36	SomeDatabase	90	ALTER INDEX [_InfoRg931551_2] ON [dbo].[_InfoRg31551] REBUILD WITH (MAXDOP=8)	230	340
3	03.01.2022 11:36	_InfoRg929031	_InfoRg929031_1	REBUILD INDEX	03.01.2022 11:36	03.01.2022 11:36	03.01.2022 11:36	SomeDatabase	89	ALTER INDEX [_InfoRg929031_1] ON [dbo].[_InfoRg29031] REBUILD WITH (MAXDOP=8)	340	350
4	03.01.2022 11:36	_InfoRg929031	_InfoRg929031_2	REBUILD INDEX	03.01.2022 11:36	03.01.2022 11:36	03.01.2022 11:36	SomeDatabase	84	ALTER INDEX [_InfoRg929031_2] ON [dbo].[_InfoRg29031] REBUILD WITH (MAXDOP=8)	350	600
5	03.01.2022 11:36	_InfoRg929073	_InfoRg929073_1	REBUILD INDEX	03.01.2022 11:36	03.01.2022 11:36	03.01.2022 11:36	SomeDatabase	82	ALTER INDEX [_InfoRg929073_1] ON [dbo].[_InfoRg929073] REBUILD WITH (MAXDOP=8)	600	9399

Лог содержит информацию:

Период операции (Period)
Имя таблицы (TableName)
Имя индекса (IndexName)
Имя операции. Для индексов это либо "REORGANIZE INDEX", либо "REBUILD INDEX" (Operation)
Дата запуска обслуживания (RunDate)
Дата старта (StartDate)
Дата завершения. Если дата завершения NULL, то значит операция обслуживания не была завершена успешно (FinishDate)
Имя базы (DatabaseName)
Признак использования операции онлайн-перестроения индекса (UseOnlineRebuild)
Произвольный комментарий, обычно заполняется при ошибках (Comment)
Процент фрагментации индекса перед обслуживанием (IndexFragmentation)
Количество измененных строк до обслуживания индекса (RowModCtr)
SQL-команда обслуживания индекса (SQLCommand) ы

Лог позволяет определить эффективность обслуживания статистики и предпринять различные меры по ее актуализации.

Обслуживание статистик

Для обслуживания объектов статистики используется процедура "sp_StatisticMaintenance", которая позволяет указать:

Конкретную базу для обслуживания (databaseName)
Интервал времени, в который доступна работа скрипта (timeFrom и timeTo)
Режим обслуживания статистики (mode, 0 - по выборке, 1 - полное сканирование, по умолчанию 0) (по выборке данных или полным сканированием). Полное сканирование дает более точный результат, но может занять длительное время, в то время как анализ выборки данных значительно ускоряет этот процесс. Подробнее здесь.
Указать произвольное условие обслуживания на имя таблиц (если нужно настроить гибкое обслуживания по таблицам). (ConditionTableName, по умолчанию не используется)

Вот несколько простых примеров.

Обновим статистику во всех базах

Следующий скрипт выполняет обновление статистики во всех базах на сервере по выборке данных.

DECLARE @command varchar(max) 
SELECT @command = 
'USE [?]
EXECUTE [SQLServerMaintenance].[dbo].[sp_StatisticMaintenance] 
   @databaseName =''?''
' 
EXEC sp_MSforeachdb @command

По умолчанию обновление выполняется в режиме выборки данных, поэтому скрипт отработает достаточно быстро.

Обновление статистики для конкретной базы полным сканированием

Настроим скрипт, который обновляет статистику полным сканированием в определенной базе данных, при этом разрешенное время работы скрипта с 02:00:00 до 03:00:00.

EXECUTE [SQLServerMaintenance].[dbo].[sp_StatisticMaintenance] 
   @databaseName = 'SomeDatabase'
  ,@timeFrom = '02:00:00'
  ,@timeTo = '03:00:00'
  ,@mode = 1 -- 0 устанавливаем режим анализа выборки данных, 1 - режим полного сканирования

Обновление статистики для конкретной таблицы

Иногда бывает необходимо выполнять обслуживание статистик для опредленной таблицы отдельно от основного обслуживания.

EXECUTE [SQLServerMaintenance].[dbo].[sp_StatisticMaintenance] 
   @databaseName = 'SomeDatabase'
  ,@mode = 1 -- 0 устанавливаем режим анализа выборки данных, 1 - режим полного сканирования
  ,@ConditionTableName = '= ''TestTable''' -- Условие на имя таблицы
GO

Настройка приоритета и исключений для индексов

В таблице "MaintenanceIndexPriority" можно настроить приоритеты и признак исключения обслуживания для индексов.

Имя базы данных (DatabaseName)
Имя таблицы (TableName)
Имя индекса (IndexName)
Приоритет (Priority). Если приоритет ниже или равен 10, то для индекса будет считаться критичным и к нему будет по возможности использоваться операция перестроения. Операция реорганизации не будет применена. При выборке индексов для обслуживания, порядок обслуживания будет в первую очередь определяться приоритетом, а только после количеством изменений для этого индекса.
Исключен из обслуживания (Exclude). Если установить в 1, то индекс не будет обслуживаться.

Логи обслуживания статистик

Лог операций обслуживания статистики хранится в таблице "MaintenanceActionsLog" и имеет примерно такой вид.

	Id	Period	TableName	IndexName	Operation	RunDate	StartDate	FinishDate	DatabaseName	RowModCtr	SQLCommand	TransactionLogUsageBeforeMB	TransactionLogUsageAfterMB
1	1	03.01.2022 10:48	service_broker_map	I_CLUST	UPDATE STATISTICS	03.01.2022 10:48	03.01.2022 10:48	03.01.2022 10:48	tempdb	129	UPDATE STATISTICS [sys].[service_broker_map] [I_CLUST]	1	2
2	2	03.01.2022 10:48	service_broker_map	I_SECONDARY	UPDATE STATISTICS	03.01.2022 10:48	03.01.2022 10:48	03.01.2022 10:48	tempdb	129	UPDATE STATISTICS [sys].[service_broker_map] [I_SECONDARY]	2	4
3	3	03.01.2022 10:48	syscachedcredentials	PK__syscache__F6D56B560A8C29D8	UPDATE STATISTICS	03.01.2022 10:48	03.01.2022 10:48	03.01.2022 10:48	msdb	10638	UPDATE STATISTICS [dbo].[syscachedcredentials] [PK__syscache__F6D56B560A8C29D8]	4	6
4	4	03.01.2022 10:48	syscachedcredentials	_WA_Sys_00000004_01142BA1	UPDATE STATISTICS	03.01.2022 10:48	03.01.2022 10:48	03.01.2022 10:48	msdb	393	UPDATE STATISTICS [dbo].[syscachedcredentials] [_WA_Sys_00000004_01142BA1]	6	9
5	5	03.01.2022 10:48	syscollector_blobs_internal	PK_syscollector_blobs_internal_paremeter_name	UPDATE STATISTICS	03.01.2022 10:48	03.01.2022 10:48	03.01.2022 10:48	msdb	1	UPDATE STATISTICS [dbo].[syscollector_blobs_internal] [PK_syscollector_blobs_internal_paremeter_name]	9	10

Лог содержит информацию:

Период операции (Period)
Имя таблицы (TableName)
Имя индекса или имя служебной статистики, если не относится к индексу (IndexName)
Имя операции. Для статистики это всегда "UPDATE STATISTICS" (Operation)
Дата запуска обслуживания (RunDate)
Дата старта (StartDate)
Дата завершения. Если дата завершения NULL, то значит операция обслуживания не была завершена успешно (FinishDate)
Имя базы (DatabaseName)
Произвольный комментарий, обычно заполняется при ошибках (Comment)
Количество измененных строк до обслуживания статистики (RowModCtr)
SQL-команда обслуживания объекта статистики (SQLCommand)
Размер лога транзакций до начала операции в МБ (TransactionLogUsageBeforeMB)
Размер лога транзакций после завершения операции в МБ (TransactionLogUsageAfterMB)

Некоторые другие поля в логе обслуживания относятся к индексам и для обслуживания статистики не заполняются.

Сервисные функции

Различные сервисные функции для упрощения сопровождения SQL Server.

Контроль логов транзакций

В операциях обслуживания индекса процедуры "sp_IndexMaintenance" имеются несколько параметров для контроля работы:

Время выполнения контролируется параметрами начала и окончания работы скрипта (@timeFrom и @timeTo).
Условия выполнения при заполненном логе транзакций контролируются параметром @maxTransactionLogSizeUsagePercent, который содержит процент заполнения журнала транзакций, после которого операции обслуживания останавливаются.

Данный вид контроля работает отлично, но не во всех случаях. Например, если перестроение индекса идет длительное время и журнал транзакций при этом уже заполнен более чем на указанный в ограничениях процент, то никаких проверок по логу транзакций не сработает. Это происходит потому что процент заполнения лога транзакций проверяется перед каждой операцией обслуживания, а не во время их выполнения.

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

Сначала в таблице LogTransactionControlSettings нужно указать список параметров для контроля:

DatabaseName - имя базы данных, для которой необходимо выполнять контроль.
MinDiskFreeSpace - минимальный объем свободного места на диске (в МБ), на котором расположен файл лога транзакций.
MaxLogUsagePercentThreshold - максимальный процент использования файла лога транзакций, после которого начинается проверка свободного места на диске. По умолчанию 90%.

Затем необходимо запускать процедуру контроля использования логов транзакций. Можно это делать вручную, но лучше сделать задание на автоматический запуск раз в 15 секунд. Контроль для всех баз выглядит так:

EXECUTE [SQLServerMaintenance].[dbo].[sp_ControlTransactionLogUsage]

Имеется возможность указать конкретуню базу и включить вывод отладочных сообщений.

EXECUTE [SQLServerMaintenance].[dbo].[sp_ControlTransactionLogUsage] 
   @databaseNameFilter = 'BSL-ORIG'
  ,@showDiagnosticMessages = 1

Отладочные сообщения нужны лишь для помощи отладки проверок.

Сбрасываться будут только текущие запросы обслуживания индексов (операции перестроения и организации индексов в текущей базе). При каждом запуске выполняется проверка для каждого отдельного файла лога транзакций в базе (файлов логов транзакций может быть несколько):

Место на диске больше минимального значения, указанного в таблице настроек.
Если места свободного меньше, чем нужно, то выполняется проверка заполненности лога транзакций. Если лог заполнен более чем указанный % в настройках, то все запросы обслуживания баз данных принудительно завершаются.
Кроме этого выполняется проверка заполнености лога транзакций с учетом максимального его размера. Максимальный размер лога определяется ограничениями роста файла лога транзакций, которые могут быть установлены явно или установлены не явно. Например, если ограничений не установлено, то максимальный размер файла лога равен 2 ТБ (это системные ограничения). В тех случаях, если файл лога транзакций становится заполненным более чем на 90% от его максимально возможного размера, то текущие операции обслуживания также прекращаются.

Таким образом, можно контролировать рост файла лога транзакций не допуская его переполнения до 100%. Особенно это может быть критично, если база включена в группу доступности AlwaysOn. При заполнении лога транзакций до 100% ошибку можно будет "вылечить" чаще всего путем "разбора" группы доступности и следующим освобождением файла лога. Такой маневр иногда может стоить большого количества времени и простоя реплик.

Реорганизация индексов и блокировки на уровне страниц

В некоторых базах данных у иднексов может быть отключен параметр AllowPageLocks. При выключенном параметре SQL Server не сможет выполнять блокировки на уровне страниц для этого объекта, а следовательно невозможно выполнение операции реорганизации индексов, которая блокировки на уровне страниц и использует.

Для решения данной проблемы в операциях обслуживания процедуры sp_IndexMaintenance реализован следующий алгоритм:

Если для индекса необходимо выполнить операцию реорганизации, а параметр AllowPageLocks у объекта выключен, то перед этим выполняется включение этой настройки.

ALTER INDEX [IndexName] ON [dbo].[TableName] SET (ALLOW_PAGE_LOCKS = ON);

Выполняем непосредственно операцию реорганизации индекса.

ALTER INDEX [IndexName] ON [dbo].[TableName] REORGANIZE

Возвращаем настройку AllowPageLocks в исходное состояние.

ALTER INDEX [IndexName] ON [dbo].[TableName] SET (ALLOW_PAGE_LOCKS = OFF);

По итогу объект будет иметь те же настройки, которые были у него до запуска обслуживания. При этом, если настройка AllowPageLocks у объекта перед запуском обслуживания была включена, то перечисленные дополнительные действия не выполняются.

Если в момент операции обслуживания произойдет сбой или соединение будет завершено администратором, то операция восстановления исходного состояния настройки AllowPageLocks будет находиться в дополнительной очереди на выполнение (в таблице MaintenanceActionsToRun). При следующем запуске любого обслуживания индексов для базы данных (то есть вызова процедуры sp_IndexMaintenance) отложенная команда будет выполнена и настройка восстановлена. Это сделано для максимальной надежности работы обслуживания, чтобы оно не ломало настройки объектов в случае сбоя.

Для проверки работы механизма отсложенных команд обслуживания можно смотреть ту же таблицу логов, что и для остальных операций обслуживания - MaintenanceActionsLog. В ней для подобных операций в колонке Operation будет содержаться значение вида MAINTENANCE ACTION TO RUN (REORGANIZE INDEX), а в колонке SQLCommand можно увидеть исходную команду, которая была выполнена.

Вообще отключение возможности блокировок на уровне страниц является точечным решением и не применяется повсеместно, т.к. ведет к увеличению потребления памяти СУБД и некоторым другим эффектам. Отключение блокировок на уровне страниц решение часто спорное, если используется для всей базы данных. Вот дополнительная информация по этой теме:

Принимая решение об отключении блокировок на уровне страниц для объекта нужно это обосноваь конкретной необходимостью и не принимать в качестве универсального решения. По ссылкам Выше можно найти более подробную информацию, а я лишь процитирую Remus Rusanu и дам [ссылку на ответ]:

I would never turn this OFF on my database. The solution is always to properly design the schema and the queries so that scans (which are the culprits for escalation) don't occur to start with.

Думайте, что делаете. А когда делаете - думайте еще!

Исправление системного кэша объектов для реплик AlwaysOn

При выполнении запросов SELECT к репликам AlwaysOn, находящимся в режиме асинхронной передачи данных, могут возникнуть ошибки вида.

Не удалось найти статистику "_WA_Sys_00000007_7C1B37B1" в системных каталогах.DB-Lib error message 20018, severity 16:
General SQL Server error: Check messages from the SQL Server

или

Could not locate statistics "_WA_Sys_00000007_7C1B37B1" in the system catalogs. DB-Lib error message 20018, severity 16:
General SQL Server error: Check messages from the SQL Server


Примечание: _WA_Sys_00000007_7C1B37B1 - имя автоматически сгенерированного объекта статисти, которое может отличаться при появлении ошибки.

Причина проблемы - это активные транзакции, которые не позволяют применить записи журнала и обновить недействительный кэш объектов статистики на вторичной реплике. Подробная информация есть в официальной документации.

Для исправления ситуаций можно запускать следующую хранимую процедуру с некоторой периодичностью (например, раз в 30 минут).

EXECUTE [dbo].[sp_FixMissingStatisticOnAlwaysOnReplica] 
GO

При таком вызове будут проверены все базы данных, учавствующие в группах доступности AlwaysOn и использующие асинхронную передачи данных. Проверка выполняет поиск объектов статистики, которые по факту удалены, но до сих пор имеет информацию в кэше объектов базы данных.

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

EXECUTE [dbo].[sp_FixMissingStatisticOnAlwaysOnReplica] 
   @databaseName = 'MyProdDatabase'
GO

Информация о найденных "битых" объектах статистики будет сохраняться в таблицу AlwaysOnReplicaMissingStats в следующем виде:

DatabaseName - имя проблемной базы данных.
TableName - имя проблемной таблицы.
StatsName - имя проблемного объекта статистики.
CreatedDate - дата обнаружения проблемы.

При обнаружении проблемы будет вызвана системная процедура FREESYSTEMCACHE(<ИмяБазы>).

Бэкапирование

Для упрощения решения некоторых задач с бэкапированием, сделана оберточная процедура для упрощенного вызова формирования бэкапов всех типов (полный, разностный или лога транзакций) со всеми доступными опциями для формирования бэкапов.

Ниже несколько примеров использования данной процедуры.

Формирование бэкапов

Ниже самый просто пример формирования бэкапа с указанием минимального количества параметров.

-- Формирование полного бэкапа
EXECUTE [SQLServerMaintenance].[dbo].[sp_BackupDatabase] 
   -- Имя базы данных. Обязательный параметр.
   @databaseName = 'FIASToolSetService_Prod'
   -- Каталог для хранения бэкапов. Обязательный параметр.
  ,@backupDirectory = 'C:\Backup'
  -- Тип бэкапа. Обязательный параметр.
  -- Возможные значения:
  --	* FULL - полный бэкап
  --	* DIFF - разностный бэкап
  --	* TRN - бэкап лога транзакций
  ,@backupType = 'FULL'

-- Разностного
EXECUTE [SQLServerMaintenance].[dbo].[sp_BackupDatabase] 
   @databaseName = 'FIASToolSetService_Prod'
  ,@backupDirectory = 'C:\Backup'
  ,@backupType = 'DIFF'

-- Лога транзакций
EXECUTE [SQLServerMaintenance].[dbo].[sp_BackupDatabase] 
   @databaseName = 'FIASToolSetService_Prod'
  ,@backupDirectory = 'C:\Backup'
  ,@backupType = 'TRN'

В результате будет создан каталог C:\Backup\FIASToolSetService_Prod с файлом полгого бэкапа вида FIASToolSetService_Prod_backup_2023_08_31_231757_917514.bak.

Также есть более гибкие возможности настройки формирования бэкапа. В листинге ниже приведены все возможности процедуры.

EXECUTE [SQLServerMaintenance].[dbo].[sp_BackupDatabase] 
   -- Имя базы данных. Обязательный параметр.
   @databaseName = 'FIASToolSetService_Prod'
   -- Каталог для хранения бэкапов. Обязательный параметр.
  ,@backupDirectory = 'C:\Backup'
  -- Тип бэкапа. Обязательный параметр.
  -- Возможные значения:
  --	* FULL - полный бэкап
  --	* DIFF - разностный бэкап
  --	* TRN - бэкап лога транзакций
  ,@backupType = 'FULL'
  -- Использовать подкаталоги для каждой базы данных.
  -- Не обязательный параметр, по умолчанию используется.
  ,@useSubdirectory = 1
  -- Использование сжатия бэкапов. Не обязательный параметр.
  -- Возможные значения:
  -- AUTO - использовать глобальную настройку на сервере. Значение по умолчанию.
  -- ENABLE - использовать сжатие.
  -- DISABLE - НЕ использовать сжатие.
  ,@backupCompressionType = 'ENABLE'
  -- Режим только копирования. Не обязательный. По умолчанию выключен. Не обязательный параметр.
  -- В даном режиме формирование бэкапа не будет влиять на цепочку транзакций.
  ,@copyOnly = 0
  -- Сформировать контрольную сумму. По умолчанию выключен. Не обязательный параметр.
  ,@checksum = 0
  -- Продолжить при возникновении ошибок. По умолчанию выключен. Не обязательный параметр.
  ,@continiueOnError = 0  
  -- Проверять бэкап после формирования. По умолчаниювыключен. Не обязательный параметр.
  ,@verify = 1  
  -- Только показать текст скрипта для формирования бэкапа.
  -- Сам бэкап при этом сформирован не будет.
  ,@showScriptOnly = 1

  -- Параметры для управления потоком резервного копирования.
  -- Могут использоваться для оптимизации времени формирования бэкапа,
  -- но со значительным влиянием на работу ситсемы ввода-вывода.
  -- Подробнее можете прочитать здесь: 
  -- https://www.mssqltips.com/sqlservertip/4935/optimize-sql-server-database-restore-performance/
  --,@blockSize
  --,@maxTransferSize
  --,@bufferCount

Практически все параметры дублируют настройки, которые присутствуют в стандартных планах обслуживания SQL Server.

Очистка бэкапов

Также для целей управления бэкапами добавлена процедура очистки устаревших файлов бэкапов.

Ниже примеры использования процедуры с описанием параметров.

EXECUTE [dbo].[sp_ClearFiles] 
  -- Каталог для очистки. Обязательный параметр.
   @folderPath = 'C:\Backup'
  -- Расширение файлов для удаления. Обязательный параметр.
  ,@fileExtension = 'bak'
  -- Количество дней хранения файлов. Не обязательный параметр.
  -- По умолчанию будут считаться устаревшими все файлы.
  ,@cutoffDateDays = 28

В данном случае будут удалены файлы полных бэкапов *.bak старше 30 дней. Это же можно достичь, указав явно дату, до которой файлы бэкапов будут считаться устаревшими.

DECLARE @cutoffDate datetime = DATEADD(week, -4, GETDATE());
EXECUTE [SQLServerMaintenance].[dbo].[sp_ClearFiles] 
  -- Каталог для очистки. Обязательный параметр.
   @folderPath = 'C:\Backup'
  -- Расширение файлов для удаления. Обязательный параметр.
  ,@fileExtension = 'bak'
  -- Дата, до которой файлы считаются устарешвими.
  -- Не обязательный параметр. По умолчанию - текущая дата.
  -- Если не заполнить, то удалятся все файлы без анализа даты создания.
  ,@cutoffDate = @cutoffDate

Вот полный синтаксис данной процедуры.

DECLARE @cutoffDate datetime = DATEADD(week, -4, GETDATE());
EXECUTE [dbo].[sp_ClearFiles] 
  -- Каталог для очистки. Обязательный параметр.
   @folderPath = 'C:\Backup'
  -- Расширение файлов для удаления. Обязательный параметр.
  ,@fileExtension = 'bak'
  -- Количество дней хранения файлов. Не обязательный параметр.
  -- По умолчанию будут считаться устаревшими все файлы.
  -- Одновремено можно указать либо @cutoffDateDays, либо @cutoffDate.
  ,@cutoffDateDays = 28
  -- Дата, до которой файлы считаются устарешвими.
  -- Не обязательный параметр. По умолчанию - текущая дата.
  -- Если не заполнить, то удалятся все файлы без анализа даты создания.
  -- Одновремено можно указать либо @cutoffDateDays, либо @cutoffDate.
  ,@cutoffDate = @cutoffDate
  -- Использовать для очистки подкаталоги первого уровня.
  -- По умолчанию включен. Не обязательный параметр.
  ,@includeSubfolders=1
  -- Только показать скрипт для удаления файлов.
  ,@scriptOnly = 1,
  -- Типы удаляемых файлов. 
  -- Есть вариант для файлов бэкапов (0, используется по умолчанию),
  -- либо файлы логов задания обслуживания (1).
  @fileType = 0

Почти все параметры дублируют стандартную задачу очистки SQL Server.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Service-Database

Service-Database

Readme.md

Служебная база обслуживания и мониторинга

Создание базы

Простой скрипт

Миграции с поддержкой версионирования и обновления

Доступные операции

Примеры использования

Сбор информации о размере таблиц

Сбор информации о состоянии объектов базы данных

Сбор информации о соединения с базой данных

Сбор информации о соединениях

Обслуживание индексов

Обслуживание индексов во всех базах

Обслуживание индексов в конкретной базе

Обслуживание индексов по конкретной таблице с учетом сохраненных данных о состоянии объектов

Обслуживание индексов с отбором по таблице и индексу

Возобновляемое перестроение индексов

Условия выполнения обслуживания

Указание процента заполнения страниц

Логи обслуживания индексов

Обслуживание статистик

Обновим статистику во всех базах

Обновление статистики для конкретной базы полным сканированием

Обновление статистики для конкретной таблицы

Настройка приоритета и исключений для индексов

Логи обслуживания статистик

Сервисные функции

Контроль логов транзакций

Реорганизация индексов и блокировки на уровне страниц

Исправление системного кэша объектов для реплик AlwaysOn

Бэкапирование

Формирование бэкапов

Очистка бэкапов

Files

Service-Database

Directory actions

More options

Directory actions

More options

Latest commit

History

Service-Database

Folders and files

parent directory

Readme.md

Служебная база обслуживания и мониторинга

Создание базы

Простой скрипт

Миграции с поддержкой версионирования и обновления

Доступные операции

Примеры использования

Сбор информации о размере таблиц

Сбор информации о состоянии объектов базы данных

Сбор информации о соединения с базой данных

Сбор информации о соединениях

Обслуживание индексов

Обслуживание индексов во всех базах

Обслуживание индексов в конкретной базе

Обслуживание индексов по конкретной таблице с учетом сохраненных данных о состоянии объектов

Обслуживание индексов с отбором по таблице и индексу

Возобновляемое перестроение индексов

Условия выполнения обслуживания

Указание процента заполнения страниц

Логи обслуживания индексов

Обслуживание статистик

Обновим статистику во всех базах

Обновление статистики для конкретной базы полным сканированием

Обновление статистики для конкретной таблицы

Настройка приоритета и исключений для индексов

Логи обслуживания статистик

Сервисные функции

Контроль логов транзакций

Реорганизация индексов и блокировки на уровне страниц

Исправление системного кэша объектов для реплик AlwaysOn

Бэкапирование

Формирование бэкапов

Очистка бэкапов