SQL Server 実行プランのスキーマを見比べてみる (2017 - 2019)

実行プランのスキーマを見る機会があったので、久々に blog を…。

ここから xsd をダウンロードできます。
Showplan Schema

単純に diff を取ると、2019 では、PDW や Spill、UDF、Page Server 等の情報が詳しく取れるようになってました。
サクッと検証出来る UDF で見てみましょう。

<xsd:complexType name="QueryExecTimeType">
    <xsd:annotation>
        <xsd:documentation>
            Shows time statistics for single query execution.
            CpuTime: CPU time in milliseconds
            ElapsedTime: elapsed time in milliseconds
            UdfCpuTime: Cpu time of UDF in milliseconds
            UdfElapsedTime: Elapsed time of UDF in milliseconds
        </xsd:documentation>
    </xsd:annotation>
    <xsd:attribute name="CpuTime" type="xsd:unsignedLong" use="required" />
    <xsd:attribute name="ElapsedTime" type="xsd:unsignedLong" use="required" />
    <xsd:attribute name="UdfCpuTime" type="xsd:unsignedLong" use="optional" />
    <xsd:attribute name="UdfElapsedTime" type="xsd:unsignedLong" use="optional" />
</xsd:complexType>

UDF (ユーザー定義関数) の CPU 時間と経過時間が追加されていました。
という訳で、次のような関数を作って、それぞれ実行プランを取ってみます。

create function dbo.SlowUdf()
returns int
as
begin
    declare @r int;
    with cte as (
      select 1 as seq 
      union all select seq + 1 from cte where seq < 100000
    )
    select top(1) @r = seq from cte 
    order by seq desc option (maxrecursion 0)
    return @r
end
go

create function dbo.FastUdf()
returns int
as
begin
    return 0
end
go

select dbo.SlowUdf()

の実行プランから該当部分を抜粋すると、

<QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="CouldNotGenerateValidParallelPlan" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="72">
  ...<省略>
  <QueryTimeStats CpuTime="530" ElapsedTime="721" UdfCpuTime="530" UdfElapsedTime="721" />    
  ...<省略>

select dbo.FastUdf()

の実行プランから該当部分を抜粋すると、

<QueryPlan DegreeOfParallelism="1" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="80" ContainsInlineScalarTsqlUdfs="true">
  ...<省略>
  <QueryTimeStats CpuTime="0" ElapsedTime="0" />      
  ...<省略>

SlowUdf は、UdfCpuTime、UdfElapsedTime が追加されています。
FastUdf は、UdfCpuTime、UdfElapsedTime が 0なのでしょう、省略されています。*1

同じ内容を 2017 で実行すると、 SlowUdf()

<QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="CouldNotGenerateValidParallelPlan" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="72">
  ...<省略>
  <QueryTimeStats CpuTime="505" ElapsedTime="697" />
  ...<省略>

FastUdf()

<QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="CouldNotGenerateValidParallelPlan" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="72">
  ...<省略>
  <QueryTimeStats CpuTime="0" ElapsedTime="0" />
  ...<省略>

UDF の情報はありませんね！

ちなみに 2016 => 2016sp1 では、クエリ中のWaitStats(待ち事象)が取れるようになってました。
知らないうちに色々増えているので確認しとかないとダメっすねー。

*1:use="optional"

メモ：DacFx の .NET 6 対応版が出てた

去年は preview じゃないとダメだったけど、
メモ：DacFx を .NET 6 で使用しようとすると、未だ preview 使わないとダメ - お だ のスペース
いつのまにか正式版出てた。

www.nuget.org

型を合わせとくとデータが多くなっても安心

この記事は meetup app osaka@6 - connpass の参加記事です。

試した環境：SQL Server 2019 15.0.2080.9

型を合わせてないと、起きる問題の1パターンを紹介します。
今回は、文字列型の char, varchar / nchar, nvarchar 間の話しです。

比較用のテーブルとデータは、同じ構造で 1万件、10万件、100万件、1000万件 と用意しました。

drop table if exists 受注テーブル_10000
drop table if exists 受注テーブル_100000
drop table if exists 受注テーブル_1000000
drop table if exists 受注テーブル_10000000

create table [受注テーブル_10000] (
  [Id] bigint not null primary key
  , [区分] char(1) not null
  , [伝票番号] varchar(10) not null
  , index [IX_10000_区分_伝票番号] ([区分], [伝票番号])
)
create table [受注テーブル_100000] (
  [Id] bigint not null primary key
  , [区分] char(1) not null
  , [伝票番号] varchar(10) not null
  , index [IX_100000_区分_伝票番号] ([区分], [伝票番号])
)
create table [受注テーブル_1000000] (
  [Id] bigint not null primary key
  , [区分] char(1) not null
  , [伝票番号] varchar(10) not null
  , index [IX_1000000_区分_伝票番号] ([区分], [伝票番号])
)
create table [受注テーブル_10000000] (
  [Id] bigint not null primary key
  , [区分] char(1) not null
  , [伝票番号] varchar(10) not null
  , index [IX_10000000_区分_伝票番号] ([区分], [伝票番号])
)
go

with [cte] as (select 1 as [cnt] union all select [cnt] + 1 from [cte] where [cnt] < 10000)
insert into [受注テーブル_10000] ([Id], [区分], [伝票番号])
select [cnt], cast(([cnt] % 4) as char(1)), concat('JU', format([cnt] / 4, N'D8')) from [cte] option (maxrecursion 0)
;

with [cte] as (select 1 as [cnt] union all select [cnt] + 1 from [cte] where [cnt] < 100000)
insert into [受注テーブル_100000] ([Id], [区分], [伝票番号])
select [cnt], cast(([cnt] % 4) as char(1)), concat('JU', format([cnt] / 4, N'D8')) from [cte] option (maxrecursion 0)
;

with [cte] as (select 1 as [cnt] union all select [cnt] + 1 from [cte] where [cnt] < 1000000)
insert into [受注テーブル_1000000] ([Id], [区分], [伝票番号])
select [cnt], cast(([cnt] % 4) as char(1)), concat('JU', format([cnt] / 4, N'D8')) from [cte] option (maxrecursion 0)
;

with [cte] as (select 1 as [cnt] union all select [cnt] + 1 from [cte] where [cnt] < 10000000)
insert into [受注テーブル_10000000] ([Id], [区分], [伝票番号])
select [cnt], cast(([cnt] % 4) as char(1)), concat('JU', format([cnt] / 4, N'D8')) from [cte] option (maxrecursion 0)
;

ちなみに再帰で1000万回やってるから非常に遅い。。*1

それぞれのテーブルで、[区分]、[伝票番号] に対して、1件に絞るクエリを varchar 型 と nvarchar 型 の2パターンの実行計画を見比べます。
取り合えず1万件のテーブルで実行計画を比較してみましょう。

nvarchar で渡してる方は何かややこしいことになってのが分かります。
Index Seek の詳細の詳細を見ると…

varchar

シーク述語を見ると、普通に [区分]、[伝票番号] に対して条件を比較している。

nvarchar

こっちは、述語を見ると、

CONVERT_IMPLICIT(nchar(1),[meetupapp].[dbo].[受注テーブル_10000].[区分],0)=[@1] AND 
CONVERT_IMPLICIT(nvarchar(10),[meetupapp].[dbo].[受注テーブル_10000].[伝票番号],0)=[@2]

とあり、型変換していることが分かる。
このままだと普通はインデックスが使われない(Seek ではなく、Scan になる)が、
SQL Server は頑張って、Seek になるようにしている。
※SQL Server 2012 では、Scan になっていた、2014 では 今と同じ動作。

シーク述語をみると、

開始: [meetupapp].[dbo].[受注テーブル_10000].[区分] > スカラー操作([Expr1005])
終了: [meetupapp].[dbo].[受注テーブル_10000].[区分] < スカラー操作([Expr1006])

となっており、Seek にはなっているが、[区分] の範囲比較になっている。
実行計画を詳しくみると、GetRangeThroughConvert という like 検索でも使われる文字の範囲を取得すると思われる内部関数が使われている。
が、ここでは気にしない。

で、[区分] しか Seek に使っていないので、実行時に不利になってそう。

