読者です 読者をやめる 読者になる 読者になる

都内で働くSEの技術的なひとりごと

都内でサラリーマンやってます。マイクロソフト系(たまに、OSS系などマイクロソフト以外の技術も...)の技術的なことについて書いています。日々の仕事の中で、気になったことを技術要素関係なく気まぐれに選んでいるので記事内容は開発言語、インフラ等ばらばらです。なお、当ブログで発信、発言は私個人のものであり、所属する組織、企業、団体等とは何のかかわりもございません。ブログの内容もきちんと検証して使用してください。よろしくお願いします♪

CPU とか、メモリを今より高スペックにしたと仮定して、クエリ性能がどう変化するか予測するのは私には無理なので、SQL Server さんに予測してもらってみる

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

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

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

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

 ビックデータによる未来予測とは多少毛色は異なりますが、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
    

    f:id:koogucc11:20150901105703p:plain

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

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

    f:id:koogucc11:20150901105953p:plain

  • 下記の 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)

  • 設定したリソースおよびトレースフラグを元に戻すには、下記のコマンドを実行します。
    DBCC OPTIMIZER_WHATIF(ResetAll)
    DBCC TRACEOFF(3604, 0)
    

 勿論、物理環境を完全再現できるものではありません。しかし、本番環境にリリースする前に、どのようにクエリが変化するか予測に使えるのではないでしょうか?

※キャンプ道具をさらに増強したくなってきた。これ欲しい♪