Мониторинг SQL запросов |Мониторинг запросов в реальном времени SQL Server

Главная — ИБ-аутсорсинг — Защита информации — Безопасность баз данных — Мониторинг баз данных — Мониторинг SQL запросов

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

Работает эта система следующим образом. Клиент, то есть обычный пользователь приложения, отправляет запрос на сервер СУБД, который обрабатывается и исполняется сервером. Если приложение работает некорректно, скорость обработки запроса падает. В этом случае необходимо проводить мониторинг, то есть диагностику работы СУБД. Такая проверка позволит определить, на каком этапе обработки запросов происходят сбои.

Как найти причины снижения производительности SQL Server

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

1. Анализ использования ОЗУ

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

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

Сам по себе объем доступной оперативной памяти не играет важной роли. Администратору базы данных нужно сравнить его с показателем использования ОЗУ. Например, если объем ОЗУ составляет 64 ГБ, а для исполнения запроса используется только 30 ГБ, то снижение производительности СУБД не связано с недостатком оперативной памяти.

Если для исполнения запроса используется 80-90% объема оперативной памяти, нужно использовать такой инструмент как Dynamic Management Views. Это встроенная утилита, которая присутствует во всех версиях SQL Server 2005 года и более поздних модификаций.

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

Обратите внимание! Чем новее версия SQL Server, тем больше инструментов для проведения диагностики встроено в СУБД. Например, в SQL Server 2008 разработчики внедрили инструмент Performance Studio и панель AUDIT, с помощью которой можно записывать запросы пользователей в файл команды SELECT или логи Windows. А в новой версии 2014 года DMV получило расширение sys.dm_exec_query_profiles. С его помощью администратор может отследить источник запроса.

Вернемся к анализу использования ОЗУ с помощью DMV. Чтобы корректно использовать DMV, нужно учитывать несколько нюансов. Во-первых, больше 90% от существующего объема оперативной памяти процесс sqlservr.exe использовать не может. Поэтому показатель загруженности в 80-90% можно считать максимальным и указывающим на недостаток ресурсов. Во-вторых, процесс sqlservr.exe даже после исполнения ресурсоемкого запроса не прекращает использовать ресурс ОЗУ. Это проще всего объяснить на примере.

Представим, что на сервере установлена ОЗУ объемом 128 ГБ. Обычно процесс sqlservr.exe задействует ОЗУ в пределах 30 ГБ, то есть 23% ресурсов. Однако в определенный момент пользователь направляет на сервер ресурсоемкий запрос, для исполнения которого необходимо задействовать всю мощность оборудования. В этот момент потребление вырастает до 100 ГБ, то есть 83% ресурсов. Так вот, процесс sqlservr.exe будет потреблять 83% ресурсов до тех пор, пока служба не будет перезагружена. Поэтому перед тем, как начинать диагностику с помощью DMV, стоит сначала перезагрузить службу. Если объем потребления ресурсов останется прежним, можно переходить к использованию DMV.

Запустив DMV, следует просмотреть sys.dm_os_sys_memory. На экране автоматически будут выведены такие параметры:

Total Physical Memory – общий объем ОЗУ;
Available Physical Memory – объем ОЗУ, который можно задействовать в текущий момент;
Total Page File (MB) – объем ОЗУ вместе с файлами подкачки (отражается в сумме);
Available Page File (MB) – суммарный объем файлов подкачки;
Memory State – текстовое описание текущего состояния ОЗУ.

Таким образом, можно оценить реальный объем потребления оперативной памяти, разделив Available Physical Memory на Total Physical Memory. А возможные причины неисправности можно найти в Memory State, открыв поля system_low_memory_signal_state и system_high_memory_signal_state.

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

2. Анализ загрузки процессора

