Основы мониторинга производительности и диагностики проблем в SQL Server

В этой статье мы рассмотрим популярные инструменты, T-SQL запросы и скрипты для обнаружения и решения различных возможных проблем с производительностью SQL Server. Эта статья поможет вам разобраться, когда вашему SQL Server недостаточно ресурсов (памяти, CPU, IOPs дисков), найти блокировки, выявить медленные запросы. Посмотрим какие есть встроенные инструменты и бесплатные сторонние скрипты и утилиты для анализа состояния Microsoft SQL Server.

Инструменты для диагностики SQL Server

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

• T-SQL — самый мощный, простой и незаменимый инструмент для поиска проблем и анализом производительности SQL Server. Практически все другие инструменты для работы с SQL Server используют T-SQL. Нет ничего такого, чтобы вы не смогли сделать с помощью T-SQL.
• SQL Server Management Studio — без SSMS практически невозможно работать с SQL Server. С помощью SSMS вы можете посмотреть Activity monitor, проанализировать план запроса, посмотреть параметры сервера или базы данных и многие другие вещи.
• Журналы ошибок SQL Server и Windows – если что-то идёт не так, журнал ошибок — это первое место, куда смотрит системный администратор. Журнал ошибок SQL Server можно посмотреть через SSMS. Журналы Windows можно посмотреть через оснастку eventvwr.msc.
• Монитор ресурсов Windows — resmon.exe незаменимый инструмент Windows для быстрой оценки состояния ресурсов сервера. Использование оперативной памяти и процессора можно посмотреть и через Диспетчер задач, но детальное использование сети и жесткого диска можно посмотреть только через resmon и perfmon.
• Системный монитор Windows (Performance Monitor) — Perfmon.exe это основное средство мониторинга Windows, он содержит в себе разнообразные “счетчики”, как системных метрик, так и приложений, включая SQL Server. Обычно счетчики perfmon обрабатывают с помощью других систем мониторинга, например, Zabbix, так как в perfmon неудобно хранить и смотреть данные за прошедшее время.
• Сторонние приложения — существует много платных и бесплатных приложений для мониторинга SQL Server. Например, одним из бесплатных приложений является dbForge Monitor от компании Devart. Приложение устанавливается как дополнение к SSMS и позволяет выводить очень удобный дашборд для отображения текущего состояния вашего SQL Server (информация об использовании памяти, CPU, нагрузках, блокировках, процессах, информацию о бэкапах, “тяжелых SQL запросах”, производительности дисковой подсистемы и т.д.).
• Скрипты Brentozar – это популярное решение для диагностики настроек и работоспособности SQL Server. У brentozar есть много скриптов для различных задач, но для диагностики нас интересует “sp_blitz”. Скачать можно бесплатно с официального сайта https://www.brentozar.com/blitz/. Запустите sp_Blitz.sql чтобы установить необходимые процедуры и выполните их exec sp_blitz для диагностики. Этот инструмент бесплатный и поддерживается сообществом SQL Server. Sp_blitz определит все популярные проблемы с вашим сервером и посоветует как их решить.
• Наборы T-SQL скриптов — удобно иметь под рукой коллекции разнообразных T-SQL запросов для диагностики SQL Server, так как не всегда есть время писать собственные запросы, лучше вооружиться заранее. Ниже перечислены ссылки на полезные T-SQL/PowerShell запросы, которые я часто использую при диагностике и тюнинге MS SQL:
  • https://github.com/SQLadmin/AwesomeSQLServer — набор запросов для мониторинга CPU/RAM/Disk IO и прочих параметров.
  • https://github.com/dgavrikov/databases_scripts/tree/master/SQL%20Server – много полезных скриптов и лайфхаков
  • https://github.com/ktaranov/sqlserver-kit — Скрипты и полезная информация. Много примеров работы с SQL Server через PowerShell

Обнаружение и решение проблем с производительностью SQL Server

Самой распространенной проблемой с которой сталкивается системный администратор, работающий с SQL Server, это жалобы пользователей на производительность запросов и самого сервера: “тормозит”, “долго выполняется запрос“, и так далее.

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

Анализ использования оперативной памяти SQL Server

Для начала нужно определить сколько памяти доступно SQL Server. Для этого запустите SSMS (SQL Server Management Studio), зайдите на сервер и зайдите в свойства сервера (ПКМ по названию сервера в Обозревателе объектов).

Сам по себе доступный объём RAM вам ничего не скажет. Нужно сравнить это число с используемой памятью в Диспетчере Задач и самим движком SQL Server с помощью DMV.

В Диспетчере задач, во вкладке Подробности, найдите sqlservr.exe и посмотрите сколько оперативной памяти использует этот процесс.

