Панель мониторинга производительности

Старайтесь не использовать запросы без параметров

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

Кроме того, хранилище запросов может быстро превысить квоту на размер из-за потенциально большого количества разных текстов запросов и, следовательно, большого количества разных планов выполнения с аналогичной формой. В результате производительность рабочей нагрузки может стать неудовлетворительной и хранилище запросов может перейти в режим «только чтение» или постоянно удалять данные в попытке справиться с входящими запросами.

Следуйте приведенным ниже рекомендациям.

• Параметризуйте запросы везде, где это возможно. Например, заключайте запросы в хранимую процедуру или . См. дополнительные сведения о .
• Используйте параметр Оптимизировать для нерегламентированной рабочей нагрузки

  Сравните число уникальных значений query_hash с общим числом записей в sys.query_store_query. Если их соотношение близко к 1, ваша нерегламентированная рабочая нагрузка создает разные запросы.

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

• Применяйте для базы данных или подмножества запросов, если количество разных планов запроса невелико.
  • Используйте структуру плана, чтобы применить принудительную параметризацию только в выбранном запросе.
  • Настройте принудительную параметризацию с помощью параметра базы данных , если в рабочей нагрузке существует небольшое число разных планов запроса. Примером является ситуация, когда отношение числа разных query_hash к общему числу записей в намного меньше 1.
• Установите параметр QUERY_CAPTURE_MODE в значение AUTO, чтобы динамические запросы с небольшим потреблением ресурсов отфильтровывались автоматически.

Совет

При использовании решения объектно-реляционного сопоставления (ORM), такого как Entity Framework (EF), запросы приложений, такие как деревья запросов LINQ, отправляемых вручную, или определенные необработанные запросы SQL, могут не параметризоваться. Это влияет на повторное использование плана и возможность отслеживать запросы в хранилище запросов. Дополнительные сведения см. в статьях Кэширование и параметризация запросов EF и Необработанные запросы SQL EF.

Поиск непараметровизованных запросов в хранилище запросов

Чтобы узнать количество планов, сохраненных в хранилище запросов с помощью приведенного ниже запроса, можно использовать динамические административные представления хранилища запросов, SQL Server, управляемый экземпляр SQL Azure или базу данных SQL Azure:

В следующем примере создается сеанс расширенных событий для записи события , что может быть пригодиться при диагностике потребления ресурсов запросов. В SQL Server такой сеанс расширенных событий по умолчанию создает файл событий в папке журналов SQL Server. Например, при установке SQL Server 2019 (15.x) в Windows по умолчанию файл событий (XEL-файл) создается в папке . Для управляемого экземпляра SQL Azure укажите вместо этого расположение хранилища BLOB-объектов Azure. Дополнительные сведения см. в статье . Событие qds.query_store_db_diagnostics для базы данных SQL Azure недоступно.

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

Для параметризованных запросов:

Для непараметризованных запросов:

Обработки для проведения сценарного нагрузочного тестирования на примере конфигурации ЗУП версии 3.1.1.91

Обработки предназначены  для проведения сценарного нагрузочного тестирования, включая  пример описанного  сценария  с обработками (epf) —  ГлавныйРасчетчик, Кадровик, Расчетчик, Табельщик.
Обработка будет полезна прежде всего тому, кто внедряет решение на базе конфигурации 1С «Зарплата и Управления персоналом» с необходимостью воспроизвести определенный сценарий с заданным количеством пользователей для расчета, а также возможность посмотреть, какая будет при этом нагрузка на ваше оборудование и скорость выполнения операций с учетом блокировок СУБД.
Также это будет интересно тем, кто хочет прощупать, как на практике пользоваться конфигурацией «Тест Центр», входящий в состав пакета 1С:КИП.

2 стартмани

Причина

Эта ошибка говорит о следующем: Windows сообщает об успешном считывании страницы с диска, но SQL Server обнаружил некоторые повреждения страницы. Эта ошибка похожа на ошибку 823, за исключением того, что Windows не обнаружила ошибку. Ошибка 824 обычно указывает на проблемы в подсистеме ввода-вывода, такие как отказ дисков, проблемы встроенного ПО, неисправные драйверы устройств и т. д. Дополнительные сведения об ошибках ввода-вывода см. в главе 2 документации Майкрософт об основных операциях ввода-вывода в SQL Server.

