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

 http://www.sql.ru/blogs/decolores/1089

Автор: DeColo®es

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

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

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

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

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

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

create procedure dbo.ObjectSearch

        @Object_id int = null,

        @Schema varchar(128) = null,

        @Name varchar(128) = null

as

begin

        set nocount on;

        select 

              *

        from sys.objects o

        where isnull(@Object_id, o.object_id) = o.object_id

        and (@Name is null or o.name like @Name)

        and isnull(@Schema, schema_name(o.schema_id)) = schema_name(o.schema_id)

end

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

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

create table dbo.__ProcLog(

        ID int identity primary key,

        ProcName varchar(128) not null,

        SUserName varchar(256) not null,

        HostName varchar(128) not null,

        Params varchar(max) null,

        StartTime datetime2(6) not null,

        EndTime datetime2(6) null,

        CPU bigint null,

        Duration bigint null,

        Reads bigint null,

        Writes bigint null

        )

create nonclustered index IX___ProcLog on dbo.__ProcLog(ProcName, StartTime)

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

alter procedure ObjectSearch

        @Object_id int = null,

        @Schema varchar(128) = null,

        @Name varchar(128) = null

as

begin

        set nocount on;

        declare 

               @_Reads bigint,

               @_Writes bigint,

               @_CPU bigint,

               @_Duration bigint,

               @_StartTime datetime2(6) = sysdatetime(),

               @_Params varchar(max),

               @_ID int

/* -- Parameter list

        select name+' as ['+name+'],'

        from sys.parameters

        where object_id = OBJECT_ID('dbo.ObjectSearch')

*/

        set @_Params = convert(varchar(max), (

               select

                       @Object_id as [@Object_id],

                       @Schema as [@Schema],

                       @Name as [@Name], 

                       @@TRANCOUNT as [@__TRANCOUNT],

                       @@OPTIONS as [@__OPTIONS]

               for xml path('p')

        ))

        insert into dbo.__ProcLog(ProcName, SUserName, HostName, Params, StartTime)

        values(

               OBJECT_SCHEMA_NAME(@@PROCID)+'.'+OBJECT_NAME(@@PROCID), -- ProcName

               SUSER_SNAME(),

               HOST_NAME(),

               @_Params,

               @_StartTime

        )

        set @_ID = SCOPE_IDENTITY()

        -- Запоминаем значения счетчиков до выполнения процедуры

        select

               @_CPU = cpu_time,

               @_Duration = total_elapsed_time,

               @_Reads = logical_reads,

               @_Writes = writes

        from sys.dm_exec_requests s

        where session_id = @@spid

--======================== Оригинальный текст процедуры BEGIN

        select 

              *

        from sys.objects o

        where isnull(@Object_id, o.object_id) = o.object_id

        and (@Name is null or o.name like @Name)

        and isnull(@Schema, schema_name(o.schema_id)) = schema_name(o.schema_id)

--======================== Оригинальный текст процедуры END  

        -- Обновляем значения счетчиков в информации о запросе

        update pl set

               pl.CPU      = s.cpu_time - @_CPU,

               pl.Duration = s.total_elapsed_time - @_Duration,

               pl.Reads    = s.logical_reads - @_Reads,

               pl.Writes   = s.writes - @_Writes,

               pl.EndTime  = sysdatetime()

        from dbo.__ProcLog pl

        inner join sys.dm_exec_requests s

               on session_id = @@spid

        where pl.ID = @_ID

end

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

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

·  В параметры сохраняем значения @@TRANCOUNT и @@OPTIONS. Знание того, как различаются настройки, поможет выявить проблему использования разных планов выполнения.

·  Имя пользователя - важный параметр. Очень часто отдельные пользователи жалуются что "все работает медленно", при этом сами же запрашивают ненужные лишние данные.

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

·  Имя процедуры не "зашито" в код, а "вычисляется" в процессе работы - это конечно создает незначительную дополнительную нагрузку на сервер, но устраняет возможность ошибки разработчика при переносе кода в другие процедуры методом copy/past.

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

create view dbo.__ProcLog_view

as

select

        ID,

        ProcName,

        SUserName,

        HostName,

        Params,

        StartTime,

        EndTime,

        CPU,

        Duration,

        Reads,

        Writes,

        (

        select 

               ParamName+','

        from (

               select 

                       data.value('local-name(.)', 'varchar(200)') as ParamName

               from xParams.nodes('/p/@*') a(data)

               where data.value('local-name(.)', 'varchar(200)') not like '[_][_]%'

        ) q

        for xml path('')

        ) as UsedParams

from dbo.__ProcLog pl

cross apply(select convert(xml, Params) as xParams) pq

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

exec ObjectSearch @Name = '%Search%'

go 10

exec ObjectSearch @Object_id = 1

go 2

exec ObjectSearch @Object_id = 1, @Name = '%Search%'

go

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

select

        count(*) as ReqCount,

        avg(CPU) as AvgCPU,

        avg(Duration) as AvgDuration,

        avg(Reads) as AvgReads,

        avg(Writes) as AvgWrites,

        UsedParams

from dbo.__ProcLog_view

where ProcName = 'dbo.ObjectSearch'

group by UsedParams

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