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