Tips for DBA: The Spy for stored procedures

Вашему вниманию предлагается макет сценария и методики, позволяющей организовать учёт исполнения хранимых процедур пользовательской базы данных. Пример сценария само-достаточен, т.е. для его успешной работы необходимо только заменить в тексте сценария слово 'ТУТ ДОЛЖНО БЫТЬ ИМЯ ВАШЕЙ БАЗЫ ДАННЫХ!!!!!!!!!!!!!!!!' на имя вашей базы данных, использование процедур которой необходимо отслеживать. Сценарий создаёт задание Агента 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