本日、SQL Server 2019 で Cumulative Update 6 (CU6) がリリースされました。
その中で興味深い改善として次の内容があります。
SQL Server 2019 CU6 から、クエリストアを無効 (OFF) にする際に、同期的なフラッシュを行うことなく、即時にクエリストアを無効化するようにできるようになりました。
How to Turn Off Query Store…in an emergency でも話題に挙げられていますね。
構文としては、次のクエリの実行が可能となるようです。
<br>ALTER DATABASE SET QUERY_STORE = OFF (FORCED) <br>
追記 :
SQL Server 2016 SP2 CU12 でもこの機能がサポートされたようです。
クエリストアのアーキテクチャについては、クエリ ストアでデータを収集する方法 で解説が行われています。
基本的な仕組みとしては、次の画像の内容となります。
クエリストアは、実行されたクエリの情報を「非同期」でディスクに書き込みを行うことで永続化を行っています。
ただし、一部の操作については同期的に実行されることがあり、その一つが「クエリストアの無効化」を行った場合です。
検証を行うために次のようなクエリを実行してみます。
(SELECT でアドホックなクエリを量産していますので、SSMS の「ツール」->「オプション」->「クエリ結果」->「SQL Server」->「結果をグリッドに表示」で「実行後に結果を破棄する」を有効化した方がよいかと)
USE [master]
GO
ALTER DATABASE [QueryStoreTEST] SET QUERY_STORE = ON
GO
ALTER DATABASE [QueryStoreTEST] SET QUERY_STORE (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = ALL)
GO
ALTER DATABASE [QueryStoreTEST] SET QUERY_STORE CLEAR
GO
CHECKPOINT
USE QueryStoreTEST
GO
SET NOCOUNT ON
DECLARE @baseSql nvarchar(max) = 'SELECT TOP 1 ”{0}”, * FROM sys.objects'
DECLARE @sql nvarchar(max)
DECLARE @cnt int = 1
RAISERROR('Query Start', 0,0) WITH NOWAIT
PRINT FORMAT(GETDATE(), 'yyyy/MM/dd hh:mm:ss.fff')
WHILE(@cnt <= 10000)
BEGIN
SET @sql = REPLACE(@baseSql, '{0}', CAST(NEWID() AS nvarchar(36)))
EXECUTE (@sql)
SET @cnt += 1
END
GO
RAISERROR('Query End', 0,0) WITH NOWAIT
PRINT FORMAT(GETDATE(), 'yyyy/MM/dd hh:mm:ss.fff')
WAITFOR DELAY '00:00:05′
RAISERROR('Query Store OFF', 0,0) WITH NOWAIT
PRINT FORMAT(GETDATE(), 'yyyy/MM/dd hh:mm:ss.fff')
DECLARE @currentTime datetime = (SELECT GETDATE())
ALTER DATABASE QueryStoreTEST SET QUERY_STORE = OFF - (FORCED)
PRINT DATEDIFF(MILLISECOND, @currentTime, GETDATE())
PRINT FORMAT(GETDATE(), 'yyyy/MM/dd hh:mm:ss.fff')
[/sourcecode]
</pre>
</div>
<p> </p>
<div id="scid:C89E2BDB-ADD3-4f7a-9810-1B7EACF446C1:c923a122-de4c-4060-807c-c92698ef468d" class="wlWriterEditableSmartContent" style="float: none; padding-bottom: 0px; padding-top: 0px; padding-left: 0px; margin: 0px; display: inline; padding-right: 0px"><pre style=white-space:normal>
[sourcecode language='sql' ]
上記のクエリでは「(FORCED)」をコメントアウトしていますので、クエリストアの無効化は従来までの動作となり、ALTER DATABASE でクエリストアを無効にするのに、6 秒程かかりました。 それでは「(FORCED)」を有効化して実行してみるとどうでしょうか。
Query Start 2020/08/05 11:28:02.087 Query End 2020/08/05 11:29:36.167 Query Store OFF 2020/08/05 11:29:41.170 403 2020/08/05 11:29:41.573
400ms で無効化が完了していますね。
従来までのクエリストアの無効化では、ALTER DATABASE を実行したタイミングで、ALTER DATABASE のトランザクション内で同期的にフラッシュされていないクエリストアの情報がフラッシュされ、ディスクに書き込みが行われていました。
この辺りの動作はトランザクションログから追うことができるのですが、今回のケースであれば次のようなクエリで情報を確認できます。
SELECT [Current LSN], Operation, Context, [Transaction Name], [Transaction ID],[Parent Transaction ID], [Begin Time], [End Time],Description FROM sys.fn_dblog(NULL, NULL) WHERE [Begin Time] IS NOT NULL or [End Time] IS NoT NUll
従来までのクエリストアの無効化については、
- CStmtAlterDB::ChangeStateOption のトランザクションを実行
- このトランザクションを親トランザクションとして、クエリストアのフラッシュの書き込みを行う
というような動作となっており、未フラッシュのクエリストアの情報が多いほど、ALTER DATABASE に時間がかかるというような動作になっていました。
(大きなロックは取っていなさそうなので、そこまで問題にはならなさそうな気はするのですが)
「FORCED」オプションを指定した場合、上記の「1.」の処理だけで、完了するトランザクションとなるようで、未フラッシュのデータが多くても、一定の時間で無効化を完了させることができるようです。
強制的な無効化ですので、クエリストアのデータのロストは発生しているのではと思いますが、瞬時に無効化できる方法が提供されたのはうれしいですね。