SQL Server использует следующие API-интерфейсы Windows для выполнения операций ввода-вывода: , , и . После завершения этих операций ввода-вывода SQL Server проверяет наличие ошибок, связанных с этими вызовами API. Если эти вызовы API завершаются ошибкой операционной системы, SQL Server сообщает об ошибке 823. Бывают ситуации, когда вызов API Windows фактически выполняется успешно, но данные, передаваемые операцией ввода-вывода, могли столкнуться с проблемой логической согласованности. Эти проблемы с логической согласованностью выводятся через ошибку 824.

В сообщении об ошибке 824 содержатся следующие сведения:

• Файл базы данных, для которого была выполнена операция ввода-вывода.
• Смещение в файле, где была предпринята попытка выполнить операцию ввода-вывода.
• База данных, которой принадлежит этот файл.
• Номер страницы, включенной в операцию ввода-вывода.
• Была ли это операция чтения или записи.
• Сведения о проверке логической согласованности, которая завершилась сбоем (тип проверки, фактическое значение и ожидаемое значение, используемое для этой проверки).

Эти проверки логической согласованности представляют собой проверки целостности, выполняемые SQL Server, чтобы гарантировать, что ключевые элементы данных, участвующих в передаче ввода-вывода, оставались неизменными на протяжении всей операции ввода-вывода. К таким проверкам относятся контрольная сумма, разорванная страница, короткая передача, идентификатор неправильной страницы, устаревшее чтение и сбой аудита страницы. Характер выполненных проверок зависит от различных параметров конфигурации на уровне базы данных и сервера.

Сообщение об ошибке 824 обычно указывает на то, что возникла проблема с базовой системой хранения, оборудованием или драйвером, который находится в пути запроса ввода-вывода. Эта ошибка может возникать, если в файловой системе возникли несоответствия или если файл базы данных поврежден.

Логическая архитектура журнала транзакций

Логически журнал транзакций SQL Server работает так, как если бы он являлся последовательностью записей в журнале. Каждая запись журнала определяется порядковый номер журнала (LSN). Каждая новая запись добавляется в логический конец журнала с номером LSN, который больше номера LSN предыдущей записи. Записи журнала сохраняются в серийной последовательности по мере их создания, таким образом если LSN2 больше, чем LSN1, то изменение, описанное записью журнала, на которую ссылается LSN2, произошло после изменения, описанного записью журнала LSN1. Каждая запись журнала содержит идентификатор транзакции, к которой она относится. Все записи журнала, связанные с определенной транзакцией, с помощью обратных указателей связаны в цепочку, которая предназначена для ускорения отката транзакции.

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

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

• Зарегистрирована логическая операция.

  • Для наката логической операции выполняется снова.
  • Для отката логической операции выполняется обратная логическая операция.

• Зарегистрированы исходный и результирующий образы записи.

  • Для наката операции применяется изображение после.
  • Для отката операции применяется образ до.

В журнал транзакций записываются различные типы операций, например:

• начало и конец каждой транзакции;

• любые изменения данных (вставка, обновление или удаление), включая изменения в любой таблице (в том числе и в системных таблицах), производимые системными хранимыми процедурами или инструкциями языка DDL;

• любое выделение и освобождение страниц и экстентов;

• создание и удаление таблиц и индексов.

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

Раздел файла журнала из первой записи, который должен присутствовать для успешного отката всей базы данных к последней зарегистрированной записи называется активной частью журнала, активным журналом или заключительным фрагментом журнала. Этот раздел журнала необходим для полного базы данных. Ни одна часть активного журнала не может быть усечена. Порядковый номер журнала (LSN) этой первой записи журнала называется минимальным номером LSN восстановления (MinLSN). Дополнительные сведения об операциях, поддерживаемых журналом транзакций, см. в разделе Журнал транзакций (SQL Server).

Разностные резервные копии и резервные копии журналов продвигают восстанавливаемую базу данных к более позднему моменту, которому соответствует больший регистрационный номер транзакции в журнале.

Общие сведения об экземпляре отказоустойчивого кластера

FCI работает в группе ресурсов WSFC вместе с одним или несколькими узлами WSFC. Когда экземпляр FCI запускается, один из узлов принимает владение группой ресурсов и выводит свой экземпляр SQL Server в сеть. Ресурсы, принадлежащие данному узлу включают в себя:

• Сетевое имя

• IP-адрес

