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