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

добавлено: 28 ноя 11
понравилось:0
просмотров: 7266
комментов: 6

теги:

Автор: 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

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

    Обсуждаем тут.
  • Комментарии


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

    • Jeminem, формат блога вряд ли позволит рассказать обо всех вариантах анализа полученных данным методом значений. А приводить конкретные примеры можно, но без общей "теории" они скорее введут в заблуждение... Хотя попробовать, кенечно стоит, но это будет не сегодня. ;)

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

      2) В действительности я не могу понять, чем этот подход, когда вы явно ухудшили производительность процедуры лучше SQL Trace. Ваш подход синхронный, более того, если таких функций много, то таблица __ProcLog ещё и узким местом может стать. По мне, намного проще запустить серверную трассу, а потом проанализировать основные паттерны использования процедуры.

    • У нас используется схожий подход. Спасибо DeColores-у - добавил еще логгирование cputime. :)

      Sergey Mikhalev, иногда бывает полезно посмотреть - а как же процедуры работали неделю - месяц назад. И постоянно крутить для этих целей трейс как-то жирно будет.

      + небольшая обертка к подобному логу позволяет достаточно легко вести отладку

    • МИхаил, трассу еще нужно настроить, если её не фильтровать, то ресурсы она будет отъедать у всех запросов, а не только у тех, которые нужно "мониторить". К тому же, как я написал в самом посте, в случае использования процедур семейства sp_prepexec, "тело" запроса и его параметры будут в разных строках трассы и "связывать" их - то еще удовольствие. И естественно, таким способом не стоит мониторить процедуры, которые вызываются очень часто.

    • 22 декабря 2011, 22:33 I_Hate_Registration

      Отличная статья, будем пробовать.



    Необходимо войти на сайт, чтобы оставлять комментарии