セージ の メモ書き

メモこそ命の恩人だ

SQL Server - クエリ処理時間の計測

準備

システム日時の取得

SYSDATETIME (Transact-SQL) - SQL Server | Microsoft Docs

  • 戻り値の型:datetime2(7)
  • GETDATE よりも精度がよい。
SELECT SYSDATETIME();
-- 2022-07-23 23:15:47.7438735

日時データの差分

DATEDIFF (Transact-SQL) - SQL Server | Microsoft Docs

DATEDIFF_BIG (Transact-SQL) - SQL Server | Microsoft Docs

  • 戻り値のサイズ
    • DATEDIFF:int (4byte)
    • DATEDIFF_BIG:bigint (8byte)
  • DATEDIFF のサイズ以上の値を扱う場合、DATEDIFF_BIG を使用する。
  • 使用できる単位(抜粋)
    • minute
    • second
    • millisecond など
DECLARE @Start datetime2 = SYSDATETIME()
WAITFOR DELAY '00:00:3'
DECLARE @End datetime2 = SYSDATETIME()

SELECT @Start, @End
-- 2022-07-23 23:17:41.7169148     2022-07-23 23:17:44.7278107

SELECT DATEDIFF(MINUTE, @Start, @End)
-- 0
SELECT DATEDIFF(SECOND, @Start, @End)
-- 3
SELECT DATEDIFF(MILLISECOND, @Start, @End)
-- 3011

キャッシュの削除

DBCC DROPCLEANBUFFERS (Transact-SQL) - SQL Server | Microsoft Docs

DBCC FREEPROCCACHE (Transact-SQL) - SQL Server | Microsoft Docs

-- データバッファのキャッシュをクリア
DBCC DROPCLEANBUFFERS;
-- クエリプランのキャッシュをクリア
DBCC FREEPROCCACHE;


処理時間の計測

ステートメントごとの処理時間

SET STATISTICS TIME (Transact-SQL) - SQL Server | Microsoft Docs

  • オンプレの場合、使用できる。
  • Azure SQL Database の場合、使用できない。(2022/8時点)
SET STATISTICS TIME ON;

-- 各ステートメントの時間を計測できる。
SELECT * FROM Sample1;
SELECT * FROM Sample2;
SELECT * FROM Sample3;

SET STATISTICS TIME OFF;

複数ステートメントの処理時間

-- キャッシュ削除
-- クエリ実行後、削除される仕様ではあるが、念のため2秒だけ待機しとく。
DBCC DROPCLEANBUFFERS;
DBCC FREEPROCCACHE;

WAITFOR DELAY '00:00:2';

-- 計測開始
DECLARE @StartTime datetime2 = SYSDATETIME();

-- 計測したい処理
-- ★★★ここに書く★★★

-- 計測終了
DECLARE @EndTime datetime2 = SYSDATETIME();
SELECT DATEDIFF(MILLISECOND, @StartTime, @EndTime);

複数ステートメントの処理時間(複数結果)

  • 注意
    • 計測処理に SELECT を入れると、キャッシュがたまる。試行回数が上がるたびに測定結果が遅くなる。。。
    • INSERT/UPDATE/DELETE などの更新系処理のみの場合にオススメ。
DECLARE @Table TABLE(Number INT, DiffTime INT);
DECLARE @Count INT = 1;
WHILE @Count <= 3 BEGIN

    -- キャッシュ削除
    -- クエリ実行後、削除される仕様ではあるが、念のため2秒だけ待機しとく。
    DBCC DROPCLEANBUFFERS;
    DBCC FREEPROCCACHE;

    WAITFOR DELAY '00:00:2';

    -- 計測開始
    DECLARE @StartTime datetime2 = SYSDATETIME();

    -- 計測したい処理
    -- ★★★ここに書く★★★

    -- 計測終了
    DECLARE @EndTime datetime2 = SYSDATETIME();
    INSERT INTO @Table (Number, DiffTime) VALUES (@Count, DATEDIFF(MILLISECOND, @StartTime, @EndTime));
    SET @Count += 1;
END

SELECT * FROM @Table;



以上