SQL Server ベースの環境で、プランキャッシュをクリアする場合、次のような構文を使用します。
- DBCC FREEPROCCACHE (Transact-SQL)
- DBCC FREESYSTEMCACHE (Transact-SQL)
- ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL)
SQL Database の場合は、DBCC ステートメントが使用できませんので、「ALTER DATABASE」でプランキャッシュのクリアが必要となります。
最近の更新で 「ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE」に、プランハンドルが指定できるようになりましたので、個別のプランのクリアも可能です。
ALTER DATABASE による、プランキャッシュのクリアですが、「データベースの CONTROL 権限」が必要となります。
CONTROL 権限 は、強めの権限となり、一般のユーザーに付与するには高すぎる権限です。
ALTER DATABASE CONFIGURATION だけで大丈夫でした orz
(CONTROL は上記の権限を付与するのに必要なだけでした)
プランキャッシュのクリアが必要なケースとしては
- プランのリコンパイルが発生し、実行プランが望ましくない場合に、再コンパイル対象としたい
ケースが考えられます。
このようなケースのプランキャッシュのクリアについては、管理者権限を持っていないユーザーでも実施したいケースがあるのではないでしょうか。
本投稿では、そのようなケースが発生した場合の実現方法をまとめてみたいかと。
一般ユーザーでプランキャッシュのクリア方法
CONNECT の権限だけが付与されているユーザーで、プランキャッシュをクリアしようとして、「ALTER DATABASE SCOPED CONFIGURATION CLEAR」を実行しても、次のようなエラーとなります。
メッセージ 15247、レベル 16、状態 13、行 3
User does not have permission to perform this action.
権限が不足しているため、実行できないですね…。
これを回避する方法の一つとしては「ストアドプロシージャで使用するコンテキストを指定し一時的に権限を委譲させる」方法があるかと。
ストアドプロシージャでは、作成する際に「EXECUTE AS」という構文を使用することができます。
デフォルトでは「CALLTER」となっており、ストアドプロシージャをコールしたユーザーの権限で実行されます。
これ以外に「SELF」「OWNER」「user_name」を指定することができ、実行時の権限を変更することができます。
今回は、dbo の権限のユーザーで接続をして、次のようなクエリにより、ストアドプロシージャを作成してみます。
CREATE OR ALTER PROCEDURE dbo.usp_Clear_Cache @plan_handle varbinary(64) WITH EXECUTE AS SELF AS BEGIN DECLARE @sql nvarchar(max) SET @sql = 'ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE ' + CONVERT(nvarchar(256), @plan_handle, 1) EXECUTE (@sql) END GO GRANT EXECUTE ON dbo.usp_Clear_Cache TO newUser GO
プランハンドルを指定して、ALTER DATABASE を実行し、作成したストアドプロシージャを、一般ユーザーに実行権限の付与をしています。
作成されたストアドプロシージャの設定を確認してみましょう。
select
object_name(object_id) as object_name,
OBJECTPROPERTY(object_id, 'OwnerId') as owner_id,
(select name from sys.database_principals where principal_id = OBJECTPROPERTY(object_id, 'OwnerId')) as owner_name,
execute_as_principal_id,
CASE execute_as_principal_id
WHEN -2 THEN 'OWNER'
ELSE ((select name from sys.database_principals where principal_id = execute_as_principal_id))
END as exec_principal
from sys.sql_modules where object_id = object_id('dbo.usp_Clear_Cache')
GO
今回は「EXECUTE AS SELF」を使用しているため、次のような設定となっています。
SELF の場合、ストアドプロシージャを作成 / 変更したユーザーのコンテキストで実行されることになります。
今回は dbo で作成していますので、それが情報が反映されていますね。
この方法で作成したストアドプロシージャ経由であれば、一般ユーザーでプランキャッシュをクリアさせることができます。
次のようなプランがキャッシュされている状態にしてみます。
このプランキャッシュを一般ユーザーでクリアしようとした場合、ALTER DATABASE を実行した場合は、権限の不足により、前述のエラーが発生します。
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE 0x06000900F8205C3BD0478530C701000001000000000000000000000000000000000000000000000000000000
しかし、実行時のコンテキストを変更したストアドプロシージャであれば、実行時にエラーは出ません。
EXEC dbo.usp_Clear_Cache @plan_handle = 0x06000900F8205C3BD0478530C701000001000000000000000000000000000000000000000000000000000000
実際にプランキャッシュからも指定したハンドルのクエリがクリアされていますね。
このような方法を使用することで、一般ユーザーの権限でも、プランキャッシュをクリアさせることができます。
一般ユーザーでセカンダリのプランキャッシュを消すためにはどうすればよいか？
SQL Database はアクティブセカンダリや Geo セカンダリというような読み取り専用サーバーを利用するための方法がいくつか提供されています。
読み取りのワークロードをセカンダリで実行している場合に、プランキャッシュをクリアするためにはどうすればよいでしょうか？
検証してみたところ、これを実現するのは難しそうでした…。
「ALTER DATABASE SCOPED CONFIGURATION」は、「FOR SECONDARY」というオプションを指定することで、セカンダリのみ構成を変更させ、設定変更の影響を見ることができます。
しかし「CLEAR PROCEDURE_CACHE」は、「FOR SECONDARY」には対応していないため、「プライマリからセカンダリのプランキャッシュをクリアする」ということは、残念ながらできません。
また、セカンダリで「ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE CACHE」を実行しようとしても次のエラーが発生します。
メッセージ 3906、レベル 16、状態 2、行 4
Failed to update database "tpch" because the database is read-only.
ALTER DATABASE はデータベース構成の変更とみなされ、セカンダリで直接実行することはできません。
セカンダリでプランキャッシュをクリアするというのは、2019/5 時点の実装では厳しそうですね…。
大体としては「sp_recompile」の利用の検討でしょうか。
プランハンドルが利用できる場合は、
- 実行中のクエリのプランハンドルを確認し、そのプランハンドルをキャッシュからクリアし、次回実行時のコンパイルさせる
という方法をとることができるのですが、sp_recompile の場合は、オブジェクトを指定することになります。
sp_recompile 'dbo.NATION'
この場合、プランキャッシュはクリアされませんが、該当のオブジェクトを使用するクエリをコンパイル対象とすることができます。
クエリをリコンパイルするという目的は達成できるのですが、プランハンドルによるキャッシュクリアと比較してリコンパイル対象となるクエリが多くなりますので、影響範囲は少し悩ましいですね。
実行中のプランに対して、実行時間が長くなった場合に、リコンパイル対象としたいという処理を実装しようとすると、「プランのテキストや実行プランを解析してオブジェクト名を抽出する」という方法をとる必要がありそうです。