Анализ использования хранимых процедур

Вашему вниманию предлагается макет сценария и методики, позволяющей организовать учёт исполнения хранимых процедур пользовательской базы данных. Пример сценария само-достаточен, т.е. для его успешной работы необходимо только заменить в тексте сценария слово‘ТУТДОЛЖНОБЫТЬИМЯВАШЕЙБАЗЫДАННЫХ!!!!!!!!!!!!!!!!’ на имя вашей базы данных, использование процедур которой необходимо отслеживать. Сценарий создаёт задание Агента SQL Server, которое по установленному расписанию (расписание подбирается в зависимости от нагрузки сервера) исполняет сценарий T-SQL. Исполняемый заданием сценарий создаёт по необходимости в базе TEMPDB таблицу Activproc, и потом записывает в ней статистику использования процедур, получая актуальные на момент исполнения метаданные сервера. Для того, чтобы понять принципы работы сценария, ознакомьтесь с теми разделами BOL, в которых описаны задействованные в сценарии административные динамические представления и функции.

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

В примере сценария местом размещения таблицы статистики процедур выбрана база данных TEMPDB. Этот факт нужно учитывать, поскольку при каждом запуски службы SQL Server эта база данных создаётся заново, и информация в таблице статистики будет утеряна.

Пример сценария:

USE [msdb]
GO
DECLARE @jobId BINARY(16)

EXEC msdb.dbo.sp_add_job @job_name=N'Activproc', 
	@enabled=1, 
	@notify_level_eventlog=0, 
	@notify_level_email=2, 
	@notify_level_netsend=0, 
	@notify_level_page=0, 
	@delete_level=0, 
	@description=N'Собирает простую статистику по использованию хранимых процедур', 
	@category_name=N'Database Maintenance', 
	@owner_login_name=N'sa', 
--	@notify_email_operator_name=N'MS-SQL-Admins', 
	@job_id = @jobId OUTPUT

EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, 
	@step_name=N'Activproc 1', 
	@step_id=1, 
	@cmdexec_success_code=0, 
	@on_success_action=1, 
	@on_success_step_id=0, 
	@on_fail_action=2, 
	@on_fail_step_id=0, 
	@retry_attempts=0, 
	@retry_interval=0, 
	@os_run_priority=0, 
	@subsystem=N'TSQL', 
	@command=N'IF NOT EXISTS (SELECT * FROM tempdb.sys.objects WHERE name = ''Activproc'')
CREATE TABLE tempdb.[dbo].[Activproc]
	(
		[SP_Name] sysname NOT NULL,
		[last_execution_time] datetime NOT NULL,
		[avg_elapsed_time_sec] money NOT NULL
	)
DECLARE @SP_Name sysname, @last_execution_time datetime, @avg_elapsed_time_sec money
DECLARE c_Activproc CURSOR GLOBAL FAST_FORWARD READ_ONLY FOR
SELECT TOP 100 PERCENT    OBJECT_NAME(s.objectid,s.dbid) AS SP_Name
			, MAX(st.last_execution_time) AS last_execution_time
			, SUM(CAST((st.total_elapsed_time * 1.0 /100000)/st.execution_count AS money)) 
			  AS avg_elapsed_time_sec
         FROM master.sys.dm_exec_cached_plans AS c
  CROSS APPLY master.sys.dm_exec_query_plan (c.plan_handle) AS q
   INNER JOIN master.sys.dm_exec_query_stats AS st
           ON c.plan_handle = st.plan_handle
  CROSS APPLY master.sys.dm_exec_sql_text(sql_handle) AS s
        WHERE c.cacheobjtype = ''Compiled Plan''
          AND c.objtype = ''Proc''
          AND q.dbid = DB_ID()
     GROUP BY DB_NAME(q.dbid),OBJECT_NAME(s.objectid,s.dbid)  
     ORDER BY avg_elapsed_time_sec DESC

OPEN GLOBAL c_Activproc
WHILE 1 = 1
BEGIN
	FETCH c_Activproc INTO @SP_Name, @last_execution_time, @avg_elapsed_time_sec
	IF @@fetch_status <> 0 BREAK
	IF @SP_Name NOT IN (SELECT SP_Name FROM tempdb.dbo.Activproc WHERE SP_Name = @SP_Name)
	BEGIN
		INSERT INTO tempdb.dbo.Activproc (SP_Name, last_execution_time, avg_elapsed_time_sec) 
		VALUES (@SP_Name, @last_execution_time, @avg_elapsed_time_sec)
	END
	ELSE
	BEGIN
		UPDATE tempdb.dbo.Activproc 
		SET last_execution_time = @last_execution_time, avg_elapsed_time_sec = @avg_elapsed_time_sec
		WHERE SP_Name = @SP_Name
	END
END
CLOSE GLOBAL c_Activproc
DEALLOCATE c_Activproc
GO', 
	@database_name=N'ТУТДОЛЖНОБЫТЬИМЯВАШЕЙБАЗЫДАННЫХ!!!!!!!!!!!!!!!!', 
	@flags=4
EXEC msdb.dbo.sp_update_job @job_id=@jobId, 
	@start_step_id = 1
EXEC msdb.dbo.sp_add_jobschedule @job_id=@jobId, 
	@name=N'1', 
	@enabled=1, 
	@freq_type=4, 
	@freq_interval=1, 
	@freq_subday_type=4, 
	@freq_subday_interval=1, 
	@freq_relative_interval=0, 
	@freq_recurrence_factor=0, 
	@active_start_date=20090217, 
	@active_end_date=99991231, 
	@active_start_time=0, 
	@active_end_time=235959, 
--	@schedule_uid=N'ffb0a0d2-93bc-49d0-9fc7-4e35140bfd9f'
EXEC msdb.dbo.sp_add_jobserver @job_id=@jobId, 
	@server_name = N'(local)'
GO

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

SELECT   [SP_Name]
        ,[last_execution_time]
        ,[avg_elapsed_time_sec]
    FROM [tempdb].[dbo].[Activproc]
ORDER BY [avg_elapsed_time_sec] DESC
GO

Автор: Александр Гладченко