SQL Server: 自動リコンパイルによる簡易的な自動チューニング機能

SQL Server 2017 および SQL Databaseでは自動チューニングという機能が提供されています。
この中に、「自動プラン選択修正」という機能があります。
こちらは、実行プラン変更によって突然クエリが遅くなった場合でも、過去の最適な実行プランに自動的に変更してくれる、というものです。

SQL Server 2016以前のバージョンでは、この機能は提供されておらず、非典型パラメータを使ったコンパイルによって実行プランが狂った場合は、手動でリコンパイルする必要がありました。

with recompile / option(recompile) オプション付与による強制再コンパイルの場合、都度コンパイルされるため統計情報が古くなっていなければ、各パラメータごとに最適な実行プランが生成される可能性が高いです。一方で、都度コンパイルされるためにオーバーヘッドの増加は避けられません。そのためできる限りこのオプションは避けたいところです。

以上を踏まえ、リコンパイル系オプションはつけずに実行プランが狂ってしまった場合に、自動チューニングのように自動検知、自動リカバリできないかなと思い、クエリを作ってみました。

githubでも公開しています。

set transaction isolation level read uncommitted
set lock_timeout 1000
set nocount on

declare @CONST_profile_name varchar(30)
declare @CONST_recipients varchar(200)
declare @CONST_subject nvarchar(500) = @@SERVERNAME + N' : 多発したスロークエリのリコンパイル完了'
declare @msg nvarchar(max) = ''
declare @CrLf nvarchar(2)
SET @CrLf = nchar(13) + nchar(10)

SET @CONST_profile_name = '****'
SET @CONST_recipients = '****'

declare @recompile_threashold_cnt int = 10 --この数以上、同一クエリが実行中だったらリコンパイル
declare @recompile_threashold_sec int = 1 --何秒以上実行中のクエリを対象にするか
declare @sql_handle varbinary(64)
declare @text nvarchar(max)
declare @exec_cnt int

while (1=1)
begin
    set @msg = ''

    /*----------------------------------------
        リコンパイル
    ----------------------------------------*/
    declare cursor_sqlhandle CURSOR FAST_FORWARD FOR
        -- 一応同時にリコンパイルするクエリは最大10個までとしておく
        select top (10)
             sql_handle
            ,max(text) as text
            ,count(*) as cnt
        from
             sys.dm_exec_requests der
        join sys.dm_exec_sessions des on des.session_id = der.session_id
        outer apply sys.dm_exec_sql_text(sql_handle) as dest
        where
            des.is_user_process = 1
        and datediff(s, der.start_time, GETDATE()) >= @recompile_threashold_sec
        and sql_handle is not null
        group by
            sql_handle
        having
            count(*) >= @recompile_threashold_cnt

    open cursor_sqlhandle

    fetch next from cursor_sqlhandle into @sql_handle, @text, @exec_cnt

    while @@fetch_status = 0
    begin
        --select @text, @exec_cnt
        set @msg = @msg + '■ クエリ(最初の2000文字)' + @CrLf + substring(@text, 1, 2000) + @CrLf + '■ 同時実行数' + @CrLf + cast(@exec_cnt as nvarchar) + @CrLf + @CrLf
        --クエリのリコンパイル
        DBCC FREEPROCCACHE(@sql_handle) WITH NO_INFOMSGS

        fetch next from cursor_sqlhandle into @sql_handle, @text, @exec_cnt
    end

    close cursor_sqlhandle
    deallocate cursor_sqlhandle

    if @msg <> ''
    begin
        exec msdb.dbo.sp_send_dbmail @profile_name = @CONST_profile_name, @recipients = @CONST_recipients, @subject = @CONST_subject, @body = @msg
    end

    waitfor delay '00:00:10'
end

このクエリは、「同一のsql_handleをもつ、1秒以上実行中のクエリが5つ以上同時発生している場合に、該当sql_handleをリコンパイルする」というものです。

一応メール通知機能もいれているので、適宜プロファイル名等設定し、SQL Serverのジョブとして1分間間隔くらいで実行しておけば、実行プランが狂ったことにより突発的に遅くなったクエリを自動検知＋自動リコンパイル＋メール通知してくれます。

注意点としては、通常の環境下で1秒以上実行中のクエリが多数実行中の場合は、閾値を変更する必要があるかもしれません。
SQL Server 2008 R2 / 2012 / 2014 / 2016 をお使いの方で、突発的なスロークエリに悩まされている方は是非お試しください。