• Общие диски

• SQL Server Служба компонента Database Engine

• SQL Server Служба агента

• SQL Server Analysis Services, если эти служб установлены

• Один файловый ресурс общего доступа, если установлен компонент FILESTREAM

В любое время только владелец группы ресурсов (и никакой другой узел в FCI) запускает соответствующие службы SQL Server в группе ресурсов. При автоматическом или запланированном переходе на другой ресурс происходит следующая последовательность событий.

1. Если событие не является сбоем оборудования или системы, все «грязные» страницы в буферном кэше записываются на диск.

2. Все соответствующие службы SQL Server в группе ресурсов останавливаются на активном узле.

3. Права владения группой ресурсов переносятся на другой узел в FCI.

4. Новый владелец группы ресурсов запускает свои службы SQL Server .

5. Клиентские запросы на подключение приложения автоматически перенаправляются на новый активный узел, используя то же имя виртуальной сети (VNN).

FCI находится в сети, если его базовый кластер WSFC находится в исправном состоянии (большая часть узлов кворума WSFC доступны в качестве целей автоматического перехода на другой ресурс). Когда кластер WSFC теряет кворум из-за сбоя аппаратной части, программного обеспечения, сети или неверной настройки кворума, весь кластер WSFC вместе с FCI выводится из сети. Для этого незапланированного сценария отработки отказа требуется вмешательство пользователя, который должен переустановить кворум для оставшихся доступных узлов, чтобы вернуть кластер WSFC и FCI обратно в сеть. Дополнительные сведения см. в разделе Режим кворума и участвующая в голосовании конфигурация WSFC (SQL Server).

Прогнозируемое время отработки отказа

В зависимости от того, когда экземпляр SQL Server в последний раз выполнял операцию контрольной точки, в буферном кэше может быть достаточное количество «грязных» страниц. Последующая отработка отказа длится столько, сколько потребуется времени для записи оставшихся «грязных» страниц на диск, что может увеличить время отработки отказа непредсказуемым образом. Начиная с Майкрософт SQL Server 2012 (11.x), FCI может использовать косвенные контрольные точки для регулирования количества грязных страниц, хранящихся в буферном кэше. Несмотря на то что это занимает дополнительные ресурсы при нормальной рабочей нагрузке, время отработки отказа становится более прогнозируемым и регулируемым. Это очень полезно, когда в соглашении об уровне обслуживания в организации указана цель времени восстановления (RTO) для решения высокого уровня доступности. Дополнительные сведения о косвенных контрольных точках см. в разделе .

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

После успешного запуска FCI служба WSFC мониторит состояние базового кластера WSFC, а также состояние экземпляра SQL Server . Начиная с Майкрософт SQL Server 2012 (11.x), служба WSFC использует выделенное подключение для опроса активного экземпляра SQL Server для получения подробных сведений о диагностике компонентов с помощью системной хранимой процедуры. Это имеет три последствия.

• Выделенное соединение с экземпляром SQL Server позволяет получать надежную диагностику компонентов в любое время, даже когда FCI испытывает перегрузку. В результате можно выделить систему, испытывающую большую нагрузку, и систему, которая находится на грани отработки отказа, позволяя избежать проблем, связанных с ложной отработкой отказа.

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

• Подробная диагностика компонентов также позволяет лучше устранять ошибки автоматической отработки отказа. Сведения диагностики сохраняются в файлы журналов, связанные с журналами ошибок SQL Server . Файлы можно загрузить в средство просмотра файлов журнала для изучения состояний компонентов, которые привели к отработке отказа, чтобы определить причину сбоя.

Дополнительные сведения см. в разделе Failover Policy for Failover Cluster Instances.

Как работать с Activity Monitor

Activity Monitor можно открыть в SQL Server Management Studio toolbar используя иконку Activity Monitor на панели, сочетанием клавиш Ctrl+Alt+A или через контекстное меню в Object Explorer.

Как уже было сказано выше, Activity Monitor отслеживает только заранее определенный набор наиболее важных показателей производительности SQL Server. Дополнительных параметров указать нельзя, нельзя и удалить что-то из показателей. Мониторинг возможен только в режиме реального времени. Нет возможности сохранить результаты мониторинга для последующего анализа. Таким образом Activity Monitor – это полезный инструмент для беглого анализа и поиска неисправностей, но он не подходит для детального сбора информации, т.к. в нём отсутствует возможность гибкой настройки счётчиков производительности, указания пороговых значений и нет возможности сбора исторических данных.