Чтобы оценить, как работа СУБД отражается на загрузке ЦП, нужно открыть Диспетчер задач и найти вкладку «Процессы». Напротив sqlservr.exe будет указан интересующий нас параметр. Однако в диспетчере задач отражается только текущая нагрузка. Поэтому для полноценного анализа загрузки процессора нужно использовать CPU_Utilization. С его помощью можно узнать, какую нагрузку процесс sqlservr.exe давал ранее. Для этого достаточно использовать следующий запрос:

DECLARE @ts BIGINT;
DECLARE @lastNmin TINYINT;
SET @lastNmin = 30;
SELECT @ts = (SELECT cpu_ticks / ( cpu_ticks / ms_ticks )
FROM sys.dm_os_sys_info);
SELECT TOP(@lastNmin) Dateadd(ms, -1 * ( @ts - [timestamp] ), Getdate())AS
[EventTime],
sqlprocessutilization AS
[SQL Server Utilization],
100 - systemidle - sqlprocessutilization AS
[Other Process CPU_Utilization],
systemidle AS
[System Idle]
FROM (SELECT
record.value('(./Record/@id)[1]', 'int') AS record_id,
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle],
record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization],
[timestamp]
FROM (SELECT[timestamp],
CONVERT(XML, record) AS [record]
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
AND record LIKE'%%')AS x)AS y
ORDER BY record_id DESC;

При этом во второй строке запроса @lastNmin следует заменить на оцениваемый период времени в минутах. Таким образом будет сформирована таблица-отчет с статистикой нагрузки ЦП поминутно.

3. Анализ загрузки локальных дисков

Чтобы проанализировать загрузку локальных дисков, необходимо запустить процесс resmon.exe. Он активирует инструмент Resource Monitor, то есть монитор ресурсов. Resource Monitor позволяет наблюдать за текущим использованием ресурсов сервера, оценивать и контролировать все исполняемые процессы, а также выполнять другие операции.

После запуска resmon.exe открывается окно Resource Monitor, в верхней части которого находится пять вкладок:

Overview (Обзор);
CPU (ЦП);
Memory (Память);
Disk (Диск);
Network (Сеть).

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

Вкладка Disk поделена на три секции:

1. Процессы с дисковой активностью. В этой секции по убыванию перечисляются наиболее ресурсоемкие процессы и запросы. По этому перечню администратор базы данных может оценить, какие операции пользователей нагружают сервер СУБД больше всего.
2. Работа диска. В этой секции перечисляются системные файлы и файлы СУБД, которые используются на момент использования Resource Monitor, а также скорость их чтения и записи, время ответа диска и т.д.
3. Запоминающие устройства. В этой секции отражается количество локальных дисков.

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

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

Активное время загрузки – это показатель, который в процентном соотношении показывает нагрузку на диск. Например, если в этой графе стоит цифра 80, значит ресурсы локального диска задействованы на 80%. Соответственно, если активное время загрузки равняется 90% или превышает этот коэффициент, значит ресурсов локального диска недостаточно.

4. Просмотр блокировок и поиск взаимоблокировок

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

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

Для просмотра блокировок используются различные утилиты: SQL Server Management Studio, Transact-SQL и т.д. Выбор утилиты зависит от того, с какой из них проще работать администратору баз данных.

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

Запрос для просмотра блокировок в T-SQL выглядит так:

SET NOCOUNT ON
GO
SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH
INTO #T
FROM sys.sysprocesses R CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T
GO
WITH
BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)
AS
(
SELECT SPID,
BLOCKED,
CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,
BATCH
FROM #T R
WHERE (BLOCKED = 0 OR BLOCKED = SPID)
AND EXISTS (SELECT *
FROM #T R2
WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)
UNION ALL
SELECT R.SPID,
R.BLOCKED,
CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,
R.BATCH
FROM #T AS R
INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID
WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID
)
SELECT N' ' + REPLICATE (N'| ', LEN (LEVEL)/4 - 1) +
CASE WHEN (LEN(LEVEL)/4 - 1) = 0
THEN 'HEAD - '
ELSE '|------ ' END
+ CAST (SPID AS NVARCHAR (10)) + N' ' + BATCH AS BLOCKING_TREE
FROM BLOCKERS
ORDER BY LEVEL ASC
GO
DROP TABLE #T
GO

