SQL Server:プロダクション環境で現在使われていないストアドプロシージャを探す方法

背景

プロダクション環境で現在使われていないテーブルを探す方法について以前記事にしましたが、これのストアドプロシージャ版を整理しておこうと思い、方法を考えてみました。

アイデア

dm_exec_procedure_statsというDMVを使います。このDMVは、キャッシュされたストアドプロシージャの統計情報をみることができます。

例えば、以下のクエリを実行することで、各ストアドプロシージャの最終実行時間、キャッシュされてからの累計実行回数などが取得できます。

select
  getdate() as collect_date,
  object_name(ps.object_id, ps.database_id) as object_name,
  db_name(ps.database_id) as database_name,
  ps.last_execution_time,
  o.modify_date,
  ps.cached_time,
  ps.execution_count
from
  sys.dm_exec_procedure_stats  as ps
  left join sys.objects as o on o.object_id = ps.object_id
where object_name(ps.object_id, ps.database_id) is not null
and object_name(ps.object_id, ps.database_id) not like 'sp[_]MS%' --レプリ系除外
order by cached_time asc

この実行結果を定期的にダンプし続けることで、「実行されたことがあるストアドプロシージャのリスト」が作れます。あとば、DBに存在するストアドプロシージャと比較することで、未使用のストアドプロシージャを特定できます。

考慮点

１．ダンプ間隔については、「キャッシュアウトされない程度に短い間隔」を指定する必要があります。
これは環境によって変わってきますが、10秒ごとにダンプしておけばほとんどの環境ではキャッシュアウトされることなく、実行されたすべてのストアドプロシージャの情報を収集できるのではと思います。
※環境ごとのキャッシュアウトされる最短時間を求める方法については別の機会で考えてまとめたいと思います。

２．「どれだけの期間実行されていないストアドプロシージャを未使用と定義するか」も決める必要があります。月末月初のバッチ処理が存在する可能性を考慮すると、最低でも「1か月間」が妥当な期間だと思われます。

クエリ

githubで公開しています。

１．調査用テーブル_create.sql を1回だけ実行してダンプ用のテーブルを作成する

--[dm_exec_procedure_stats_usage_dump]というテーブルを作成
select
  getdate() as first_insert_date,
  object_name(ps.object_id, ps.database_id) as object_name,
  db_name(ps.database_id) as database_name,
  ps.last_execution_time,
  o.modify_date as last_modify_date,
  o.create_date as create_date,
  ps.cached_time as last_cached_time,
  ps.execution_count as last_execution_count
into dm_exec_procedure_stats_usage_dump
from
  sys.dm_exec_procedure_stats  as ps
  left join sys.objects as o on o.object_id = ps.object_id
where 1=0

--クラスタ化インデックス作成
create clustered index CIX_dm_exec_procedure_stats_usage_dump on dm_exec_procedure_stats_usage_dump(object_name, database_name)

--Unique制約作成
alter table dm_exec_procedure_stats_usage_dump add constraint UQ_dm_exec_procedure_stats_usage_dump unique (object_name, database_name)   

２．定期的(10秒-1分間程度)に「調査用テーブル_merge.sql」を実行して情報をダンプ

set nocount on
merge dm_exec_procedure_stats_usage_dump as target
using (
    select
      getdate() as first_insert_date,
      object_name(ps.object_id, ps.database_id) as object_name,
      db_name(ps.database_id) as database_name,
      ps.last_execution_time,
      o.modify_date,
      o.create_date,
      ps.cached_time,
      ps.execution_count
    from
	(
	    --object_idで重複する場合があるため重複カット
		select * from (
			select *, row_number() over(partition by object_id order by last_execution_time desc) as rownum from sys.dm_exec_procedure_stats
		) as ps
		where rownum = 1
	) as ps
      left join sys.objects as o on o.object_id = ps.object_id
    where object_name(ps.object_id, ps.database_id) is not null
    and object_name(ps.object_id, ps.database_id) not like 'sp[_]MS%' --レプリ系除外
) as source
on target.object_name = source.object_name
when matched then
    update set last_execution_time = source.last_execution_time
              ,last_modify_date = source.modify_date
              ,create_date = source.create_date --alterでなくdrop+createされるケースを考慮
              ,last_cached_time = source.cached_time
              ,last_execution_count = source.execution_count
when not matched then
    insert (first_insert_date, object_name, database_name, last_execution_time, last_modify_date, create_date, last_cached_time, last_execution_count)
    values (source.first_insert_date, source.object_name, source.database_name, source.last_execution_time, source.modify_date, source.create_date, source.cached_time, source.execution_count)
option (maxdop 1) 
;

※最低でも1か月間は定期的にダンプし続けるのが望ましい

３．「情報収集完了後に未使用ストアドを特定するクエリ.sql」を実行して未使用ストアドを特定

declare @db_name as nvarchar(1000)
set @db_name = 'master' --未使用ストアドを取得したいDB名をセット
declare @sql nvarchar(max)

set @sql = '
select * from ' + @db_name + '.sys.objects
where name not in (select object_name from dm_exec_procedure_stats_usage_dump where database_name = ''' + @db_name + ''')
and type=''P''
'
execute(@sql)