• Если на сервере, например, 128 GB оперативной памяти, а процесс sqlservr.exe использует 60 GB и ограничений по RAM у SQL Server нет, то оперативной памяти вам хватает.
• Если SQL Server использует 80-90% RAM от заданной или максимальной, то в таком случае нужно смотреть DMV. Имейте в виду, что sqlservr.exe не сможет использовать всю оперативную память. Если на сервере 128 GB, то sqlservr.exe может использовать только 80-90% (100-110 GB), так как остальная память резервируется для операционной системы.

Имейте в виду, что процесс SQL Server’a не отдаёт оперативную память обратно в систему. Например, ваш SQL Server обычно использует 20 GB памяти, но при месячном отчете он увеличивает потребление до 100 GB, и даже когда вычисление отчета закончится и сервер будет работать в прежнем режиме, процесс SQL Server’a всё равно будет использовать 100 GB до перезагрузки службы.

Даже если вы уверены, что оперативной памяти серверу хватает, не будет лишним точно знать объём потребляемой RAM.

Узнать реальное использование RAM можно с помощью Dynamic Management Views. DMV это административные вьюверы (представления). С помощью DMV можно диагностировать практически любую проблему в SQL Server.

Посмотрим sys.dm_os_sys_memory, для удобства используем запрос:

Рассмотрим каждый выводимый параметр:

1. [Total Physical Memory] – объём оперативной памяти доступный в операционной системе. На некоторых серверах может показывать немного больше реально установленной.
2. [Available Physical Memory] – объём оперативной памяти доступный для SQL Server, без учета уже захваченной SQL Server.
3. [Total Page File (MB)] – Объём “Сommit limit”. Commit Limit = Оперативная память + все файлы подкачки. То есть, если у вас на сервере 32 GB оперативной памяти и 16 GB файл подкачки, commit limit будет 48 GB.
4. [Available Page File (MB)] – Объём файла подкачки.
5. Percentage Used – процент занятой оперативной памяти. Такого параметра нет в самом sys.dm_os_sys_memory, но он считается по формуле available_physical_memory_kb / total_physical_memory_kb
6. [Memory State] – Состояние RAM. Поле system_memory_state_desc содержит в себе состояние потребления оперативной памяти в виде текста. Значение этого поля считается исходя из других двух: system_low_memory_signal_state и system_high_memory_signal_state. Вы можете выбирать их напрямую, если вам нужен Boolean/bit формат данных. Для ознакомления со всеми полями sys.dm_os_sys_memory ознакомьтесь с документацией https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-sys-memory-transact-sql?view=sql-server-ver15

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

Если Вам нужно убедиться, что серверу хватает RAM, вы можете смотреть только на поля system_low_memory_signal_state, system_high_memory_signal_state и system_memory_state_desc. Если system_low_memory_signal_state = 1, то серверу явно не хватает оперативной памяти.

Загрузка процессора в SQL Server

Нагрузку на процессор определить проще, так как это можно сделать в Диспетчере задач. Чтобы узнать текущую нагрузку на процессор, найдите в Диспетчере задач процесс sqlservr.exe

Если вы хотите узнать нагрузку за прошедшее время, можно воспользоваться запросом:

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

Анализ нагрузки на диск SQL Server

Посмотрим на загрузку дисков в операционной системе. Для этого запустите resmon.exe.

Нам нужна вкладка Disk. В секции Disk Activity отображаются файлы, к которым идёт обращение, и их скорость read/write на текущий момент. Отфильтруйте эту секцию по Total (кликните на Total). На самом верху будут файлы, которые на данный момент максимально используют диск. В случае с SQL Server это может быть полезно чтобы определить какая база больше всего нагружает диск на текущий момент.

В секции Storage отображаются все диски в системе. В этой секции нам нужны 2 параметра – Active Time и Disk Queue. Active Time в процентах отображает нагрузку на диск, то есть если вы видите на диске C:\ Active Time равный 90, это значит что ресурс чтения/записи диска на текущий момент используется на 90%. Столбец Disk Queue отображает очередь обращений к диску, и если значение очереди не равно нулю, то диск загружен на 100% и не справляется с нагрузкой. Так же если Active Time близок к 100, то диск используется практически на пределе своих возможностей по скорости.

Просмотр блокировок в SQL Server

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

Блокировки можно посмотреть через Activity Monitor в SSMS, но мы воспользуемся T-SQL, так как этот вариант более удобен и нагляден. Выполняем запрос:

Этот запрос возвращает список блокировок в виде дерева. Это удобно в работе, так как обычно, если возникает одна блокировка, она провоцирует за собой другие. Аналогично в Activity Monitor или в выводе sp_who2 можно увидеть поле “Blocked By”.

Если запрос ничего не вернул, то блокировок нет.

Если запрос вернул какие-то данные, то нужно проанализировать цепочку.

