none
パーテション分割したテーブルのデータ検索性能が悪化する。 RRS feed

  • 質問

  • お世話になっております。

    状況ですが、以下の継続結果です。

    ※なお、①、②ではともに事前にDBキャッシュをクリアしています。

    ①旧SQL Server 2008 R2

     プライマリキー列(年度)でのパーティション定義

    データ件数:2億件

    RAM:96GB  SQL Server Maxメモリ:80%に制限

    クエリで、2018年度のカウント取得-->役460万件  所要時間:10秒

    グループポリシで、メモリ内ページロック設定

    ***********************************

    *①==>②環境へ、DBバックアップ・復元で移行

    ***********************************

    ②新SQL Server 2017 (Azure Iaas基盤)

     プライマリキー列(年度)でのパーティション定義

    データ件数:2億件

    RAM:220GB  SQL Server Maxメモリ:80%に制限

    クエリで、2018年度のカウント取得-->役460万件  所要時間:2分30秒

    グループポリシで、メモリ内ページロック設定していない

       ==>この設定での差異は、これから確認

    ------------------------------------------------------------------------------

    ・①、②ともに実行プランの差異はない状況(Index Scan)

    ・メモリも大幅に増加している状況で、実行プランの差異なし

    ------------------------------------------------------------------------------

    ・なお、メモリ内ページロックでのクエリ性能への確認中

    心配なのが、標準のDBバックアップ・復元でDB移行した場合に、パーティション定義のあるテーブルの移行で、何か性能悪化にかかわるような注意事項とかあるんでしょうか?

    何か新環境では、もしかしてパーティションがうまく効いていない・・とかの状況でしょうか?

    すいません、何か情報ありましたら、ご教示ください。

    2019年1月17日 1:21

回答

  • akiyama1192さん、こんにちは。フォーラムオペレーターのHarukaです。
    ご返信頂きありがとうございます。

    実行プランが異なるため、SQL Server 2017 の実行プランの詳細を確認する必要があります。

    1.下記を実行してください。

    Set statistics profile on

      (query)

    Set statistics profile OFF

    2.列「Rows」と「EstimateRows」を比較します。
    この2つの列の値がまったく異なる場合、パフォーマンスの問題は、実行プランが間違っていることが原因である可能性があります。
    統計を更新して、再度パフォーマンスを確認してください。

    どうぞよろしくお願いします。


    MSDN/ TechNet Community Support Haruka

    ~参考になった投稿には「回答としてマーク」をご設定ください。なかった場合は「回答としてマークされていない」も設定できます。同じ問題で後から参照した方が、情報を見つけやすくなりますので、
    ご協力くださいますようお願いいたします。また、MSDNサポートに賛辞や苦情がある場合は、MSDNFSF@microsoft.comまでお気軽にお問い合わせください。~
    • 回答としてマーク akiyama1192 2019年2月20日 2:21
    2019年2月19日 4:49
    モデレータ

