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