HEAD значит что этот запрос является причиной всех остальных блокировок ниже по дереву. 64 – это идентификатор процесса (SPID). После этого пишется тело запроса, который вызвал блокировку. Если у вас хватает ресурсов сервера, то скорее всего дело в самом запросе и во взаимном обращении к каким-то объектам. Для того чтобы сказать точнее, нужно анализировать конкретный запрос, который вызвал блокировку.

Политики SQL Server

Даже когда у вас всё работает хорошо и жалоб нет, на самом деле может быть много проблем, которые всплывут позже. Для этого в SQL Server есть политики.

Политика в SQL Server это, грубо говоря, проверка правила на соответствие заданному значению. Например, с помощью политик вы можете убедиться, что на всех базах на сервере выключен Auto Shrink. Рассмотрим пример импорта и выполнения политики

В SSMS, подключитесь к серверу, на котором хотите выполнять политики (Management -> раздел Policy Management).

Импортируем файл Database Auto Shrink.xml. Жмём Evaluate

На экземпляре node1 две базы данных, test1 и test2. На test2 включен autoshrink. Посмотрим детали.

Политика определила включенный параметр AutoShrink, в описании обычно пишется объяснения к правилам. В данном случае дается объяснение почему auto shrink лучше отключать.

Политики могут выполняться либо по расписанию, либо по требованию (разово). Результаты выполнения политики можно посмотреть в журнале политик.

При установке SQL Server нужно выбирать только используемые компоненты СУБД, и указывать настройки в соответствии с конфигурацией “железа” вашего сервера. Всегда следите чтобы серверу хватало ресурсов, и чтобы на сервере не было блокировок

Самым мощным инструментом для диагностики SQL Server является T-SQL и DMV. Так же рекомендуется построить круглосуточный мониторинг над SQL Server и над обслуживающей его инфраструктурой для обнаружения всех возможных проблем.

Счетчики производительности в SqlClient Performance counters in SqlClient

ПРИМЕНИМО К: .NET Framework .NET Core .NET Standard

Скачать ADO.NET

Счетчики производительности Microsoft.Data.SqlClient можно использовать для мониторинга состояния приложения и используемых им ресурсов подключения. You can use Microsoft.Data.SqlClient performance counters to monitor the status of your application and the connection resources that it uses. Показания счетчиков производительности можно отслеживать с помощью системного монитора Windows или получить к ним доступ программным путем с помощью класса PerformanceCounter в пространстве имен System.Diagnostics. Performance counters can be monitored by using Windows Performance Monitor or can be accessed programmatically using the PerformanceCounter class in the System.Diagnostics namespace.

Доступные счетчики производительности Available performance counters

В настоящее время имеется 14 различных счетчиков производительности, доступных для Microsoft.Data.SqlClient, как показано в следующей таблице. Currently there are 14 different performance counters available for Microsoft.Data.SqlClient as described in the following table.

Счетчик производительности Performance counter	Описание Description
HardConnectsPerSecond	Количество соединений с сервером базы данных в секунду. The number of connections per second that are being made to a database server.
HardDisconnectsPerSecond	Количество разрывов соединений с сервером базы данных в секунду. The number of disconnects per second that are being made to a database server.
NumberOfActiveConnectionPoolGroups	Количество уникальных активных групп пулов соединений. The number of unique connection pool groups that are active. Этот счетчик управляется числом уникальных строк соединения, найденных в домене приложения. This counter is controlled by the number of unique connection strings that are found in the AppDomain.
NumberOfActiveConnectionPools	Общее число пулов соединений. The total number of connection pools.
NumberOfActiveConnections	Количество текущих активных соединений. The number of active connections that are currently in use. Примечание. Этот счетчик производительности по умолчанию не включен. Note: This performance counter is not enabled by default. Инструкции о том, как его включить, см. в разделе Активация счетчиков, отключенных по умолчанию. To enable this performance counter, see Activate off-by-default counters.
NumberOfFreeConnections	Количество соединений, доступных в пулах соединений. The number of connections available for use in the connection pools. Примечание. Этот счетчик производительности по умолчанию не включен. Note: This performance counter is not enabled by default. Инструкции о том, как его включить, см. в разделе Активация счетчиков, отключенных по умолчанию. To enable this performance counter, see Activate off-by-default counters.
NumberOfInactiveConnectionPoolGroups	Количество уникальных групп пулов соединений, отмеченных для усечения. The number of unique connection pool groups that are marked for pruning. Этот счетчик управляется числом уникальных строк соединения, найденных в домене приложения. This counter is controlled by the number of unique connection strings that are found in the AppDomain.
NumberOfInactiveConnectionPools	Количество неактивных пулов соединений, не участвовавших в последних операциях и ожидающих удаления. The number of inactive connection pools that have not had any recent activity and are waiting to be disposed.
NumberOfNonPooledConnections	Количество активных соединений, не помещенных в пулы. The number of active connections that are not pooled.
NumberOfPooledConnections	Количество активных соединений, которые управляются инфраструктурой пулов соединений. The number of active connections that are being managed by the connection pooling infrastructure.
NumberOfReclaimedConnections	Количество соединений, затребованных сборкой мусора, в которой приложение не вызывает методы Close и Dispose . The number of connections that have been reclaimed through garbage collection where Close or Dispose was not called by the application. Примечание. Если подключения не закрывать и не удалять явно, производительность может снижаться. Note Not explicitly closing or disposing connections hurts performance.
NumberOfStasisConnections	Количество соединений, ожидающих в настоящий момент завершения действия и поэтому доступных для приложения. The number of connections currently awaiting completion of an action and which are therefore unavailable for use by your application.
SoftConnectsPerSecond	Количество активных соединений, извлекаемых из пула соединений. The number of active connections being pulled from the connection pool. Примечание. Этот счетчик производительности по умолчанию не включен. Note: This performance counter is not enabled by default. Инструкции о том, как его включить, см. в разделе Активация счетчиков, отключенных по умолчанию. To enable this performance counter, see Activate off-by-default counters.
SoftDisconnectsPerSecond	Количество активных соединений, возвращаемых в пул соединений. The number of active connections that are being returned to the connection pool. Примечание. Этот счетчик производительности по умолчанию не включен. Note: This performance counter is not enabled by default. Инструкции о том, как его включить, см. в разделе Активация счетчиков, отключенных по умолчанию. To enable this performance counter, see Activate off-by-default counters.