Аргументы

| database_id NULL

Область применения: SQL Server 2008 (10.0.x) и более поздние версии, База данных SQL Azure

Идентификатор базы данных. database_id имеет значение int без значения по умолчанию. Допустимыми входными значениями являются идентификационный номер базы данных или NULL. Если задано значение NULL, возвращаются все базы данных в экземпляре SQL Server.

Может быть указана встроенная функция DB_ID.

| file_id NULL

Область применения: SQL Server 2008 (10.0.x) и более поздние версии, База данных SQL Azure

Идентификатор файла. file_id имеет значение int без значения по умолчанию. Правильные значения — идентификационный номер файла или значение NULL. Когда указывается значение NULL, возвращаются все файлы базы данных.

Можно указать встроенную функцию FILE_IDEX и ссылаться на файл в текущей базе данных.

Журнал транзакций с упреждающей записью

В этом разделе описана роль, которую журнал транзакций с упреждающей записью играет в записи изменений данных на диск. SQL Server использует алгоритм с упреждающей записью журнала (WAL), который гарантирует, что сначала на диск будет записана соответствующая запись журнала, и только после этого изменения данных будут записаны на диск. Таким образом обеспечиваются свойства ACID для транзакции.

Чтобы понять, как работает упреждающее ведение журнала, важно знать, как измененные данные записываются на диск. SQL Server поддерживает буферный кэш (также называемый буферным пулом), в который он считывает страницы данных при извлечении данных

При изменении страницы в буферном кэше она не сразу записывается обратно на диск; Вместо этого страница помечается как «грязная». Перед физической записью на диск на странице данных может быть выполнено несколько логических операций записи. При каждой логической операции записи в кэш журнала, который записывает изменения, добавляется запись журнала транзакций. Записи журнала должны быть перенесены на диск до того, как соответствующая «грязная» страница будет удалена из буферного кэша и записана на диск. Заключается в том, что процесс контрольных точек производит периодический просмотр буферного кэша на наличие буферов со страницами определенной базы данных и запись всех «грязных» страниц на диск. Контрольные точки экономят время во время последующего восстановления при помощи создания точки, в которой все «грязные» страницы гарантированно записываются на диск.

Запись измененной страницы данных из буферного кэша на диск называется сбросом страницы на диск. SQL Server Имеет логику, которая предотвращает запись на диск «грязной» страницы до записи на него связанной записи журнала. Содержимое журнала записывается на диск при сбросе буферов журнала. Это происходит при фиксации транзакции или заполнении буферов журнала.

Оптимизация

Продолжим разговор о выборе оптимального способа получения данных. Следующая стадия жизни запроса: оптимизация.

В T-SQL, так же, как и в SQL, оптимизация означает выбор лучшего способа получения данных среди всех возможных альтернатив. Предположим, у нас есть простой запрос с объединением 2х таблиц, у каждой таблицы есть по одному некластерному индексу — и вот у нас уже 4 способа выполнить запрос! Количество возможных вариантов растёт экспоненциально вместе с ростом сложности запросов и количеством способов обращения к данным (т.е., с числом индексов в нужных таблицах). Добавьте к этому, что соединение (JOIN) может быть выполнено при помощи разных стратегий (в общем случае: вложенные циклы, соединение хэшированием или объединением). Становится понятно, почему оптимизация настолько важна в работе SQL Server.

SQL Server использует оптимизатор, основанный на стоимостной оценке. Это значит, что он перебирает все (ну или большинство) возможных вариантов выполнения и пытается оценить возможную стоимость каждой альтернативы. Выбран будет вариант с наименьшей стоимостью.

Стоимость, по большей части, зависит от объёма данных, которые необходимо будет прочитать в рамках выбранного способа выполнения. Для того, чтобы получить эту «стоимость», SQL Server должен знать размер каждой таблицы и распределение значений в колонках этой таблицы. Информация об этом распределении хранится в статистиках.

Кроме объёма читаемых данных, стоимость также зависит от предполагаемой нагрузки на CPU и объёма памяти, необходимого для выполнения запроса.

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

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

Решение

При возникновении ошибки 824 можно попробовать следующие решения.

• Просмотрите таблицу suspect_pages в , чтобы проверить, возникает ли эта проблема на других страницах (в той же или в разных базах данных).

