CPU とか、メモリを今より高スペックにしたと仮定して、クエリ性能がどう変化するか予測するのは私には無理なので、SQL Server さんに予測してもらってみる - 都内で働くSEの技術的なひとりごと / Technical soliloquy of System Engineer working in Tokyo

　従来の技術では、環境が変化することによって、状況がどのように変化するかの未来予測は難しいものでした。現在では、Azure Machine Learning などの機械学習を利用することで、過去のデータから、未来に起こり得るデータを予測することは比較的容易になってきています。自分の将来がどう変わるか、仕事を変えたらどうなるか....人生の予測までできたら....

人生を確実に予測するロジカル・ライフプランのすすめ

作者: 橋本顕愛
出版社/メーカー: 大和書房
発売日: 2013/09/21
メディア: 単行本（ソフトカバー）
この商品を含むブログを見る

※人生を確実に予測するか....

　ビックデータによる未来予測とは多少毛色は異なりますが、SQL Server においてもCPU、メモリなどのリソースなどの環境が変化した場合、どのようにパフォーマンスが変化するか知りたいものです。下図は、私がいつもブログを書いている Lenovo のスペックです。簡単な SQL Server の検証などにも使用しています。
f:id:koogucc11:20150901101308p:plain
f:id:koogucc11:20150901101317p:plain

　CPU、メモリなどのサーバーリソースを増強したらクエリはどうなるか？上図のマシンスペックを10倍にしたら、CPU：40コア、メモリ:80G です。もちろん、どのくらいのパフォーマンス、実行プランになるのか想像することは難しいです。このような事に対応するため、SQL Server では下記の undocumented なコマンドを用意しています。オプティマイザが使用する各種リソースを仮設定することが可能です。

DBCC OPTIMIZER_WHATIF

　上記のコマンドを使用し、リソースを仮に増強することで、クエリの実行プランがどのように変化するかを見てみましょう。

まず、念のためキャッシュプランをクリアします。
```
DBCC FREEPROCCACHE
```

下記の SQL を実行します。

SET STATISTICS PROFILE ON
SELECT
    oh.*,
    od.*
FROM 
    Sales.SalesOrderHeader oh
    INNER JOIN Sales.SalesOrderDetail od
    ON oh.SalesOrderID = od.SalesOrderID
ORDER BY
    oh.ModifiedDate
SET STATISTICS PROFILE OFF

f:id:koogucc11:20150901105339p:plain
f:id:koogucc11:20150901105349p:plain

OPTIMIZER_WHATIF のコマンドを有効化するために、下記のトレースフラグを ON にします。(グローバル設定にしないようにしてください。他の人にも影響を与えてしまいます。)
```
DBCC TRACEON (3604,0)
```

トレースの状態を確認します。3604 の Status が1になっているのが確認できます。
```
DBCC TRACESTATUS
```

My Lenovo のスペックの 10倍である、CPUコア数：40、メモリ：80G に設定します。
```
DBCC OPTIMIZER_WHATIF(CPUs, 40)
DBCC OPTIMIZER_WHATIF(MemoryMBs, 81920)
```

リソースのステータスを確認します。リソースが変更されているのが確認できます。
```
DBCC OPTIMIZER_WHATIF(Status)
```

下記の SQL を実行します。必ず OPTION (RECOMPILE) を付加してください。

SET STATISTICS PROFILE ON
SELECT
    oh.*,
    od.*
FROM 
    Sales.SalesOrderHeader oh
    INNER JOIN Sales.SalesOrderDetail od
    ON oh.SalesOrderID = od.SalesOrderID
ORDER BY
    oh.ModifiedDate
OPTION (RECOMPILE)
SET STATISTICS PROFILE OFF

f:id:koogucc11:20150901110220p:plain
f:id:koogucc11:20150901110257p:plain

上図の実行プランを見ると、並列実行に変化していることが判断できます。

|--Parallelism(Gather Streams, ORDER BY:([oh].[ModifiedDate] ASC))
        |--Nested Loops(Inner Join, OUTER REFERENCES:([oh].[SalesOrderID], [Expr1002]) WITH ORDERED PREFETCH)