すべての返信

  • 補足します。

    ・新旧環境ともに、IDXは再構築した後の状況。

    ・データベースのデータファイル構成、TempDBのデータファイル構成は、まったく同じ状況で設定

    している状況です。

    ・同じ実行プラン(簡単なカウント取得クエリ:select count(*) ・・・)を確認したので、統計の更新

      及びデータベースの互換性レベル(旧100、新140)の差異はでていないと考えています。

    2019年1月17日 20:36
  • お騒がせ委します。

    結局、現新サーバーを一旦確認した結果ですが、

    ・現サーバーは24プロセッサ

      RAM:96GB  SQL Server Maxメモリ:0.8掛け

    ・新サーバはAzure Iaasですが、8論理プロセッサ

      RAM:220GB  SQL Server Maxメモリ:0.8掛け

    同じ実行プランでのselect count(*)クエリの実行性能の違いを確認すると、やはり現サーバでの24プロセッサの性能は断然上でした。

    コア数比較はしてないのですが、CPU性能差を評価しました。

    何かほかに確認すべきポイントありましたら、ぜひご教示ください。

    宜しくお願い致します。

    2019年1月21日 0:52
  • お世話になっています。

    ユーザーが以前に担当ベンダから話を受けた記憶あり。。とのことでしたので、一旦担当ベンダに確認してもらうことにしました。

    情報がありましたら、改めてご連絡させてください。

    当投稿は一旦完了にさせてください。

    ありがとうございました。

    2019年1月28日 21:24
  • akiyama1192さん、こんにちは。フォーラムオペレーターのHarukaです。
    MSDNフォーラムにご投稿くださいましてありがとうございます。

    この問題は、DBをSQL Server 2008 R2からSQL Server 2017に移行した後のパフォーマンスの変化についてです。

    要点は次のとおりです。
    1.メモリ内ページロック
    構成は実行プランとは関係ありません。 
    実行プランは、クエリ、スキーマ、および統計にのみ依存します。 
    新しいサーバーではメモリ内ページロック設定が行われていないため、これが1つの理由であると考えられます。 
    CXテストを設定することをお勧めします。

    2.パーティション
    SQL Server 2017はパーティションをサポートし、これによりパフォーマンスが向上します。 
    パフォーマンスの問題はパーティションとは関係ないはずです。

    3.パフォーマンス
    実行プラン:コマンドを使用:

       Set statistics profile on

        (query)

       Set statistics profile OFF


    2つのサーバーから実行された結果に違いがあるかどうかを確認するために、列を比較してください。 
    違いがなければ、実行プランは同じであると言えます。

    Running Time:コマンドを使用:

       Set statistics time on

       (query)

       Set statistics time off

    クエリにかかる時間が取得できます。 
    2つのサーバーから実行される結果の時間コストを比較します。
    どれが多くの時間にかかるのかをご確認ください。

    SQL Server実行時間:クエリを実際に実行するにかかる時間。
    SQL Serverによる解析時間とコンパイル時間:クエリコンパイルにかかる時間。
    CPU時間:このステップにかかるCPU時間。
    経過時間:このステップにかかる合計時間。

    SQL Serverの実行時間のほうが長いはずです。
    CPU時間と経過時間の両方をチェックする必要があります。 
    経過時間が長い場合、これはI/O またはブロックが原因である可能性があります。
    下記のコマンドを使用できます。

    select * from sys.dm_exec_requests

    CX クエリをもう一度実行し、待機時間中にコマンドを実行するための新しいクエリを新たに実行して、セッションを見つけ、待機タイプを確認します。

    どうぞよろしくお願いします。


    MSDN/ TechNet Community Support Haruka

    ~参考になった投稿には「回答としてマーク」をご設定ください。なかった場合は「回答としてマークされていない」も設定できます。同じ問題で後から参照した方が、情報を見つけやすくなりますので、
    ご協力くださいますようお願いいたします。また、MSDNサポートに賛辞や苦情がある場合は、MSDNFSF@microsoft.comまでお気軽にお問い合わせください。~
    2019年1月29日 8:45
    モデレータ
  • お世話になっております。

    重要な情報いただき、ありがとうございます。

    Azure基盤でいろいろとトラブル中ですが、ご教示いただいた点について、確認してみます。

    現時点での状況ですが、

    ①一旦、メモリ内ページロックは設定している状況です。

    ②夜間バッチとして、一部JP1からストアドを実行していて、中の一部で極端に性能劣化したクエリがあり、それがパーテション定義しているTBLとのことでした。現状ですが、HINT句を追加して、レベル100で稼働させると、sql server 2008 R2での実行プランが適用されるため、暫定対応しています。

    クエリストア機能での実行プランのクエリ強制についても、調査中です。

    ③別投稿で、0xc100012エラーについても確認させていただいていますが、例えば高負荷クエリ実行中にSSASデータベースの復元作業を行った処、同じ0xc100012エラーが発生したりしています。

    ④Iaas基盤で、1つの物理DISKにRDB領域やキューブ領域が集中している状況で、またDISKのIO性能も予算を抑えるために、落としているような状況です。

    たぶんなんですが、IO性能が追い付かずに、アペリケーション層でクエリ性能劣化と0xc100012エラー発生の頻度があがるんでは??と継続調査している状況です。

    ⑤また、同じ原因で高負荷クエリを実行させると、CPU・メモリは余裕ありとみえるんですが、DISKがいっぱいいっぱいで、他のアプリケーション操作等が反応が悪くなる(CPU100%みたいに、サーバーが極端に重くなる?? ように考えてます。 とりあえず、Azure基盤でのDISK IOをレベルUPする準備も始めました。


    2019年1月29日 19:21
  • akiyama1192さん、こんにちは。フォーラムオペレーターのHarukaです。

    本件につきまして、確認中だと思われますが、いまの状況はいかがでしょうか。

    何かのアップデートがありましたら、ご返信いただけますでしょうか。

    どうぞよろしくお願いします。


    MSDN/ TechNet Community Support Haruka

    ~参考になった投稿には「回答としてマーク」をご設定ください。なかった場合は「回答としてマークされていない」も設定できます。同じ問題で後から参照した方が、情報を見つけやすくなりますので、
    ご協力くださいますようお願いいたします。また、MSDNサポートに賛辞や苦情がある場合は、MSDNFSF@microsoft.comまでお気軽にお問い合わせください。~

    2019年2月12日 7:02
    モデレータ
  • しばらくバタバタしてまして、ご連絡遅れました。

    色々確認してたんですが、ご指摘の通リ、sqlserver2017でのパーテション機能には問題なしとの結果です。

    遅くなったクエリの実行プランを確認していたんですが、結果は以下のように判断しました。

    ・sqlserver2008R2 / sqlserver2017での、実行プラんを」比較した結果、何やらプランが異なっている。

       どうやら、2008R2でのプランが早い。

    ・sqlserver2016から、新しいアーキテクチャのオプティマイザが提供されているとのこと

    ・現在ですが、HINT句で対象レベル=100で、極端に遅くなったクエリの実行を暫定対応しています。

      ただ、恒久対応とはできないので、現在クエリストア機能でクエリ強制をかける準備(性能検証)を行っています。

    以上、最新の状況になります。

    2019年2月17日 0:39
  • akiyama1192さん、こんにちは。フォーラムオペレーターのHarukaです。
    ご返信頂きありがとうございます。

    実行プランが異なるため、SQL Server 2017 の実行プランの詳細を確認する必要があります。

    1.下記を実行してください。

    Set statistics profile on

      (query)

    Set statistics profile OFF

    2.列「Rows」と「EstimateRows」を比較します。
    この2つの列の値がまったく異なる場合、パフォーマンスの問題は、実行プランが間違っていることが原因である可能性があります。
    統計を更新して、再度パフォーマンスを確認してください。

    どうぞよろしくお願いします。


    MSDN/ TechNet Community Support Haruka

    ~参考になった投稿には「回答としてマーク」をご設定ください。なかった場合は「回答としてマークされていない」も設定できます。同じ問題で後から参照した方が、情報を見つけやすくなりますので、
    ご協力くださいますようお願いいたします。また、MSDNサポートに賛辞や苦情がある場合は、MSDNFSF@microsoft.comまでお気軽にお問い合わせください。~
    • 回答としてマーク akiyama1192 2019年2月20日 2:21
    2019年2月19日 4:49
    モデレータ