Если после введения этого запроса на экране не появляется список блокировок, значит СУБД работает правильно.

Как провести упреждающую диагностику?

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

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

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

Чтобы создать систему круглосуточной или периодической диагностики запросов SQL Server, лучше всего использовать такие инструменты как T-SQL и DMV. Они обладают широким функционалом и довольно просты в использовании.

24.08.2020

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

Заполняя настоящую веб-форму и отправляя указанные в ней персональные данные, я даю свое согласие ООО «СерчИнформ» (далее – оператор) на обработку моих персональных данных в соответствии с Политикой конфиденциальности (https://searchinform.ru/resources/security/, далее — Политика) с правом передачи оператором моих персональных данных третьим лицам, которые являются партнерами оператора. Я понимаю, что мои персональные данные будут переданы с использованием открытых каналов связи (сети международного обмена Интернет) и впоследствии могут обрабатываться как с использованием средств автоматизации, так и без использования таких средств.

Давая настоящее согласие, я подтверждаю, что:

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

Я понимаю, что в соответствии с федеральным законодательством получение оператором согласия на обработку персональных данных является обязательным, и в случае моего отказа от их предоставления и (или) отсутствия согласия на их обработку, интересующие меня услуги не могут быть оказаны оператором.

Я, в соответствии с ч.1 ст. 18 Федерального закона от13.03.2006 г. №38-ФЗ «О рекламе», даю своё согласие ООО «СерчИнформ» (ИНН 7704306397, адрес: 127254, г. Москва, проезд Огородный, д. 16/1, стр. 3, помещение 901) (далее - СерчИнформ) на направление мне на указанные мной контактные данные сообщений в информационных и/или рекламно-информационных целях об услугах (в широком понимании этого определения, в том числе, но не ограничиваясь, сервисах, мероприятиях, встречах, вебинарах, конференциях и т.д.) СерчИнформ со следующими условиями:

Я даю согласие на получение информации и рекламы путем осуществления прямых контактов со мной с помощью любых средств связи включая, но не ограничиваясь, направления sms-сообщений и/или puch-уведомлений и/или электронных писем и/или сообщений в мессенджерах и/или посредством телефонных звонков и/или факсимильной связи.
Я гарантирую, что предоставленная мной информация является полной, достоверной и точной, а также что при представлении информации не нарушаются действующее законодательство Российской Федерации, законные права и интересы третьих лиц. Вся представленная информация заполнена мной в отношении себя лично.
Я согласен/согласна с тем, что текст данного мной по собственной воле и в моих интересах согласия хранится в электронном виде в базе данных СерчИнформ и подтверждает факт согласия на обработку моих контактных данных в соответствии с положениями настоящего документа.
Настоящее согласие действует до поступления в СерчИнформ любым способом (письменно, устно, в электронном виде) требования от меня как субъекта персональных данных о прекращении обработки персональных данных в соответствии с ч.2 ст. 15 Федерального закона от 27.07.2006 г. №152-ФЗ «О персональных данных».
Я подтверждаю, что любое моё действие по нажатию на кнопку «Отправить», «Зарегистрироваться», «Записаться», «Оставить заявку», проставлению отметки « » является достаточной формой согласия и позволяет подтвердить сторонам факт получения такого согласия, при этом иных доказательств для дополнительного подтверждения моего свободного волеизъявления не потребуется.
Я подтверждаю, что владею информацией о том, что в любой момент в течение всего срока действия настоящего согласия, я вправе отозвать согласие и отписаться от получения рассылок как путем устного обращения к представителю СерчИнформ, так и путем перехода по соответствующей ссылке в любом письме, или путем направления письменного уведомления в СерчИнформ об отзыве своего согласия и отказе от рассылок.