SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server 2019 CU6 でクエリストアの強制 OFF が可能になりました

without comments

本日、SQL Server 2019 で Cumulative Update 6 (CU6) がリリースされました。

その中で興味深い改善として次の内容があります。

SQL Server 2019 CU6 から、クエリストアを無効 (OFF) にする際に、同期的なフラッシュを行うことなく、即時にクエリストアを無効化するようにできるようになりました。

構文としては、次のクエリの実行が可能となるようです。

ALTER DATABASE <DB 名> SET QUERY_STORE = OFF (FORCED)

 

追記 :

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')
&#91;/sourcecode&#93;
</pre>
</div>

<p>&#160;</p>

<p>上記のクエリでは「(FORCED)」をコメントアウトしていますので、クエリストアの無効化は従来までの動作となります。</p>

<p>私の環境では、実行時に出力される結果は次のようになりました。</p>

<div id="scid:C89E2BDB-ADD3-4f7a-9810-1B7EACF446C1:c650814a-3247-4896-914b-189a4b87e3a2" 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>

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

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

従来までのクエリストアの無効化については、

  1. CStmtAlterDB::ChangeStateOption のトランザクションを実行
  2. このトランザクションを親トランザクションとして、クエリストアのフラッシュの書き込みを行う

というような動作となっており、未フラッシュのクエリストアの情報が多いほど、ALTER DATABASE に時間がかかるというような動作になっていました。

(大きなロックは取っていなさそうなので、そこまで問題にはならなさそうな気はするのですが)

「FORCED」オプションを指定した場合、上記の「1.」の処理だけで、完了するトランザクションとなるようで、未フラッシュのデータが多くても、一定の時間で無効化を完了させることができるようです。

強制的な無効化ですので、クエリストアのデータのロストは発生しているのではと思いますが、瞬時に無効化できる方法が提供されたのはうれしいですね。

Written by Masayuki.Ozawa

8月 5th, 2020 at 11:43 am

Posted in SQL Server

Tagged with ,

Leave a Reply