SQL Server. Анализ использования параметров процедур

SQL Server. Анализ использования параметров процедур

В большинстве баз данных, которые мне доводилось видеть, встречаются «универсальные» процедуры со множеством параметров. Как правило, это процедуры поиска чего-либо в базе данных или же процедуры, формирующие набор данных для отчета. При этом чем больше параметров, тем реже они используются.
То есть процедура умеет, например, искать клиента по имени, части имени, клиентскому номеру, номеру счета, но при этом естественно, одновременно используется очень ограниченное число параметров. Действительно, при поиске клиента по какому-либо его уникальному номеру (ИНН, СНИЛС, ИКК и т.д.) нет
смысла уточнять его ФИО или город проживания.
Внутри процедур как правило, есть один «главный» «универсальный» запрос, учитывающий все параметры при фильтрации. Как и все универсальное, быстро выполняться он не будет даже если «обложить» всю таблицу индексами — индексы хороши для поиска чего-то конкретного.

Для оптимизации таких универсальных процедур, я бы рекомендовал для начала определить наиболее типичные, часто используемые варианты комбинаций реально используемых параметров и для этих комбинаций реализовать отдельные специализированные запросы. При оптимизации, всего 4 комбинации параметров процедуры поиска не просто использовались в 95% случаев вызова процедуры, но и создавали до 10% общей нагрузки на сервер.
После выделения этих вариантов использования в отдельные запросы, нагрузка на сервере была снижена на эти 10%.

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

Но все решается проще — логирование параметров можно добавить в саму процедуру. Кроме того, мы добавим также сохранение информации и длительности выполнения запроса, логических чтениях и другую служебную информацию, которая может понадобиться для анализа проблем производительности.

Итак, предположим, что наша у нас есть такая процедура поиска:

Процедура ищет объекты в базе данных, фильтруя их по ID объекта, имени схемы или маске имени самого объекта. Думаю, что многие узнают используемые приемы «универсализации» в данном запросе.

Создадим таблицу, в которую будем сохранять параметры вызова процедур и статистику времени выполнения:

Теперь модифицируем процедуру для того, чтобы сохранять значения параметров в этот «лог»:

Несколько комментариев по поводу того, что и как сделано:

  • Сами параметры сохраняются сразу после запуска процедуры — это нужно для случаев, когда при каком-либо сбое в ходе выполнения процедуры до финальных шагов дело просто не дошло. Например, интересные для оптимизации случаи, когда выполнение было завершено по time-out.
  • В параметры сохраняем значения @@TRANCOUNT и @@OPTIONS. Знание того, как различаются настройки, поможет выявить проблему использования разных планов выполнения.
  • Имя пользователя — важный параметр. Очень часто отдельные пользователи жалуются что «все работает медленно», при этом сами же запрашивают ненужные лишние данные.
  • Параметры собираем в формате xml. Это с одной стороны, не слишком сильно нагружает систему (в отличии от разбора xml документа) в процессе выполнения запросов пользователями, а с другой — позволяет автоматизировать процесс анализа комбинаций параметров и их значений.
  • Имя процедуры не «зашито» в код, а «вычисляется» в процессе работы — это конечно создает незначительную дополнительную нагрузку на сервер, но устраняет возможность ошибки разработчика при переносе кода в другие процедуры методом copy/past.Представление, выводящее использованную комбинацию параметров:

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


    Ну и последнее… Как правило, проблема таких универсальных процедур не в том, что их сложно оптимизировать. В большом % случаев, такие процедуры возвращают пользователю на экран огромное количество столбцов и еще большее — строк. Очевидно, что человек просто не способен как-то разумно обрабатывать таблицу в несколько сотен столбцов и тысячи строк. Поэтому действительно правильно будет начать с того, чтобы понять какие конкретные задачи решает данная процедура и для конкретных задач написать не менее конкретные запросы.

    Автор: Дмитрий Костылёв

    ***   ***   ***   ***   ***   ***   ***   ***   ***   ***   ***   ***
    Полезные Скрипты

    Рубрика Проверь себя

    Ссылка на наш канал YouTube

Запись опубликована в рубрике Скрипты с метками , . Добавьте в закладки постоянную ссылку.

Добавить комментарий

Войти с помощью: