SQL Server Row Goal の話し

2023/03/03 にあるはずの meetup app osaka@7 のネタ。

色々賢くなってるようなので、ちょっと確認。

データは、Release AdventureWorks sample databases · microsoft/sql-server-samples · GitHub の AdventureWorksDW2019 使ってます。
SQL Server のバージョンは、 2022 使ってますが、もうちょい古くてもこの機能入ってます。

Row Goal は、クエリ内に特定のキーワード(top, offset fetch, exists, etc...) のような全部見なくても良さげ？みたいなのが入った時に、良しなにやってくれそうな感じやつです。

同じクエリで片方は Row Goal を無効にした場合の実行プランを見比べます。

select top(25) * from [FactProductInventory]
;
select top(25) * from [FactProductInventory]
option (use hint ('disable_optimizer_rowgoal') )
;

実行プランをみると

上のプランは、25行読む予定で25行読んだで。
下のプランは、776286行読む予定で25行で終わったで。
みたいな感じです。
何もしてないシンプルなクエリだとまあ全部読まなくて良かったねで済みそうですね。
ここのポイントは、何行読む予定(見積行)の数によって、実行プランが変わる場合があるよってのを次の例で試してみます。

試すクエリはこんなやつです。

select top(100) * 
from 
  [FactInternetSales] inner join [DimCustomer] on
    [FactInternetSales].[CustomerKey] = [DimCustomer].[CustomerKey]
order by 
  [DimCustomer].[EmailAddress]
  , [DimCustomer].[CustomerKey]
  , [FactInternetSales].[SalesOrderNumber]
  , [FactInternetSales].[SalesOrderLineNumber]

テーブルくっ付けて、並び替えて先頭 100 件取る感じ。
where 条件は無しで、join の結合箇所も [DimCustomer] が PK というくらいのインデックスしかないです。
そうすると、 [FactInternetSales] と [DimCustomer] を スキャン して結合、ソートして先頭100件というようなプランになります。
これは top があっても、ソートしないとダメでインデックスでソート済みでもないので全部見ないとダメーって感じになり、Row Goal が有効でも良しなになりません。

set statistics io on

select top(100) * 
from 
  [FactInternetSales] inner join [DimCustomer] on
    [FactInternetSales].[CustomerKey] = [DimCustomer].[CustomerKey]
order by 
  [DimCustomer].[EmailAddress]
  , [DimCustomer].[CustomerKey]
  , [FactInternetSales].[SalesOrderNumber]
  , [FactInternetSales].[SalesOrderLineNumber]
option (use hint ('disable_optimizer_rowgoal') )

;
select top(100) * 
from 
  [FactInternetSales] inner join [DimCustomer] on
    [FactInternetSales].[CustomerKey] = [DimCustomer].[CustomerKey]
order by 
  [DimCustomer].[EmailAddress]
  , [DimCustomer].[CustomerKey]
  , [FactInternetSales].[SalesOrderNumber]
  , [FactInternetSales].[SalesOrderLineNumber]
;

set statistics io off

プランも一緒なので、IO 量も変わらず。

(100 rows affected)
テーブル 'Worktable'。スキャン数 0、論理読み取り数 0、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル 'FactInternetSales'。スキャン数 1、論理読み取り数 1249、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル 'DimCustomer'。スキャン数 1、論理読み取り数 984、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。

(1 row affected)

(100 rows affected)
テーブル 'Worktable'。スキャン数 0、論理読み取り数 0、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル 'FactInternetSales'。スキャン数 1、論理読み取り数 1249、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル 'DimCustomer'。スキャン数 1、論理読み取り数 984、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。

(1 row affected)

Completion time: 2023-02-25T13:23:48.6981772+09:00

ソートの先頭列は、[DimCustomer] テーブルを使ってるので、[DimCustomer] を読みながら [FactInternetSales] を繋いでみてくれたら Top が生きてきそうです。
ここで 1個インデックスを追加してみます。

create index [IX_FactInternetSales_CustomerKey] on [FactInternetSales] ([CustomerKey])

[FactInternetSales] で結合条件にしている [CustomerKey] にインデックスを作りました。
先ほどと同じクエリを実行すると、こんな感じのプランに変わります。

Row Goal 無効にしてるクエリは何も変わりませんが、無効にしてないクエリは、プランが変わりました。
[DimCustomer] を並び変えて順番に [FactInternetSales] を繋いでいって、100行になったら終わりみたいなプランですね。
IO 量をみると、

(100 rows affected)
テーブル 'Worktable'。スキャン数 0、論理読み取り数 0、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル 'FactInternetSales'。スキャン数 1、論理読み取り数 1249、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル 'DimCustomer'。スキャン数 1、論理読み取り数 984、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。

(1 row affected)

(100 rows affected)
テーブル 'FactInternetSales'。スキャン数 32、論理読み取り数 401、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル 'Worktable'。スキャン数 0、論理読み取り数 0、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル 'DimCustomer'。スキャン数 1、論理読み取り数 984、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。

(1 row affected)

Completion time: 2023-02-25T13:31:33.0897307+09:00

元のプランより、[FactInternetSales] の読み取り数が減っています。1249 => 401
これが Row Goal の効果で読む行減るんちゃうか？ってなってプランが変わって実際に読む量減ってやったーって感じです。

