DBCC (Transact-SQL)
Язык Transact-SQL предоставляет инструкции DBCC, которые выступают в качестве консольных команд базы данных для SQL Server.
Инструкции консольных команд базы данных группируются по следующим категориям.
| Категория команды | Выполнение |
|---|---|
| Обслуживание | Обслуживание задач в базе данных, индексе или в файловой группе. |
| Разное | Вспомогательные задачи, например установка флага трассировки или удаление из памяти библиотеки DLL. |
| Informational | Задачи, собирающие и отображающие разные типы сведений. |
| Проверка | Проверочные операции в базе данных, таблице, индексе, каталоге, в файловой группе или распределение страниц базы данных. |
Команды DBCC принимают входные параметры и возвращают значения. Все команды DBCC могут принимать в качестве параметров как литералы в Юникоде, так и литералы в двухбайтовой кодировке.
Использование внутреннего моментального снимка базы данных в командах DBCC
Следующие команды DBCC выполняют операции с внутренним моментальным снимком базы данных, предназначенным только для чтения, который создает компонент Компонент Database Engine. Тем самым предотвращаются проблемы блокировки и параллелизма при выполнении этих команд. Дополнительные сведения см. в разделе Моментальные снимки базы данных (SQL Server).
При выполнении одной из этих команд DBCC компонент Компонент Database Engine создает моментальный снимок базы данных и приводит ее в согласованное состояние на уровне транзакций. Затем команда DBCC выполняет проверку этого моментального снимка. После завершения команды DBCC этот моментальный снимок удаляется.
Иногда внутренний моментальный снимок базы данных не требуется или его невозможно создать. В этом случае команда DBCC выполняется в отношении реальной базы данных. Если база данных находится в режиме в сети, команда DBCC использует блокировку таблиц для обеспечения согласованности проверяемых объектов. Это поведение то же самое, как если бы был указан параметр WITH TABLOCK.
Внутренний моментальный снимок базы данных не создается, если команда DBCC выполняется:
Команды DBCC используют блокировки таблиц, а не внутренние моментальные снимки базы данных, если выполняются:
Для запуска команды DBCC CHECKALLOC или эквивалентной части DBCC CHECKDB с параметром WITH TABLOCK требуется X-блокировка базы данных. Такую блокировку нельзя устанавливать для базы данных tempdb или master: это может привести к ошибкам во всех остальных базах данных.
Команда DBCC CHECKDB вызывает ошибку при выполнении в базе данных master, если невозможно создать внутренний моментальный снимок базы данных.
Формирование отчета о состоянии команд DBCC
В представлении каталога sys.dm_exec_requests содержатся сведения о ходе выполнения и текущем этапе выполнения команд DBCC CHECKDB, CHECKFILEGROUP и CHECKTABLE. В столбце percent_complete указывается процент выполнения команды, а в столбце command отображается текущий этап ее выполнения.
Определение единицы хода выполнения зависит от текущего этапа выполнения команды DBCC. Иногда отчет о состоянии формируется на уровне гранулярности страницы базы данных, на других этапах — на уровне гранулярности одной базы данных или исправления распределения пространства. В следующей таблице представлены все этапы выполнения и уровень гранулярности, на котором команда формирует отчет о состоянии.
| Этап выполнения | Описание | Уровень гранулярности отчетов о состоянии |
|---|---|---|
| DBCC TABLE CHECK | Во время этого этапа проверяется логическая и физическая согласованность объектов в базе данных. | Отчет о состоянии сформирован на уровне страниц базы данных. Значение отчета о состоянии обновляется через каждые 1000 проверенных страниц базы данных. |
| DBCC TABLE REPAIR | Во время этого этапа выполняются исправления базы данных, если указывается параметр REPAIR_FAST, REPAIR_REBUILD или REPAIR_ALLOW_DATA_LOSS и имеются ошибки на уровне объектов. | Отчет о состоянии сформирован на уровне отдельных исправлений. Счетчик обновляется для каждой завершенной операции исправления. |
| DBCC ALLOC CHECK | Во время этого этапа проверяются структуры распределения. Примечание. Те же проверки выполняет команда DBCC CHECKALLOC. | О состоянии не сообщается |
| DBCC ALLOC REPAIR | Во время этого этапа выполняются исправления базы данных, если указывается параметр REPAIR_FAST, REPAIR_REBUILD или REPAIR_ALLOW_DATA_LOSS и имеются ошибки на уровне распределения пространства. | О состоянии не сообщается. |
| DBCC SYS CHECK | Во время этого этапа проверяются системные таблицы базы данных. | Отчет о состоянии сформирован на уровне страниц базы данных. Значение отчета о состоянии обновляется через каждую 1 000 проверенных страниц базы данных. |
| DBCC SYS REPAIR | Во время этого этапа выполняются исправления базы данных, если указывается параметр REPAIR_FAST, REPAIR_REBUILD или REPAIR_ALLOW_DATA_LOSS и имеются ошибки на уровне системных таблиц. | Отчет о состоянии сформирован на уровне отдельных исправлений. Счетчик обновляется для каждой завершенной операции исправления. |
| DBCC SSB CHECK | Во время этого этапа проверяются объекты компонента SQL Server Service Broker. Примечание. Этот этап не выполняется при выполнении инструкции DBCC CHECKTABLE. | О состоянии не сообщается. |
| DBCC CHECKCATALOG | Во время этого этапа проверяется согласованность каталогов базы данных. Примечание. Этот этап не выполняется при выполнении инструкции DBCC CHECKTABLE. | О состоянии не сообщается. |
| DBCC IVIEW CHECK | Во время этого этапа проверяется логическая согласованность всех индексированных представлений базы данных. | Отчет о состоянии сформирован на уровне отдельных представлений баз данных. |
Информационные инструкции
Инструкции проверки
Инструкции обслуживания
Вспомогательные инструкции
DBCC PROCCACHE (Transact-SQL)
Отображает сведения о кэше процедур в табличном формате.
Синтаксические обозначения в Transact-SQL
Синтаксис
Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.
Аргументы
WITH
Позволяет указывать параметры.
NO_INFOMSGS
Подавляет все информационные сообщения с уровнями серьезности от 0 до 10.
Remarks
Кэш процедур служит для кэширования скомпилированных и исполняемых планов с целью ускорить выполнение пакетов. Элементы кэша процедур находятся на уровне пакета. Кэш процедур содержит следующие записи:
Результирующие наборы
В следующей таблице описаны столбцы в результирующем наборе.
| Имя столбца | Описание |
|---|---|
| num proc buffs | Общее количество страниц, используемое всеми записями кэша процедур. |
| num proc buffs used | Общее число страниц, занятых всеми используемыми в данный момент записями. |
| num proc buffs active | Только для обратной совместимости. Общее число страниц, занятых всеми используемыми в данный момент записями. |
| proc cache size | Общее число элементов в кэше процедур. |
| proc cache used | Общее число элементов, используемых в настоящий момент. |
| proc cache active | Только для обратной совместимости. Общее число элементов, используемых в настоящий момент. |
Разрешения
Команды SQL Server для очистки кэшей перед выполнением сравнения производительности
При сравнении времени выполнения двух разных запросов важно очистить кеш, чтобы убедиться, что выполнение первого запроса не влияет на производительность второго.
В поиске Google я мог найти эти команды:
На самом деле, мои запросы занимают более реалистичное время после нескольких выполнений, чем раньше. Однако я не уверен, что это рекомендуемая техника.
Какая лучшая практика?
Лично для общего запроса 2-е и последующие исполнения имеют большее значение.
Вы тестируете дисковый ввод-вывод или производительность запросов?
Если ты хочешь, ты можешь:
Поздний ответ, но может быть полезен для других читателей
Имейте в виду, что эту команду не следует запускать в производственной среде. Выполнение этой команды приведет в основном к пустому буферному кешу. Выполнение любого запроса после выполнения команды DBCC DROPCLEANBUFFERS будет использовать физическое чтение для возврата данных в кэш, который, скорее всего, будет намного медленнее, чем память.
Это может быть полезным инструментом разработки, поскольку вы можете многократно выполнять запрос в среде тестирования производительности без каких-либо изменений в скорости / эффективности из-за кэширования данных в памяти.
Мне всегда говорили использовать:
Используйте DBCC DROPCLEANBUFFERS для тестирования запросов с холодным буферным кешем без выключения и перезапуска сервера.
Чтобы удалить чистые буферы из пула буферов, сначала используйте CHECKPOINT для создания холодного буферного кэша. Это заставляет все грязные страницы для текущей базы данных быть записанными на диск и очищает буферы. После этого вы можете выполнить команду DBCC DROPCLEANBUFFERS, чтобы удалить все буферы из пула буферов.
Если вы хотите сравнить два разных запроса или процедуры, которые пытаются сделать одно и то же по-разному, они могут попасть на одни и те же страницы. Если вы наивно выполняете запрос № 1, а затем запрос № 2, второй может быть намного быстрее просто потому, что эти страницы были кэшированы первым запросом. Если вы очищаете кеш перед каждым выполнением, они начинают работать равномерно.
Если вы хотите протестировать производительность «горячего» кэша, обязательно выполняйте запросы несколько раз, чередуясь и отбрасывая первые несколько запусков. Средние результаты.
Скажем, у вас есть запрос, который занимает одну секунду для горячего кэша, но одну минуту для холодного кэша. Оптимизация, которая делает запрос в памяти на 20% медленнее, но связанный с вводом-выводом запрос на 20% быстрее, может быть большим выигрышем: при обычных операциях никто не заметит дополнительные 200 мс при нормальных обстоятельствах, но если что-то заставляет запрос запустить против диска, 48 секунд вместо 60 может спасти продажу.
Это менее важно для современных систем с десятками гигабайт памяти и относительно быстрым хранилищем SAN и SSD, но это все еще имеет значение. Если какой-то аналитик выполнит запрос массивной таблицы на вашу базу данных OLTP, которая уничтожит половину вашего буферного кеша, эффективные для хранения запросы вернут вас быстрее.
DBCC FREESYSTEMCACHE (Transact-SQL)
Удаляет все неиспользуемые элементы из всех кэшей. Компонент Компонент SQL Server Database Engine заранее автоматически очищает неиспользуемые элементы кэша в фоновом режиме, освобождая память для текущих записей. Но можно использовать эту команду, чтобы вручную удалить неиспользуемые записи из каждого кэша или из указанного кэша пула Resource Governor.
Синтаксические обозначения в Transact-SQL
Синтаксис
Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.
Аргументы
( ‘ALL’ [,pool_name ] )
Ключевое слово ALL указывает все поддерживаемые кэши.
Аргумент pool_name указывает кэш пула регулятора ресурсов. Освобождены будут только записи, связанные с этим пулом. Чтобы получить список доступных имен пулов, выполните следующую команду.
Большинство, но не все, кэши можно освободить по отдельности с помощью этой команды.
MARK_IN_USE_FOR_REMOVAL
Асинхронно освобождает текущие используемые элементы из соответствующих кэшей после того, как они перестают использоваться. Новые элементы, созданные в кэше после выполнения DBCC FREESYSTEMCACHE WITH MARK_IN_USE_FOR_REMOVAL, не будут затронуты.
NO_INFOMSGS
Подавляет вывод всех информационных сообщений.
Remarks
При выполнении инструкции DBCC FREESYSTEMCACHE очищается кэш планов для экземпляра SQL Server. Очистка кэша планов становится причиной перекомпиляции всех предстоящих планов выполнения и приводит к непредвиденному временному снижению производительности обработки запросов. Для каждого удаленного хранилища кэша в кэше планов журнал ошибок SQL Server содержит следующее информационное сообщение:
SQL Server has encountered %d occurrence(s) of cachestore flush for the ‘%s’ cachestore (part of plan cache) due to ‘DBCC FREEPROCCACHE’ or ‘DBCC FREESYSTEMCACHE’ operations.
Это сообщение добавляется в журнал каждые пять минут при сбросе кэша в течение этого интервала времени.
Наборы результатов
Инструкция DBCC FREESYSTEMCACHE возвращает следующее сообщение: «Выполнение инструкции DBCC завершено. Если инструкция DBCC выдает сообщения об ошибках, обратитесь к системному администратору».
Разрешения
Требует разрешения ALTER SERVER STATE на сервере.
Примеры
A. Освобождение неиспользуемых записей кэша из кэша пула регулятора ресурсов
В следующем примере показывается, как очищать кэши, выделенные указанному пулу ресурсов регулятора ресурсов.
Б. Освобождение записей из кэшей после прекращения их использования
В следующем примере используется предложение MARK_IN_USE_FOR_REMOVAL, чтобы освободить записи из всех текущих кэшей, когда записи становятся неиспользуемыми.
Регламентные операции на уровне субд для MS SQL Server, Оптимизация работы
Одной из часто встречающихся причин не оптимальной работы системы является неправильное или несвоевременное выполнение регламентных операций на уровне СУБД. Особенно важно выполнять эти регламентные процедуры в крупных информационных системах, которые работают под значительной нагрузкой и обслуживают одновременно большое количество пользователей. Специфика таких систем в том, что обычных действий, выполняемых СУБД автоматически (на основании настроек) оказывает недостаточно для эффективной работы.
Если в работающей системе наблюдаются какие-либо симптомы проблем с производительностью, следует проверить, что в системе правильно настроены и регулярно выполняются все рекомендуемые регламентные операции на уровне СУБД.
Выполнение регламентных процедур должно быть автоматизировано. Для автоматизации этих операций рекомендуется использовать встроенное средства MS SQL Server: Maintenance Plan. Существуют так же другие способы автоматизации выполнения этих процедур. В настоящей статье для каждой регламентной процедуры дан пример ее настройки при помощи Maintenance Plan для MS SQL Server 2005.
Для MS SQL Server рекомендуется выполнять следующие регламентные операции:
Обновление статистикОчистка процедурного КЭШаДефрагментация индексовРеиндексация таблиц базы данных
Рекомендуется регулярно контролировать своевременность и правильность выполнения данных регламентных процедур.
Обновление статистик
MS SQL Server строит план запроса на основании статистической информации о распределении значений в индексах и таблицах. Статистическая информация собирается на основании части (образца) данных и автоматически обновляется при изменении этих данных. Иногда этого оказывается недостаточно для того, что MS SQL Server стабильно строил наиболее оптимальный план выполнения всех запросов.
В этом случае возможно проявление проблем с производительностью запросов. При этом в планах запросов наблюдаются характерные признаки неоптимальной работы (неоптимальные операции).
Для того, чтобы гарантировать максимально правильную работу оптимизатора MS SQL Server рекомендуется регулярно обновлять статистики базы данных MS SQL.
Для обновления статистик по всем таблицам базы данных необходимо выполнить следующий SQL запрос:
Обновление статистик не приводит к блокировке таблиц, и не будет мешать работе других пользователей. Статистика может обновляться настолько часто, насколько это необходимо. Следует учитывать, что нагрузка на сервер СУБД во время обновления статистик возрастет, что может негативно сказаться на общей производительности системы.
Оптимальная частота обновления статистик зависит от величины и характера нагрузки на систему и определяется экспериментальным путем. Рекомендуется обновлять статистики не реже одного раза в день.
Приведенный выше запрос обновляет статистики для всех таблиц базы данных. В реально работающей системе разные таблицы требуют различной частоты обновления статистик. Путем анализа планов запроса можно установить, какие таблицы больше других нуждаются в частом обновлении статистик, и настроить две (или более) различных регламентных процедуры: для часто обновляемых таблиц и для всех остальных таблиц. Такой подход позволит существенно снизить время обновления статистик и влияние процесса обновления статистики на работу системы в целом.
Настройка автоматического обновления статистик (MS SQL 2005)
Запустите MS SQL Server Management Studio и подключитесь к серверу СУБД. Откройте папку Management и создайте новый план обслуживания:
Создайте субплан (Add Sublan) и назовите его «Обновление статистик». Добавьте в него задачу Update Statistics Task из панели задач:
Настройте расписание обновления статистик. Рекомендуется обновлять статистики не реже одного раза в день. При необходимости частота обновления статистик может быть увеличена.
Настройте параметры задачи. Для этого следует два раза кликнуть на задачу в правом нижнем углу окна. В появившейся форме укажите имя базу данных (или несколько баз данных) для которых будет выполняться обновление статистик. Кроме этого вы можете указать для каких таблиц обновлять статистики (если точно неизвестно, какие таблицы требуется указать, то устанавливайте значение All).
Обновление статистик необходимо проводить с включенной опцией Full Scan.
Сохраните созданный план. При наступлении указанного в расписании срока обновление статистик будет запущено автоматически.
Очистка процедурного КЭШа
Оптимизатор MS SQL Server кэширует планы запросов для их повторного выполнения. Это делается для того, чтобы экономить время, затрачиваемое на компиляцию запроса в том случае, если такой же запрос уже выполнялся и его план известен.
Возможна ситуация, при которой MS SQL Server, ориентируясь на устаревшую статистическую информацию, построит неоптимальный план запроса. Этот план будет сохранен в процедурном КЭШе и использован при повторном вызове такого же запроса. Если Вы обновили статистику, но не очистили процедурный кэш, то SQL Server может выбрать старый (неоптимальный) план запроса из КЭШа вместо того, чтобы построить новый (более оптимальный) план.
Таким образом, рекомендуется всегда после обновления статистик очищать содержимое процедурного КЭШа.
Для очистки процедурного КЭШа MS SQL Server необходимо выполнить следующий SQL запрос:
Этот запрос следует выполнять непосредственно после обновления статистики. Соответственно, частота его выполнения должна совпадать с частотой обновления статистики.
Настройка очистки процедурного КЭШа
Поскольку процедурный КЭШ необходимо очищать при каждом обновлении статистики, данную операцию рекомендуется добавить в уже созданный субплан «Обновление статистик». Для этого следует открыть субплан и добавить в его схему задачу Execute T-SQL Statement Task. Затем следует соединить задачу Update Statistics Task стрелочкой с новой задачей.
В тексте созданной задачи Execute T-SQL Statement Task следует указать запрос «DBCC FREEPROCCACHE»:
Дефрагментация индексов
При интенсивной работе с таблицами базы данных возникает эффект фрагментации индексов, который может привести к снижению эффективности работы запросов.
Рекомендуется регулярное выполнение дефрагментации индексов. Для дефрагментации всех индексов всех таблиц базы данных необходимо использовать следующий SQL запрос (предварительно подставив имя базы):
Дефрагментация индексов не блокирует таблицы, и не будет мешать работе других пользователей, однако создает дополнительную нагрузку на SQL Server. Оптимальная частота выполнения данной регламентной процедуры должна подбираться в соответствии с нагрузкой на систему и эффектом, получаемым от дефрагментации. Рекомендуется выполнять дефрагментацию индексов не реже одного раза в день.
Возможно выполнение дефрагментации для одной или нескольких таблиц, а не для всех таблиц базы данных.
Настройка дефрагментации индексов (MS SQL 2005)
В ранее созданном плане обслуживания создайте новый субплан с именем «Дефрагментация индексов». Добавьте в него задачу Reorganize Index Task:
Задайте расписание выполнения для задачи дефрагментации индексов. Рекомендуется выполнять задачу не реже одного раза в неделю, а при высокой изменчивости данных в базе еще чаще – до одного раза в день.
Настройте задачу, указав базу данных (или несколько баз данных) и выбрав необходимые таблицы. Если точно неизвестно, какие таблицы следует указать, то устанавливайте значение All.
Реиндексация таблиц включает полное перестроение индексов таблиц базы данных, что приводит к существенной оптимизации их работы. Рекомендуется выполнять регулярную переиндексацию таблиц базы данных. Для реиндексации всех таблиц базы данных необходимо выполнить следующий SQL запрос:
Реиндексация таблиц блокирует их на все время своей работы, что может существенно сказаться на работе пользователей. В связи с этим реиндексацию рекомендуется выполнять во время минимальной загрузки системы.
После выполнения реиндексации нет необходимости делать дефрагментацию индексов.
В ранее созданном плане обслуживания создайте новый субплан с именем «Дефрагментация индексов». Добавьте в него задачу Rebuild Index Task:
Задайте расписание выполнения для задачи реиндексирования таблиц. Рекомендуется выполнять задачу во время минимальной нагрузки на систему, не реже одного раза в неделю.
Настройте задачу, указав базу данных (или несколько баз данных) и выбрав необходимые таблицы. Если точно неизвестно, какие таблицы следует указать, то устанавливайте значение All.
Реиндексация таблиц базы данных
Необходимо осуществлять регулярный контроль выполнения регламентных процедур на уровне СУБД. Ниже приведен пример контроля выполнения плана обслуживания для MS SQL Server 2005.
Откройте созданный вами план обслуживания и выберите из контекстного меню пункт «View History»:
Откроется окно с протоколом выполнения всех заданных регламентных процедур.
Успешно выполненные задачи и задачи, выполненные с ошибками, будут помечены соответствующими иконками. Для задач, выполненных с ошибками, доступна подробная информация об ошибке.