• Проверьте согласованность баз данных, расположенных в том же томе (как в сообщении 824), с помощью команды DBCC CHECKDB. При обнаружении несоответствий в команде воспользуйтесь рекомендациями из статьи базы знаний Устранение ошибок согласованности баз данных, о чем сообщает DBCC CHECKDB.

• Если в базе данных, в которой возникают эти ошибки 824, параметр базы данных не включен, включите параметр немедленно. Ошибки 824 могут возникать по другим причинам, кроме ошибки контрольной суммы, но CHECKSUM обеспечивает наилучший вариант проверки согласованности страницы после ее записи на диск. Используйте этот скрипт для определения баз данных, в которых параметр CHECKSUM не включен:

• Проверьте журналы событий Windows на наличие ошибок или сообщений от операционной системы, устройства хранения или драйвера устройства. Если они каким-то образом связаны с этой ошибкой, сначала следует устранить эти ошибки. Например, помимо сообщения 824, вы также можете заметить такое событие, как «Драйвер обнаружил ошибку контроллера в \Device\Harddisk4\DR4», сообщаемое источником диска в журнале событий. В этом случае необходимо проверить наличие этого файла на устройстве, а затем сначала исправить ошибки диска.

• Используйте служебную программу SQLIOSim, чтобы узнать, можно ли воспроизвести ошибки 824 за пределами обычных запросов ввода-вывода SQL Server. SQLIOSim поставляется с SQL Server 2008 (10.0.x) и более поздних версий, поэтому отдельная загрузка не требуется.

• Обратитесь к поставщику оборудования или изготовителю устройства, чтобы убедиться, что:

  • Аппаратные устройства и конфигурация соответствуют требованиям ввода-вывода SQL Server.
  • драйверы устройств и другие программные компоненты, поддерживающие все устройства в пути ввода-вывода, обновлены.

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

• Оцените наличие драйверов фильтров в пути ввода-вывода этих запросов. Чтобы получить список всех драйверов фильтров в системе, можно выполнить следующие команды:

  • Исключить файлы базы данных и журналов из проверки с помощью таких драйверов фильтров. Дополнительные сведения см. в разделе .
  • Проверьте наличие обновлений для этих драйверов фильтра.
  • Можно ли удалить или отключить эти драйверы фильтров, чтобы увидеть, исчезнет ли проблема, которая приводит к ошибке 824?

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

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

Администрирование