Группы пула подключений и пулы подключений Connection pool groups and connection pools

При использовании проверки подлинности Windows (встроенная безопасность) необходимо следить за счетчиками NumberOfActiveConnectionPoolGroups и NumberOfActiveConnectionPools . When using Windows Authentication (integrated security), you must monitor both the NumberOfActiveConnectionPoolGroups and NumberOfActiveConnectionPools performance counters. Причина в том, что группы пулов соединений сопоставлены с уникальными строками соединений. The reason is that connection pool groups map to unique connection strings. Если используется встроенная безопасность, то пулы соединений сопоставляются со строками соединений и дополнительно создают специальные пулы для отдельных идентификаторов Windows. When integrated security is used, connection pools map to connection strings and additionally create separate pools for individual Windows identities. Например, если Кирилл и Мария, находящиеся в одном домене приложений, используют строку соединения «Data Source=MySqlServer;Integrated Security=true» , создается группа пула соединений для этой строки соединения и два дополнительных пула — один для Кирилла, другой для Марии. For example, if Fred and Julie, each within the same AppDomain, both use the connection string «Data Source=MySqlServer;Integrated Security=true» , a connection pool group is created for the connection string, and two additional pools are created, one for Fred and one for Julie. Если Петр и Елена используют строку подключения с одинаковым именем входа SQL Server «Data Source=MySqlServer;User >;Password= » , создается только один пул для идентификатора . If John and Martha use a connection string with an identical SQL Server login, «Data Source=MySqlServer;User >;Password= » , then only a single pool is created for the identity.

Активация счетчиков, отключенных по умолчанию Activate off-by-default counters

Счетчики производительности NumberOfFreeConnections , NumberOfActiveConnections , SoftDisconnectsPerSecond и SoftConnectsPerSecond отключены по умолчанию. The performance counters NumberOfFreeConnections , NumberOfActiveConnections , SoftDisconnectsPerSecond , and SoftConnectsPerSecond are off by default. Чтобы включить их, добавьте в файл конфигурации приложения следующие данные: Add the following information to the application’s configuration file to enable them:

Получение значений счетчиков производительности Retrieve performance counter values

В следующем приложении командной строки показан способ получения значений счетчиков производительности. The following console application shows how to retrieve performance counter values in your application. Чтобы возвратить данные для всех счетчиков производительности поставщика данных Microsoft SqlClient для SQL Server, подключения должны быть открыты и активны. Connections must be open and active for information to be returned for all of the Microsoft SqlClient Data Provider for SQL Server performance counters.

Здесь используется пример базы данных AdventureWorks. This example uses the sample AdventureWorks database. Строка подключения, представленная в примере кода, предполагает, что база данных установлена и доступна на локальном компьютере, и что созданы имена входа, соответствующие представленным в строках подключения. The connection strings provided in the sample code assume that the database is installed and available on the local computer, and that you have created logins that match those supplied in the connection strings. Имена входа SQL Server придется создать, если сервер использует параметры безопасности по умолчанию, разрешающие только проверку подлинности Windows. You may need to enable SQL Server logins if your server is configured using the default security settings which allow only Windows Authentication. При необходимости измените строки соединения, чтобы они соответствовали среде. Modify the connection strings as necessary to suit your environment.