では、set statistics io と set statistics time で件数別で実行時の IO と 時間を確認してみる。

1万件

logical read(論理読み取り数) が 2 => 24 になってるが、elapsed time(経過時間) は 0 ms のまま。

10万件

logical read(論理読み取り数) が 3 => 186 で、elapsed time(経過時間) は 0 ms => 8 ms

100万件

logical read(論理読み取り数) が 3 => 1811 で、elapsed time(経過時間) は 0 ms => 80 ms

1000万件

logical read(論理読み取り数) が 4 => 9059 で、elapsed time(経過時間) は 0 ms => 488 ms

件数が増えるにつれて、段々と遅くなっていくのが分かる。
インデックスを作って使ってるのに昔は速かったのに今は遅いというケースの1パターンに該当するのでは？
0.5秒って大したことないように見えてもこれが何万回も実行されるとちりも積もってで結構な差に…。

ちなみに、今回は複合インデックス(列が2個以上のインデックス)で比較したが、単一列のインデックスの場合は、
ここまで顕著に差は出ません。
※シーク述語 で、インデックスの最初の列を範囲検索しているので、単一列インデックスだと、その列しか無いため

今回は条件にリテラルで値を指定していますが、他にも

• テーブル間を join する時に、双方の列の型が違う
• プログラムから SQL を発行する際の変数の型を指定しなかったため、既定の動作で全部 nvarchar になった

等で同じことが起きます。

join するテーブルの列の型が違うのはそういうのの担当の人に相談するとして、
実装する人は、プログラムから SQL 呼び出すときに変数の型を手抜きしない、
クエリのリテラルに気を付けてると、何年か経ったときに遅くなって困ってるっていう相談を聞かずに済むかも？

*1:うちの環境で6分近く掛かった。

メモ：not in と not exists は等価じゃない

どっかで書いた気もするけど、null が混じったときに結果が違うよと。

select * from (values (1), (2), (3)) as a(id)
where a.id not in (select * from (values (null), (2)) as b(id))

結果無し

select * from (values (1), (2), (3)) as a(id)
where not exists (select * from (values (null), (2)) as b(id) where b.id = a.id)

1、3 がヒット。

メモ：互換性レベルは下位バージョンの動作検証には良くない

実働環境が古い SQL Server で手元には新しいのしかない時に、互換性レベル下げたらエラーになってくれるかなー？と思ってやってみたけどダメだった。
ALTER DATABASE 互換性レベル (Transact-SQL) - SQL Server | Microsoft Docs

互換性を 2014相当にしてるのに、Temporal Table 使えてる。困るなぁ。 pic.twitter.com/uCR8hxtQEJ

— oda shinsuke (@shinsukeoda) 2021年12月10日

ちゃんと古い環境用意しないとうっかり新しい機能、関数使っちゃうね…。

メモ：DacFx を .NET 6 で使用しようとすると、未だ preview 使わないとダメ

DacFx doesn't work on .NET 6 · Issue #33 · microsoft/DacFx · GitHub

NuGet Gallery | Microsoft.SqlServer.DacFx 160.5323.3-preview
より上のバージョン使ったらセーフ。

メモ：SELECT - INTO の指定先のテーブルの存在チェックはコンパイル時

1個のクエリ内で drop create で一時テーブル使い回ししたいなーと書いてたらエラーになったのでメモ。

こういうのがエラーになってクエリ自体実行されない。

drop table if exists #temptable
select [Name] into #temptable from sys.tables
select * from #temptable
drop table if exists #temptable

/* ↑で drop table してるのでクエリ実行時にエラー */
select * from #temptable
/*
error Msg 208, Level 16, State 0 
Invalid object name '#temptable'.
*/

/* ↑で drop table してても、こいつがあるとクエリの実行がされない！*/
select [Name] into #temptable from sys.tables
/*
error Msg 2714 Msg 2714, Level 16, State 1 
There is already an object named '#temptable' in the database.
*/

正解は2回目以降は truncate + insert select。

drop table if exists #temptable
select [Name] into #temptable from sys.tables
select * from #temptable

truncate table #temptable

select * from #temptable

insert into #temptable select [Name] into #temptable from sys.tables