さらにインデックスを追加してみましょう。
今度は、[DimCustomer] の ソートに使用している列をインデックスにします。

create index [IX_DimCustomer_EmailAddress] on [DimCustomer] ([EmailAddress])

このインデックスを追加することで、さっきのプランで [DimCustomer] をスキャン(Clustered Index Scan)してソートしていたのが無くなり、このインデックスをスキャンだけに変わるはずです。
また同じクエリを実行してみましょう。

予想通りソート操作が無くなりました。*1
IO 量は

(100 rows affected)
テーブル 'Worktable'。スキャン数 0、論理読み取り数 0、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル 'FactInternetSales'。スキャン数 1、論理読み取り数 1249、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル 'DimCustomer'。スキャン数 1、論理読み取り数 984、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。

(1 row affected)

(100 rows affected)
テーブル 'FactInternetSales'。スキャン数 32、論理読み取り数 401、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル 'DimCustomer'。スキャン数 1、論理読み取り数 110、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。

(1 row affected)

Completion time: 2023-02-25T13:38:44.6290791+09:00

先ほどと比べると、[DimCustomer] の読み取り数が、984 => 110 と減りました。

気にせず恩恵受けてますが、賢くなっていってますねー。
ただ、何らかの原因で有効化した場合に遅いプランになる時があったら、その時は無効化してねとのことで。
SQL Server 2014、2016、2017に追加されたクエリ実行プランの KB4051361-オプティマイザー行の目標情報 - Microsoft サポート

*1:DimCustomer の Key Lookup は増えてますが

メモ：全DB、全ユーザーテーブルに対しての雑な sp_spaceused もどき

SQL Server で、クエリ1発だけで全てのデータベースの全てのユーザーテーブル に sp_spaceused の結果が欲しい + 取得日時とかDB名も欲しいなーなんて時のメモ。
雑なので、色々端折ってます。
ちゃんとしたの欲しい人は、クエリ1発諦めてカーソル + 動的SQL(sp_spaceused 呼んだ結果を一時テーブル入れてとかなんやら)とかでやるか、sp_spaceused のソース見てちゃんとやるように改造するかしたらいーんじゃないかな？

exec sp_MSforeachdb 'use [?];
select getdate() as [collect_date]
  , db_name() as [database_name]
  , object_name([object_id]) as [table_name]
  , sum([rows]) as [rows]
  , sum([reserved]) as [reserved_kb]
  , sum([pages]) as [data_kb]
  , sum([used_page]) - sum([pages]) as [index_size_kb]
  , sum([reserved]) - sum([used_page]) as [unused_kb]
from (
  select [object_id]
    , sum([reserved_page_count]) * 8 as [reserved]
    , sum([used_page_count]) * 8 as [used_page]
    , sum(case
          when ([index_id] < 2) then ([in_row_data_page_count] + [lob_used_page_count] + [row_overflow_used_page_count])
          else 0
        end
    ) * 8 as [pages]
    , sum(case
          when ([index_id] < 2) then [row_count]
          else 0
        end
    ) as [rows]
  from [sys].[dm_db_partition_stats]
  where [object_id] in (select [objects].[object_id] from [sys].[objects] where [type] = ''U'')
  group by [object_id]
  union
  select [it].[parent_id]
    , sum([p].[reserved_page_count])
    , sum([p].[used_page_count])
    , 0
    , 0
  from [sys].[dm_db_partition_stats] [p] inner join [sys].[internal_tables] [it]
    on [p].[object_id] = [it].[object_id]
  where [it].[parent_id] in (select [objects].[object_id] from [sys].[objects] where [type] = ''U'') 
    and [it].[internal_type] IN (202,204,207,211,212,213,214,215,216,221,222,236) 
  group by [it].[parent_id]
) [_target]
group by [object_id]
'

tempdb はじくとかてきとーにやってください。

Microsoft MVP じゃなくなりました

最近何もしてないのでまあそうだろうなぁと。

オンラインはあんまりやる気が出ないので、
オフラインでイベント開催しても良いかなーと思えるようになったら、
SQL World はぼちぼちやりたいなーとは思ってます。

メモ：ある列の値が上限を超えたら新しい行に分割するクエリ

SQL Server でのメモ

id	qty
1	47
2	14
3	112

を

id	qty
1	25
1	22
2	14
3	25
3	25
3	25
3	25
3	12

としたい。
サンプルのクエリはテーブル作るのメンドイので、テーブル値コンストラクターで。
テーブル値コンストラクター (Transact-SQL) - SQL Server | Microsoft Docs

with [cte] as (
  select 
    [id]
    , qty
    , case when [qty] > 25 then 25 else [qty] end as [splitted_qty]
    , 1 as [now]
    , [qty] / 25 + case when [qty] % 25 = 0 then 0 else 1 end as [split_count] 
  from (values (1, 47), (2, 14), (3, 112)) as b([id], [qty])
  union all
  select 
    [id]
    , qty
    , case when [now] + 1 < [split_count] then 25 else [qty] % 25 end as [splitted_qty]
    , [now] + 1 as [now]
    , [split_count]
  from cte
  where [now] + 1 <= [split_count]
)
select * from [cte] order by [id], [now]

再帰でやってるので上限超える場合は maxrecursion でよしなに。。
WITH common_table_expression (Transact-SQL) - SQL Server | Microsoft Docs

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分近く掛かった。