• Data Scripter Add-in for Management Studio. С помощью этой надстройки можно составить сценарии обработки данных для таблиц SQL Server из среды SQL Server Management Studio (SSMS) (sqlblogcasts.com/blogs/seanprice/archive/2007/08/28/data-scripter-add-in-for-management-studio.aspx).
• Iometer. Данный инструмент измеряет и определяет характеристики подсистем ввода/вывода в одиночных и кластерных серверах iometer.org.
• Microsoft SQL Server 2005 Upgrade Advisor. Анализирует экземпляры SQL Server 2000 и SQL Server 7.0, обнаруживая особенности и изменения настроек, которые могут повлиять на переход к SQL Server 2005 (www.microsoft.com/downloads/details.aspx?FamilyID=1470e86b-7e05-4322-a677-95ab44f12d75).
• mRemote. С помощью этого инструмента (совместимого с RDP и VNC) можно управлять подключениями к удаленным серверам из интерфейса пользователя (www.mremote.org/wiki).
• MSSQL Blocks. Инструмент для сбора информации из многочисленных экземпляров SQL Server 2005 и 2000 о блокированных процессах. Сведения сохраняются в XML-файлах для последующего анализа (www.sqlblocks.narod.ru).
• Orphan Finder. С помощью Orphan Finder можно искать записи в базах данных SQL Server 2005 со значениями в столбцах внешнего ключа, отсутствующими в родительской таблице (http://www.spi.hr/EnglishaboutLC/tabid/470/Default.aspx).
• Partition Management. Метод скользящего окна используется для удаления данных из одного раздела и переноса его в промежуточную таблицу. Строится промежуточная таблица, используемая для загрузки данных в разделы (www.codeplex.com/SQLPartitionMgmt).
• Toad for SQL Server Freeware. Инструмент компании Quest Software располагает редактором исходного текста и данных, наборами данных для изменения таблиц и обозревателем для просмотра и управления объектами базы данных в графическом режиме (www.toadsoft.com/toadsqlserver/toad_sqlserver.htm).
• SortSQLFilesInProject. Это инструмент для сортировки SQL-файлов в проекте SSMS или пакетов в проекте SQL Server Integration Services (SSIS) (www.sqldbatips.com/showarticle.asp?ID=78).
• SQLDBSize 1.0. Инструмент графически отображает сведения о размерах баз данных, таблиц, индексов и других объектов (www.sqldbtools.com/Tools.aspx?ProductId=3).
• SQL Management Studio Snapshot Add-In. Данная утилита добавляет пункт Create Snapshot в контекстные меню в SSMS, позволяющий получить моментальный снимок базы данных (sqlblogcasts.com/blogs/seanprice/archive/2007/07/15/sql-management-studio-snapshot-add-in.aspx.
• SQLRecon. Обнаруживает все компьютеры SQL Server и Microsoft SQL Server Desktop Engine (MSDE), выполняя активное и пассивное сканирование сети (www.specialopssecurity.com/labs/sqlrecon).
• SQL Server 2008 Extended Events Manager. С помощью данной программы можно создавать, удалять, изменять, запускать и останавливать сеансы Extended Events и файлы метаданных в SQL Server 2008 (www.codeplex.com/ExtendedEventManager).
• SQL Server Automation Scripts. С помощью заданий SQL Server Agent можно автоматизировать задачи обслуживания (download.microsoft.com/download/4/0/C/40CBAD9A-D990-450B-8785-F288CEBFB448/AITScripts.zip).
• SQL Server Database Copy Tool. Копирование баз данных с одного компьютера SQL Server на другой (dbcopytool.codeplex.com).
• SQL Server Express Utility. С помощью утилиты командной строки можно выполнять различные задачи обслуживания SQL Server (www.microsoft.com/downloads/details.aspx?FamilyID=fa87e828-173f-472e-a85c-27ed01cf6b02).
• SQL Server Health and History Tool (SQLH2) Performance Collector. Инструмент используется для сбора данных счетчиков производительности из компьютеров SQL Server и сохранения их в репозитарии (www.microsoft.com/downloads/details.aspx?familyid=64983AF0-7902-427E-9B41-7C2E8FDCC140).
• SQL Server Health and History Tool (SQLH2) Reports. С помощью инструмента можно предотвратить неполадки, собирая и сохраняя информацию об изменениях и тенденциях (www.microsoft.com/downloads/details.aspx?familyid=A4B837C7-A1FA-4F25-840B-FEF15E917F18).
• YourSQLDba. Хранимая процедура T-SQL автоматизирует типовые задачи обслуживания базы данных, в том числе резервное копирование базы данных и журналов, и проверки целостности (www.grics.qc.ca/YourSqlDba).

Activity Monitor

Activity Monitor отслеживает наиболее важные показатели эффективности SQL Server. Чтобы получить их, он выполняет запросы к экземпляру SQL Server каждые 10 секунд. Мониторинг осуществляется только когда инструмент открыт, поэтому побочный эффект от его использования минимальный.

Все метрики показаны на 5 разных панелях: Overview (Обзор), Processes (Процессы), Resource Waits (Ожидания ресурсов), Data File I/O (Ввод/вывод файлов данных), и Recent Expensive Queries (последние затратные запросы).

Overview (Общие сведения). Содержит графики Processor Time (Процессорное время), Number of Waiting Tasks (Количество ожидающих задач), Database I/O (Ввод-вывод в базе данных) и Number of Batch Requests/second (Количество пакетных запросов в секунду).

Processes (Процессы). Показывает все работающие в данный момент процессы во всех БД экземпляра SQL Server. Выводится информация по следующим полям: Логин, название приложения и хост, состояние задач и команд, время ожидания и т.д. Информацию можно отфильтровать по любому из полей таблицы.

Кроме того, есть удобная возможность для администраторов в контекстном меню вызвать SQL Server Profiler и выполнить более детальный анализ любого процесса.

Resource Waits (Ожидание ресурсов). Показывает ожидания для различных ресурсов: памяти, компиляции, сети и т.д.

Можно увидеть время ожидания (сколько времени задача SQL Server ожидала ресурсов от системы), последнее, общее и среднее.

Data File I/O (Ввод-вывод в файлах данных). Выводит информацию о всех файлах БД: MDF, NDF и LDF. Их название, полный путь, активность по чтению и записи и время отклика.

Recent Expensive Queries (Последние ресурсоемкие запросы). Показывает запросы, выполненные в последние 30 секунд, которые используются наибольшее количество аппаратных ресурсов: процессора, памяти, диска и сети. Контекстное меню позволяет открыть запрос в закладке запроса SQL Server Management Studio и просомтреть его план выполнения.

Рабочие потоки (Workers)

Рабочие потоки – это потоки (нити, threads), относящиеся к SQL Server. Несколько таких потоков создаётся сразу при старте службы сервера, дальше, по необходимости, могут быть созданы дополнительные — пока общее число рабочих потоков не достигнет значения параметра max worker threads.

Только рабочие потоки могут выполнять код. Рабочие потоки ждут, пока «ожидающая» (PENDING) задача не станет доступна, затем каждый рабочий поток забирает ровно одну задачу и выполняет её. Рабочий поток занят (работает) пока задача не будет выполнена полностью. Ожидающие (pending) задачи, для которых не нашлось свободного рабочего потока вынуждены висеть, пока какая-нибудь из выполняемых задач не завершится – и не освободится рабочий поток, который сможет взять новую задачу.

Если рабочий поток выбирает пакетный запрос (batch), он один будет выполнять весь пакет, т.е. каждую его команду. Тут должно стать понятно, могут ли разные команды в одном скрипте (=> задаче => рабочем процессе) выполняться параллельно: нет, не могут. Поскольку они все выполняются в одном потоке, каждый запрос из пакета не начнёт выполняться, пока не закончится предыдущий.

Для команд, которые используют внутренний параллелизм (помним про DOP, здесь он будет >1) и создают подзадачи, каждая такая подзадача пройдёт ровно такой же цикл: будет создана со статусом «ожидание» (PENDING), её должен будет подхватить и выполнить рабочий поток (не тот поток, который выполняет «родительский» пакетный запрос! – тот поток по определению уже занят).

Список рабочих потоков можно увидеть в представлении sys.dm_os_workers.

Наблюдение за памятью операционной системы

Для отслеживания нехватки памяти используйте приведенные ниже счетчики Windows. Значения многих счетчиков памяти операционной системы можно запрашивать с помощью динамических административных представлений sys.dm_os_process_memory и sys.dm_os_sys_memory.

• Память: доступно байтов
  Этот счетчик указывает на то, сколько байт памяти доступно на данный момент для использования процессами. Низкие значения счетчика Доступно байтов могут указывать на общую нехватку памяти операционной системы. Это значение можно запросить с помощью T-SQL из sys.dm_os_sys_memory.available_physical_memory_kb.

  

  

  

  

  

  

  

  

  

• Память: страниц/с
  Этот счетчик показывает число страниц, которые были или получены с диска из-за ошибок страниц физической памяти, или записаны на диск для освобождения пространства в рабочем множестве из-за ошибок страниц. Большое значение счетчика Страниц/с может означать излишнюю подкачку.

• Память: ошибок страницы/с Этот счетчик показывает частоту ошибок страниц для всех процессов, включая системные. Низкий, но не нулевой уровень выгрузки на диск (и вызванные ею ошибки страниц) является нормальным, даже если у компьютера достаточно большое количество доступной памяти. Диспетчер виртуальной памяти (VMM) Microsoft Windows берет страницы из SQL Server и других процессов по мере того, как он урезает размеры рабочих множеств этих процессов. Деятельность VMM может привести к ошибкам страниц.

• Процесс: ошибок страницы/с Этот счетчик показывает частоту ошибок страниц для определенного пользовательского процесса. С помощью счетчика Процесс: ошибок страниц/с можно определить, вызвана ли повышенная активность диска подкачкой, выполняемой сервером SQL Server. Чтобы определить, является ли SQL Server или другой процесс причиной излишней подкачки, наблюдайте за счетчиком Процесс: ошибок страниц/с для экземпляра процесса SQL Server.

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

Мониторинг в динамической среде

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

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

• Отслеживая выполняемые запросы Transact-SQL, вы можете определить, правильно ли написаны запросы и получать ожидаемые результаты.

• Отслеживая пользователей, пытающихся подключиться к экземпляру SQL Server, можно проверить надежность защиты и протестировать приложения или системы разработки.

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

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