Поиск таймаутов выполнения запросов с помощью Query Store

Недавно во время обсуждения по решению проблем, связанных с таймаутами запросов в Azure SQL Database, я подумала — интересно, можете ли вы найти запросы, время ожидания которых истекло, в Query Store (хранилище запросов)?

Оказывается, можно.

Основные сведения о таймаутах запросов

Вот некоторые основы, касающиеся таймаутов выполнения запросов для SQL Server, Azure SQL Database и Azure SQL Managed Instance:

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

• Таймауты устанавливаются приложением, которое осуществляет вызов . Если вы явно не задали таймаут в строке соединения, обычно используется таймаут по умолчанию. Для таймаутов выполнения запросов, как правило, это 30 секунд.

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

Традиционный способ обнаружения таймаутов запросов

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

"Классический" способ сделать это — запустить трассировку базы данных, используя классический SQL Trace (возможно, с Profiler), либо трассировку Extended Events. Трассировка направлена на поиск события Attention, которое также известно как Error (ошибка) 3617.

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

Поиск таймаутов в Query Store

Если вы включили Query Store, то сможете найти таймауты сразу, без выполнения трассировки.

Здесь есть одна оговорка: Query Store не обязательно собирать каждый запрос. Параметр QUERY_CAPTURE_MODE может быть установлен на AUTO, что позволяет сбалансировать пространство, занимаемое планами, и при этом совсем не нужно накапливать все запросы.

Давайте вызовем быстрый тайм-аут и просмотрим его в Query Store.

Воспроизведение таймаута выполнения запроса

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

Я включила Query Store в базе данных со следующими параметрами:

ALTER DATABASE [ContosoRetailDW]
SET QUERY_STORE = ON
    (
      OPERATION_MODE = READ_WRITE,
      CLEANUP_POLICY = ( STALE_QUERY_THRESHOLD_DAYS = 90 ),
      DATA_FLUSH_INTERVAL_SECONDS = 900,
      MAX_STORAGE_SIZE_MB = 1024,
      INTERVAL_LENGTH_MINUTES = 60,
      SIZE_BASED_CLEANUP_MODE = AUTO,
      MAX_PLANS_PER_QUERY = 200,
      WAIT_STATS_CAPTURE_MODE = ON,
      QUERY_CAPTURE_MODE = ALL
    );

ALL может быть не лучшим значением QUERY_CAPTURE_MODE для баз данных в продакшне — здесь он полезен, когда у меня нет реальной рабочей нагрузки. Я также повысила уровень совместимости базы данных, потому что он устарел, и далее в запросе мне хочется использовать синтаксис TRY_CONVERT.

ALTER DATABASE ContosoRetailDW SET COMPATIBILITY_LEVEL = 140;
GO

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

BEGIN TRAN
 DECLARE @OnlineSalesKey INT
 SELECT @OnlineSalesKey = OnlineSalesKey FROM dbo.FactOnlineSales WITH (UPDLOCK);

Теперь подключите вторую сессию. В диалоговом окне выберите "Options" (Параметры).

В диалоговом окне параметров подключения установите значение Execution time-out (таймаут выполнения) равным 5 секундам.

Выберите Connect (Подключиться).

Выполните следующий запрос в этой сессии:

SELECT MAX(DateKey) as MaxDate
FROM FactOnlineSales;

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

Msg -2, Level 11, State 0, Line 0
Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

Теперь, вернувшись к первому сеансу, выполните:

ROLLBACK

Поиск таймаута в Query Store

Чтобы обнаружить таймауты в Query Store, нужно искать запросы, для которых в sys.query_store_runtime_stats значение execution_type установлено на 3. Обратите внимание, что это не только таймауты запросов — данный статус означает "Прерванное выполнение по инициативе клиента". Но при тестировании я обнаружила, что это включает в себя и таймауты запросов.

Вот пример запроса к Query Store DMVs, который осуществляет поиск такого статуса:

SELECT
 qst.query_sql_text,
 qrs.execution_type,
 qrs.execution_type_desc,
 qpx.query_plan_xml,
 qrs.count_executions,
 qrs.last_execution_time
FROM sys.query_store_query AS qsq
JOIN sys.query_store_plan AS qsp on qsq.query_id=qsp.query_id
JOIN sys.query_store_query_text AS qst on qsq.query_text_id=qst.query_text_id
OUTER APPLY (SELECT TRY_CONVERT(XML, qsp.query_plan) AS query_plan_xml) AS qpx
JOIN sys.query_store_runtime_stats qrs on qsp.plan_id = qrs.plan_id
WHERE qrs.execution_type =3
ORDER BY qrs.last_execution_time DESC;
GO

Вот скриншот результатов, которые я вижу в SSMS для этого запроса:

Я кликнула кнопку "cancel executing query" (отмена выполнения запроса) для запросов в строках 2 и 3 этого набора результатов. Запрос на строку 1 был запущен дважды с таймаутом выполнения в SSMS, установленным на 5 секунд, когда таблица была заблокирована в другой сессии.

Это не идеально, но проверить стоит

Как я уже говорила, вы, вероятно, не захотите фиксировать все запросы в Query Store в базе данных для продакшна. Когда случаются таймауты, нет никакой гарантии, что в Query Store сохранилась запись о запросе. (Это справедливо для всех систем мониторинга — при внимательном наблюдении приходится идти на компромисс с производительностью).

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

В Query Store есть еще много интересных вещей

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

Однако Query Store осуществляет многие функции по настройке производительности систем наблюдения. Это, безусловно, позволит удешевить ваше решение по мониторингу, особенно для контроля доступности, выполняя при этом большую часть настройки и устранения неполадок.

Продолжим изучать Query Store на открытом уроке, который пройдет 11 июля. О чем поговорим:
- Что такое утилита Query store?
- Как и зачем ее можно использовать?
- Как интерпретировать информацию собранную Query Store?

Записаться на урок можно на странице курса "MS SQL Server Developer".