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

Проблема инвентаризации запросов

Ручной анализ кода в sys.sql_modules – это распространенный подход для поиска запросов с подсказками. Однако этот метод может давать ложные срабатывания и неэффективен для запросов, выполняемых ad hoc или динамически генерируемых в приложениях. Другой подход – использование трассировки на стороне сервера или профилировщика, но это может создавать значительную нагрузку на сервер и полагаться на свободные правила анализа.

Лучшее решение с использованием DMV

К счастью, существует лучшее решение с использованием DMV sys.dm_exec_cached_plans, sys.dm_exec_sql_text и sys.dm_exec_text_query_plan. Анализируя сохраненные в системе планы запросов, мы можем получить ценную информацию о подсказках, используемых в запросах.

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

SELECT t.[text], qp.query_plan 
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
CROSS APPLY sys.dm_exec_text_query_plan(p.plan_handle, 0, -1) AS qp;

Изучая планы запросов, мы можем определить подсказки, изучая XML-вывод. Некоторые распространенные подсказки, которые можно получить из XML, включают FORCESEEK, FORCESCAN, NOEXPAND и MAXDOP.

Получение подсказок запроса с использованием DMV

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

SELECT [Query] = t.[text], [Database] = DB_NAME(t.dbid), qp.query_plan,
    [ForceSeek]  = CASE WHEN qp.query_plan   LIKE '%ForceSeek="1"%'                 THEN 1 ELSE 0 END,
    [ForceScan]  = CASE WHEN qp.query_plan   LIKE '%ForceScan="1"%'                 THEN 1 ELSE 0 END,
    [NoExpand]   = CASE WHEN qp.query_plan   LIKE '%NoExpandHint="1"%'              THEN 1 ELSE 0 END,
    [ForceIndex] = CASE WHEN qp.query_plan   LIKE '%ForcedIndex="1" ForceSeek="1"%' THEN 1 ELSE 0 END,
    [NoLock]     = CASE WHEN UPPER(t.[text]) LIKE '%NOLOCK%'                        THEN 1 ELSE 0 END,
    [MaxDop]     = CASE WHEN qp.query_plan   LIKE '%<QueryPlan%[^<]%"MaxDopSet%' 
                       AND UPPER(t.[text])   LIKE '%MAXDOP%'                        THEN 1 ELSE 0 END
FROM 
    sys.dm_exec_cached_plans AS p
    CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t
    CROSS APPLY sys.dm_exec_text_query_plan(p.plan_handle, 0, -1) AS qp
WHERE 
    t.[text] NOT LIKE '%dm_exec_cached_plans%' -- чтобы исключить этот запрос из результата
    AND
    (
      qp.query_plan LIKE '%ForceSeek="1"%'
      OR qp.query_plan LIKE '%Forcescan="1"%'
      OR qp.query_plan LIKE '%NoExpandHint="1"%'
      OR qp.query_plan LIKE '%ForcedIndex="1" ForceSeek="1"%'
      OR UPPER(t.[text]) LIKE '%NOLOCK%'
   OR (qp.query_plan LIKE '%<QueryPlan%[^<]%"MaxDopSet%' AND UPPER(t.[text]) LIKE '%MAXDOP%')
    );

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

Соображения и предостережения

При использовании этого метода следует учитывать несколько соображений и предостережений:

• Некоторые подсказки, такие как OPTION (FORCE ORDER) или подсказки соединения, не отображаются в детерминированном виде в плане запроса. Для идентификации этих подсказок может потребоваться анализ текста запроса.
• DMV отображают только планы, которые в настоящее время находятся в кэше. Планы могут быть удалены или удалены при перезапуске сервера или выполнении DBCC FREEPROCCACHE.
• Некоторые подсказки, такие как OPTION (RECOMPILE), предотвращают кэширование плана вообще.
• Если включена настройка оптимизации для рабочих нагрузок ad hoc, запросы, связанные с планами однократного использования, могут быть недоступны.

Вывод

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

Используя мощь DMV, администраторы баз данных могут получить представление о выполняемых запросах в их системе SQL Server и принимать обоснованные решения для оптимизации производительности и устранения проблем.