Поиск медленных запросов в SQL Server

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

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

declare @dayportion float;
declare @substringlength int;
set @dayportion=-1.0;
set @substringlength=200;

SELECT TOP 15 
    sum(total_logical_reads) 'Total Logical Reads', 
    sum(total_logical_writes) 'Total Logical Writes',
    sum(total_worker_time) 'Total CPU',
    sum(execution_count) 'Count',
    sum(total_logical_reads) * 100 / (select sum(total_logical_reads) from sys.dm_exec_query_stats where last_execution_time > dateadd(day, @dayportion, getdate())) 'Pct of Reads',
    sum(total_logical_writes) * 100 / (select sum(total_logical_writes) from sys.dm_exec_query_stats where last_execution_time > dateadd(day, @dayportion, getdate())) 'Pct of Writes',
    sum(total_worker_time) * 100 / (select sum(total_worker_time) from sys.dm_exec_query_stats where last_execution_time > dateadd(day, @dayportion, getdate())) 'Pct of CPU',
    sum(total_logical_reads) / sum(execution_count) 'Avg Reads',
    sum(total_logical_writes) / sum(execution_count) 'Avg Writes',
    sum(total_worker_time) / sum(execution_count) 'Avg CPU',
    statement_text
FROM (
        select 
        total_logical_reads, 
        total_logical_writes,
        total_worker_time,
        execution_count,
        substring(SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
            ((CASE qs.statement_end_offset
              WHEN -1 THEN DATALENGTH(st.text)
             ELSE qs.statement_end_offset
             END - qs.statement_start_offset)/2) + 1), 0, @substringlength) as statement_text
        FROM sys.dm_exec_query_stats qs 
        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
        WHERE last_execution_time > dateadd(day, @dayportion, getdate())
) x
group by statement_text
ORDER BY 'Pct of CPU' DESC

Этот запрос мне дали коллега в Клике, когда я только начинал там работать. Впоследствии я его немного доработал и улучшил и теперь делюсь с вами. Дело в том, что на рабочих серверах нельзя устанавливать какие-то утилиты или инструменты, которые бы позволили смотреть визуально подобные вещи, и в те времена не так много доступного было.

У SQL Server Management Studio есть встроенная возможность, но очень это визуальное окно, которое подтормаживает. В те моменты, когда база данных тормозит под нагрузкой у меня не удавалось открыть это окно, а выполнить подобный запрос очень легко.

В самом начале есть параметр dayportion – это количество дней, за которые мы хотим получить информацию. Я обычно смотрю за последний день, поэтому параметр равен -1.

Результат отсортирован по колонке Pct of CPU. Это отношение затрат на выполнение запроса к количеству выполнений. Чем медленнее запросы и чаще выполняются, тем выше они будут в результате.

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

Я погонял несколько запросов и у меня получилась вот такая картина:

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

У меня был случай, когда этот запрос вернул запрос select без каких-либо фильтров и он выполнялся много раз. В логике программы был баг и в определенный момент запрос мог выполняться без фильтров и это был баг как по логике, так и проблема для сервера.

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

select * from Member where FacebookProfileID = 7000011

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

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

А теперь я покажу, как то же самое увидеть визуально, с помощью красивых возможностей SQL Server Management Studio. В Object Explorer кликаем правой кнопкой по самому верхнему элементу дерева (там вы должны видеть имя базы данных) и в контекстном меню выбираем Activity Monitor:

Внизу окна должны есть несколько закладок и наиболее интересными являются – Active Expensive Queries и Recent Expensive Queries:

Теперь по оптимизации и как получить информацию, которая позволит понять, что не так с запросом, и как сделать так, чтобы он выполнялся быстрее.

Я несколько раз уже показывал, как пользоваться статистикой времени и io:

set statistics time on
set statistics io on

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

Но эти две команды не единственные помощники во время поиска проблемных проблем в запросах.

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

set showplan_text on

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

select * from Member where MemberID = 7000001

Вместо реального результата мы должны увидеть план выполнения в виде текста, как показано на следующем рисунке:

Отключаем эту функцию, прежде чем перейти к следующей:

set showplan_text off

А следующей опцией будет showplan_all:

set showplan_all on

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

Отключаем отображение полной информации о плане:

set showplan_all off

Следующая версия того, что мы уже видели – это showplan_xml:

set showplan_xml on

Эта опция позволяет включить отображение плана выполнения в виде XML. Читать его не так уж и легко, даже если отформатировать с помощью специальных средств:

А вот если кликнуть на плане, то он откроется в виде визуального представления. SQL Server Management Studio умеет рендерить подобные планы в визуальное представление, которое мы видели раньше. Так что XML будет удобен в тех случаях, когда его нужно отправить кому-то на анализ.

Чтобы проще было сохранять XML кликните на него, чтобы открыть визуальное представление. Теперь здесь кликаем правой кнопкой и появляется опция – Save Execution Plan As...

Теперь мы можем познакомиться с реальным планов выполнения. До сих пор это была информация о предполагаемом плане выполнения. Я показывал вам, что SQL Server Management Studio позволяет визуально посмотреть предполагаемый и реальный план. То же самое и в виде кода.

Итак, реальный план включаем командой:

set statistics profile on

Теперь если выполнить запрос, то мы увидим в панели результата не только реальную найденную строку, но и план выполнения:

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

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

set statistics profile off

А следующая команда – это отображение реального плана выполнения в XML формате:

set statistics xml on

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

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

---

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