none
SQL Server 2014 Management Studioでのコンパイル表示 RRS feed

  • 質問

  • お世話になります
    SQL Server 2014 Management Studioにて新しいクエリーより以下の構文を実行すると
    コンパイルされる時とそうでない時があり、もっと色々なテーブルと連結した長い
    SQL文を実行すると時間の違いが明らかに表示するまでにかかります。
    ストアドプロシージャにしてCREATE PROCEDURE句で作成しEXECで実行しましたが同じく
    初回は時間がかかり、直後の実行はスムーズに表示されます。
    タイミングでリコンパイルされないようにできないものでしょうか
    あまり変わらない時間で表示出来ないものなのでしょうか
    SQLSERVERでの設定等の変更又は他の方法で改善できればと思います、ご教授お願いします。
    --実行例------------------------------------------------------------------------------
    SET STATISTICS TIME ON
    GO
    SELECT *,t2.市町村名,t2.市町村名カナ FROM M_郵便 AS t1
    LEFT JOIN M_県市町村 AS t2 ON t2.都道府県番号=SUBSTRING(t1.市町村番号,1,2) AND t2.市町村番号=SUBSTRING(t1.市町村番号,3,3)
    ORDER BY t1.郵便番号
    --実行例 メッセージ-------------------------------------------------------------------
    ※1回目
    SQL Server の構文解析とコンパイルの時間:
     CPU 時間 = 0 ミリ秒、経過時間 = 3 ミリ秒。
    (118937 行処理されました)
     SQL Server 実行時間:
    、CPU 時間 = 453 ミリ秒、経過時間 = 2533 ミリ秒。
    ※2回目 5秒後実行
    SQL Server の構文解析とコンパイルの時間:
     CPU 時間 = 0 ミリ秒、経過時間 = 0 ミリ秒。
    (118937 行処理されました)
     SQL Server 実行時間:
    、CPU 時間 = 438 ミリ秒、経過時間 = 2523 ミリ秒。
    ※3回目 1分後実行
    SQL Server の構文解析とコンパイルの時間:
     CPU 時間 = 0 ミリ秒、経過時間 = 3 ミリ秒。
    (118937 行処理されました)
     SQL Server 実行時間:
    、CPU 時間 = 563 ミリ秒、経過時間 = 2582 ミリ秒。
    --色々なテーブルと連結した長いSQL文---------------------------------------------------
    ※1回目
    SQL Server の構文解析とコンパイルの時間:
     CPU 時間 = 33235 ミリ秒、経過時間 = 33451 ミリ秒。
    (38807 行処理されました)
     SQL Server 実行時間:
    、CPU 時間 = 5562 ミリ秒、経過時間 = 6640 ミリ秒。

    ※2回目 5秒後実行
    SQL Server の構文解析とコンパイルの時間:
     CPU 時間 = 0 ミリ秒、経過時間 = 0 ミリ秒。
    (38807 行処理されました)
     SQL Server 実行時間:
    、CPU 時間 = 5641 ミリ秒、経過時間 = 6466 ミリ秒。
    データベースやクエリーを変更してないのにも関わらずタイミングでコンパイルされてしまいます
    2回目の6秒ぐらいで何とか安定して表示できないものなのでしょうか
    2017年7月2日 23:44

回答

  • 直接の回答ではありませんが、リコンパイルがどの程度発生しているのか、次のSQLで確認してみて下さい。
    plan_generation_numがリコンパイルの回数を表します。

    SELECT
     t.text, q.plan_generation_num
    FROM
     sys.dm_exec_query_stats q
    CROSS APPLY
     sys.dm_exec_sql_text(qs.sql_handle) t


    ★良い回答には回答済みマークを付けよう! MVP - .NET  http://d.hatena.ne.jp/trapemiya/

    • 回答としてマーク ジョウジ 2017年7月4日 23:44
    2017年7月4日 2:11

すべての返信

  • 以前に使っていたSQL Server 2008Developerは表示が早かった気がして

    同文のストアドプロシージャを確認したところ、初回からSQL Server の構文解析とコンパイルの時間が

    0ミリ秒で同間隔に実行してもゼロ表示でした。

    データベースの設定等も確認しましたがバージョンの違いで増えたり変わってしまった箇所もありましたが

    同様の設定になっている感じがします、書籍ネット等でいろいろと調べましたが改善できません、大変でもご教授お願いします。

    2017年7月3日 10:39
  • 直接の回答ではありませんが、リコンパイルがどの程度発生しているのか、次のSQLで確認してみて下さい。
    plan_generation_numがリコンパイルの回数を表します。

    SELECT
     t.text, q.plan_generation_num
    FROM
     sys.dm_exec_query_stats q
    CROSS APPLY
     sys.dm_exec_sql_text(qs.sql_handle) t


    ★良い回答には回答済みマークを付けよう! MVP - .NET  http://d.hatena.ne.jp/trapemiya/

    • 回答としてマーク ジョウジ 2017年7月4日 23:44
    2017年7月4日 2:11
  • 返答ありがとうございます

    上記の文を実行してみましたが1回と結果表示されました、こんな便利な機能もあるんですね。

    SQL Server 2014と2008がインストールされているPCの環境等が悪いのかと思い、

    一日かけいろいろと見直ししてみました、その結果、原因は不明なのですが、PCの再起動時のみ、

    構文解析とコンパイルの時間が表示され、いろいろなタイミングで実行しても

    リコンパイルされなくなりました、しかし毎日PCを立ち上げ1回目は遅いという

    現象は改善されません、これもSQLの仕様なのでしょうか。

    2017年7月4日 23:44
  • コンパイルされたキャッシュはメモリ上に保存されていますから、PCを立ち上げ直したらキャッシュも無くなっています。
    それで初回は時間がかかるのだと思われます。
    ちなみにDBCC FREEPROCCACHE
    を実行すると再起動しなくてもキャッシュがクリアされますので、この直後は初回でなくても実行に時間がかかるようになるはずです。
    キャッシュされている状態は例えば以下のSQLで確認できます。

    SELECT c.UseCounts, c.Cacheobjtype, 
    DB_NAME(DB_ID()) AS データベース, t.TEXT AS SQL 
    FROM sys.dm_exec_cached_plans c
    CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
    WHERE objtype='Proc'


    ★良い回答には回答済みマークを付けよう! MVP - .NET  http://d.hatena.ne.jp/trapemiya/

    2017年7月5日 2:25