none
SQLサーバ2005(x64)でのクエリ処理が遅い RRS feed

  • 質問

  • SQLサーバ2000SP4(32ビット)で、10秒で処理されるクエリが、

    SQLサーバ2005SP2(64ビット)で、140秒かかります。

     

    ハード的には、SQLサーバ2005のほうが圧倒的に性能が良く、

    クエリ実行中のパフォーマンスモニタを見ると、1つのCPUの使用率が高い状態です。

    MAXDOPを1に変更してもやはり遅いままでした。

     

    ハード構成

    SQLサーバ2000

     CPU:Pentium4 2.4GHz

     DISK:IDE 1本(データもトランも同一パーティションに格納)

     メモリ:1GB

     

    SQLサーバ2005

     CPU:Xeon 7140M (2コア)×2個

     DISK:SAS RAID1:tempdb

         SCSI RAID10:データ

         SCSI RAID1:トラン

     メモリ:4GB

     

    推定実行プランを見ても、

    Clustered Index Seekの箇所にコストがかかっているようですし、

    ちゃんとインデックスを使用しているようなのですが、

    なぜそんなにCPUを使用するのかわかりません。

     

    何かご存知の方がいらっしゃいましたら、

    ご教授ください。

     

    よろしくお願いいたします。

     

    2008年7月9日 13:30

回答

  • こんにちは、naginoです。

     

    詳細な情報ありがとうございます。

    非クラスタ化インデックスの定義を拝見して、思い当たる節がありました。

    カバリングインデックス関連の動作がSQL Server 2000とSQL Server 2005で変更になっているのを思い出しました。

     

    クラスタ化インデックスがFIELD6に、非クラスタ化インデックスがFIELD1、FIELD7、FIELD9に対して作成されています。

    SQL Server 2000では、FIELD1を使用したJOINと、FIELD7を使用したWHERE句があるので、そこで非クラスタ化インデックスを

    使用し、FIELD8を使用したWHERE句があるのでBookmark Lookupを行っています。

    通常Bookmark Lookupは高速ではないので、Index SeekとStream Aggregateの組み合わせになるようにカバリングインデックスを

    作成したりするのが、クエリチューニングの手法の一つです。

     

    一方、SQL Server 2005では、非クラスタ化インデックスを使用しているのは同じですが、その後Bookmark Lookupをせずに

    クラスタ化インデックスをClustered Index SeekしてJoinしてからFIELD8を使用したWHERE句の条件でFilterしています。

    これは、Bookmark LookupがSQL Server 2005から使用されなくなったためです。

    http://msdn.microsoft.com/ja-jp/library/ms180920.aspx

    ですが、そのためにTBL1とTBL5のJOIN、そしてその結果に対してのJOINが発生し、行数の多いまま処理が進むために

    結果として遅くなっているようです。

     

    この変更は上記のURLにあるように仕様ですので、同じクエリ、同じスキーマ(構造)で実行プランが変化してしまったのも

    納得です。

     

    2005では、クエリとスキーマがこのままでは2000と同様の実行プランが作成されることはありませんので、

    クエリかスキーマを変更することになります。

     

    目立って速度が変化したクエリが今回問題となっているクエリのみであれば、Existsによるパフォーマンスチューニングが

    有効であることが分かっていますので、クエリのチューニングをお勧めします。

     

    他にも速度が変化したクエリがあるのであれば、それらのJOIN条件を調べて適切なカバリングインデックスを用意されることを

    お勧めします。

    ただし、SQL Server 2005からは付加列がありますので、インデックス追加によるディスク使用量や更新のオーバーヘッドが

    軽減されていますが0になっているわけではありませんので、これらが許容できない場合は1つ1つのクエリのチューニングを

    お勧めします。

    http://msdn.microsoft.com/ja-jp/library/ms190806.aspx

    http://msdn.microsoft.com/ja-jp/library/ms189607.aspx

     

    実行プランを提示いただいていながら、Bookmark Lookupをぼんやりと見過ごしていました。

    余計なお手数をおかけしてすみませんでした。

    一助になれば幸いです。

    2008年7月18日 15:40

すべての返信

  • こんにちは、naginoです。

     

    メモリ・CPU・ディスクの速度の点では「SQLサーバ2005」の方が高速という状況ですね。

    以下、実行プランに差異が無いという前提になります。

     

    いくつか可能性があるとは思いますが、可能性の高そうな事項を挙げてみます。

     

    -1.データ量に大きな差異がある。

    これは念のための確認です。

    「SQLサーバ2000」と「SQLサーバ2005」にある、今回処理対象のテーブルのデータは

    同程度でしょうか?

    データ量が大きく異なると、ディスクIOの量などが大きく変化するため、トータルの処理時間に対し、

    RAIDによる高速化以上に影響することがありえます。

     

    -2.インデックスに著しいフラグメンテーションが発生している。

    UPDATEや、DELETEとINSERTを繰り返していると、インデックスが断片化して、結果としてディスクIOの量が

    増大することがあります。

    「DBCC SHOWCONTIG」コマンドなどでスキャン密度などから調べることができます。

    SQL Server 2005 あれば、以下の方法もあるようです。

    http://msdn.microsoft.com/ja-jp/library/ms189858.aspx

    断片化が確認できる場合、バックアップ後に「SQLサーバ2005」の方でインデックスの再構築、あるいは再編成を

    行ってみてください。

     

    -3.統計が更新されていない。

    通常はデフォルトで自動更新になっているため、大量の更新処理を行った後などに限るのですが、

    統計情報が実際のデータと乖離していると、適切な実行プランを作成できないために処理速度が

    遅くなります。

    可能であれば、統計情報の更新を行ってみてください。

    http://msdn.microsoft.com/ja-jp/library/ms187348.aspx

     

    まずは上記のあたりから確認されてはいかがでしょうか。

    一助になれば幸いです。

     

    nagino

    MCITP(Database Developer)

    2008年7月10日 23:17
  • レスが遅れてすみません。

     

    -1.データ量に大きな差異がある。

    → データはまるで同じもので実行しています。

     

    -2.インデックスに著しいフラグメンテーションが発生している。

    → インデックスのメンテナンスをタスクで実行していますので、

       大丈夫かと思います。

     

    -3.統計が更新されていない。

    → Urlを参照して更新してみましたが、結果は変わりませんでした。

     

    実行プランなのですが、

    コストのかかっている箇所に違いがありました。

    まるで同じSQLを実行しても、プランが変わってしまうのは、

    作成されている統計情報に違いがある、ということになるのでしょうか。

    2008年7月16日 10:42
  • こんにちは、naginoです。

     

    ご調査ありがとうございます。

    ご回答いただいた内容を見ると、残念ながら前回列挙した事項が原因では無いようです。

     

    実行プランに差がある場合、データ量など諸条件が同じでも実行速度に差がでます。

    実行プランにどのような差があるか、からその原因を推測できるかもしれませんので、可能な範囲で

    実行プランをそれぞれ示していただけると何かわかるかもしれません。

    また、できましたら各SQL ServerのEditionもあわせて記載いただければと思います。

     

    ちなみに実行プランに差が出る原因は前述の要因だけとは限りません。

    前述以外の要因としては、以下でしょうか。

     

    -1.インデックスの差異

    インデックスの有無、クラスタ化・非クラスタ化の差異により発生します。

     

    -2.分散クエリか否か

    CPUのコア数と、分散クエリの有効無効の差異により発生します。

    ただ、はじめにMAXDOPを1に設定されているので、今回は該当しないと考えられます。

     

    -3.複雑なクエリで、オプティマイザの動作がバージョンにより異なる

    SQL Server 2005と2000では、多少オプティマイザの動作が異なるため、同一のクエリでも

    異なる実行プランを生成することがあります。

    ただし、SQL Server 2005の方が遅い実行プランを生成してしまうことは、滅多に無いと思われます。

     

    他にもパーティショニングなどさまざまな要因がありえるため、これでもまだ上記の中に原因があるとは限りません。

    このあたりは奥が深い箇所になるため、特定した回答ができず、すみません。

    一助になれば幸いです。

    2008年7月16日 11:47
  • こんばんは。

    ご返信いただきありがとうございます。

     

    SQLサーバの環境はそれぞれ

     ・SQLサーバ2000 StandardEdition SP4 (32bit)

     ・SQLサーバ2005 StandardEdition SP2 (64bit)

    となっています。

     

    元となるSQLは、

     

    SELECT     DISTINCT
               TBL5.FIELD1,
               TBL5.FIELD2
    FROM       TBL1 
    INNER JOIN TBL2
    ON         TBL1.FIELD1 = TBL2.FIELD1
    INNER JOIN TBL3
    ON    TBL1.FIELD1 = TBL3.FIELD1
    INNER JOIN TBL4
    ON         TBL2.FIELD2 = TBL4.FIELD2
    INNER JOIN TBL5
    ON         TBL4.FIELD1 = TBL5.FIELD1
    INNER JOIN TBL6
    ON         TBL1.FIELD1 = TBL6.FIELD1
    INNER JOIN TBL7
    ON         TBL6.FIELD2 = TBL7.FIELD2
    ORDER BY   TBL5.FIELD1

     

    のような文となっています。

    各JOINの項目にはIndexを作成してあります。

     

    実行プランは、

    SQLサーバ2000では、元となるTBL1のIndexSeekにコストが95%、

    その他のテーブルのJOINで、各テーブルのIndexSeekに1%ずつくらいとなっています。

     

    SQLサーバ2005では、

    TBL1のIndexSeekには1%、TBL2のIndexSeek90%、NestedLoopsで1~2%ずつとなっています。

     

    SQLサーバ2005でSQL文の各JOINをLEFT JOINに変更していったところ、

    TBL7のJOINを変更した際に、実行時間が一気に15秒ほどに縮まりました。

    その際の実行プランは、

    TBL1のIndexSeekには10%、TBL2のIndexSeek84%、NestedLoopsで1~2%ずつとなっています。

     

    TBL7にもINDEXは作成してあり、TBL7のデータ件数もさほど多くありません。

     

    テーブル件数

     TBL1:6,000,000

     TBL2:1,800

     TBL3:1,000

     TBL4:20

     TBL5:200

     TBL6:2,500

     TBL7:200

     

    なぜ、そんなにも実行時間に差が出るのか、

    よくわからなくなってしまいました。

    2008年7月16日 13:40
  • こんにちは、naginoです。

     

    詳細な情報ありがとうございます。

    手元の以下の環境で簡単な確認を取りましたが、目だった差異が見当たりませんでした。

     ・SQL Server 2000 StandardEdition SP4 (32bit)

     ・SQL Server 2005 StandardEdition SP1 (32bit)

    手元に64bit環境が無いため、64bit環境限定の原因だった場合、解決が難しいかもしれません。

     

    INNER JOINとLEFT JOINの場合、(結果の差異は別として)速度自体はLEFT JOINの方が早いことが多くあります。

     

    本ケースではTBL1の行数が非常に多いため、TBL1の結合される順番や、TBL1に対するSEEK/SCANの動作が

    処理速度を左右すると考えられます。

    ですが、動作が遅いほうの「SQLサーバ2005」において、TBL2のIndex Seekが処理速度のほとんどを占めている点が

    違和感があります。

    そこが今回の原因だと推測しています。

     

    以下可能な範囲で情報提供いただけますでしょうか。

     

    -1.環境差異が無いことの確認

    念のための確認ですが、一番最初の投稿にClustered Index Seekとありましたのでクラスタ化インデックスを使用されていると思われますが、

    各テーブルのFIELD1にクラスタ化インデックス、FIELD2に非クラスタ化インデックスが作成してあると考えて相違ありませんでしょうか。

    また、TBL2のインデックスや列の型に関して、2環境間で差異はありませんでしょうか。

     

    -2.データの格納状況の確認

    TBL2のページ使用状況について差異が無いか確認するため、2環境で以下の実行結果をご提示いただけますでしょうか。

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

    DBCC SHOWCONTIG (TBL2)
    --------------------------------------------------------

     

    -3.実行プランの詳細の確認

    もし可能ならばという前提ですが、実行プランをご提示いただけますでしょうか。

    実行プランはSET SHOWPLAN_ALLで文字ベースで出力できます。

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

    SET SHOWPLAN_ALL ON;
    GO

     

    /*実行プランを表示させたいクエリ*/;
    GO

     

    SET SHOWPLAN_ALL OFF;
    GO

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

     

    -4.蛇足

    蛇足ではありますが、次のクエリで実行計画や実行速度が変化するか確認いただけますでしょうか。

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

    SELECT DISTINCT
        TBL5.FIELD1,
        TBL5.FIELD2
    FROM TBL5
    WHERE EXISTS (
        SELECT *
        FROM TBL1
        INNER JOIN TBL2
            ON TBL1.FIELD1 = TBL2.FIELD1
        INNER JOIN TBL3
            ON TBL1.FIELD1 = TBL3.FIELD1
        INNER JOIN TBL4
            ON TBL2.FIELD2 = TBL4.FIELD2
        INNER JOIN TBL6
            ON TBL1.FIELD1 = TBL6.FIELD1
        INNER JOIN TBL7
            ON TBL6.FIELD2 = TBL7.FIELD2
        WHERE TBL4.FIELD1 = TBL5.FIELD1
        )
    ORDER BY TBL5.FIELD1

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

    環境によっては若干ですが動作速度が変化することが期待できます。

     

    解決までたどり着けるかわかりませんが、可能な限り原因追求に近づければと思いますので、よろしくお願いします。

    2008年7月17日 0:38
  • こんにちは、ヅダです。

     

    2点提示した情報に誤りがありました。

    詳細な情報をいただいているのに、混乱させるようなことをして申し訳ありません。

     

    ①SQL文について

       JOINがわかり易いように、簡易的に記載してしまいましたが、

     実際は、サブクエリを使用して、もう少し複雑です。

    ②実行プランの結果について

     SQLサーバ2005でのコストなのですが、

     簡易的なSQL文での実行プランを記載してしまいました。

     実際のSQLでは少し不可解(足したら100%を超えています)なプランとなっていました。

     

     

    実際のSQLは、

    SELECT DISTINCT TBL5.FIELD5, TBL5.FIELD10
    FROM        TBL1 
    INNER JOIN  TBL2
    ON   TBL1.FIELD1 = TBL2.FIELD1
    AND   TBL2.FIELD2 = (SELECT   MAX(FIELD2)
            FROM   TBL2
            WHERE  FIELD1 = TBL1.FIELD1
            AND    FIELD2 <= TBL1.FIELD7
            AND    FIELD3 = '1')
    AND   TBL2.FIELD3 = '1'
    INNER JOIN  TBL3
    ON   TBL1.FIELD1 = TBL3.FIELD1
    AND         TBL3.FIELD2 = (SELECT   MAX(FIELD2)
                                 FROM   TBL3
                                 WHERE  FIELD1 = TBL1.FIELD1
            AND    FIELD2 <= TBL1.FIELD7
            AND FIELD3 = '1')
    AND TBL3.FIELD3 = '1'
    INNER JOIN  TBL4
    ON   TBL2.FIELD4 = TBL4.FIELD4
    AND         TBL4.FIELD3 = '1'
    INNER JOIN  TBL5
    ON   TBL4.FIELD5 = TBL5.FIELD5
    AND         TBL5.FIELD3 = '1'
    INNER JOIN  TBL6
    ON   TBL1.FIELD1 = TBL6.FIELD1
    AND         TBL6.FIELD2 = (SELECT    MAX(FIELD2)
                                 FROM    TBL6
                                 WHERE   FIELD1 = TBL1.FIELD1
            AND     FIELD2 <= TBL1.FIELD7
            AND  FIELD3 = '1')
    AND   TBL6.FIELD3 = '1'
    INNER JOIN  TBL5 TBL5X
    ON   TBL6.FIELD5 = TBL5X.FIELD5
    AND         TBL5X.FIELD3 = '1'
    WHERE       TBL1.FIELD7 BETWEEN '20080601' AND '20080630'
    AND      TBL1.FIELD8 <> '000000000'
    AND   RTRIM(ISNULL(TBL1.FIELD8, '')) <> ''
    AND   TBL5.FIELD5 <> ''
    ORDER BY    TBL5.FIELD5

     

    となっています。

    実行プランは、

    TBL1:NestedLoops:キー参照:53%

    TBL1:NestedLoops:IndexSeek:1%

    TBL2:NestedLoops:ClusteredIndexSeek:19%

    TBL2:NestedLoops:ClusteredIndexSeek:32%

    TBL3:NestedLoops:ClusteredIndexSeek:16%

    TBL3:NestedLoops:ClusteredIndexSeek:27%

    TBL4:NestedLoops:ClusteredIndexScan:1%

    TBL6:NestedLoops:ClusteredIndexSeek:22%

    TBL6:NestedLoops:ClusteredIndexScan:1%

    ほかは0%でした。

     

    -1.環境差異が無いことの確認

     SQLサーバ2000と2005では、まったく同じテーブル・インデックスを使用しています。

     FIELD1は、TBL1では非クラスタ化インデックス・それ以外のテーブルでは、クラスタ化インデックスとなっています。

     

    -2.データの格納状況の確認(SQL2005)

    DBCC SHOWCONTIG により、'TBL4' テーブルがスキャンされています...
    テーブル: 'TBL4' (21575115)、インデックス ID: 1、データベース ID: 7
    TABLE レベルのスキャンが実行されました。
    - スキャンされたページ数.....................: 1
    - スキャンされたエクステント数...............: 1
    - エクステントの切り替え回数.................: 0
    - エクステントごとの平均ページ数.............: 1.0
    - スキャン密度 [最善値:実際値]...............: 100.00% [1:1]
    - 論理スキャンの断片化 ......................: 0.00%
    - エクステント スキャンの断片化 .............: 0.00%
    - ページごとの平均空きバイト数...............: 5464.0
    - 平均ページ密度 (全体) .....................: 32.49%
    DBCC SHOWCONTIG により、'TBL5' テーブルがスキャンされています...
    テーブル: 'TBL5' (53575229)、インデックス ID: 1、データベース ID: 7
    TABLE レベルのスキャンが実行されました。
    - スキャンされたページ数.....................: 2
    - スキャンされたエクステント数...............: 2
    - エクステントの切り替え回数.................: 1
    - エクステントごとの平均ページ数.............: 1.0
    - スキャン密度 [最善値:実際値]...............: 50.00% [1:2]
    - 論理スキャンの断片化 ......................: 0.00%
    - エクステント スキャンの断片化 .............: 50.00%
    - ページごとの平均空きバイト数...............: 2030.5
    - 平均ページ密度 (全体) .....................: 74.91%
    DBCC SHOWCONTIG により、'TBL1' テーブルがスキャンされています...
    テーブル: 'TBL1' (85575343)、インデックス ID: 1、データベース ID: 7
    TABLE レベルのスキャンが実行されました。
    - スキャンされたページ数.....................: 382651
    - スキャンされたエクステント数...............: 47833
    - エクステントの切り替え回数.................: 47832
    - エクステントごとの平均ページ数.............: 8.0
    - スキャン密度 [最善値:実際値]...............: 100.00% [47832:47833]
    - 論理スキャンの断片化 ......................: 0.01%
    - エクステント スキャンの断片化 .............: 0.22%
    - ページごとの平均空きバイト数...............: 22.0
    - 平均ページ密度 (全体) .....................: 99.73%
    DBCC SHOWCONTIG により、'TBL3' テーブルがスキャンされています...
    テーブル: 'TBL3' (2073058421)、インデックス ID: 1、データベース ID: 7
    TABLE レベルのスキャンが実行されました。
    - スキャンされたページ数.....................: 11
    - スキャンされたエクステント数...............: 2
    - エクステントの切り替え回数.................: 1
    - エクステントごとの平均ページ数.............: 5.5
    - スキャン密度 [最善値:実際値]...............: 100.00% [2:2]
    - 論理スキャンの断片化 ......................: 0.00%
    - エクステント スキャンの断片化 .............: 0.00%
    - ページごとの平均空きバイト数...............: 312.0
    - 平均ページ密度 (全体) .....................: 96.15%
    DBCC SHOWCONTIG により、'TBL2' テーブルがスキャンされています...
    テーブル: 'TBL2' (2105058535)、インデックス ID: 1、データベース ID: 7
    TABLE レベルのスキャンが実行されました。
    - スキャンされたページ数.....................: 59
    - スキャンされたエクステント数...............: 8
    - エクステントの切り替え回数.................: 7
    - エクステントごとの平均ページ数.............: 7.4
    - スキャン密度 [最善値:実際値]...............: 100.00% [8:8]
    - 論理スキャンの断片化 ......................: 0.00%
    - エクステント スキャンの断片化 .............: 0.00%
    - ページごとの平均空きバイト数...............: 204.4
    - 平均ページ密度 (全体) .....................: 97.47%
    DBCC SHOWCONTIG により、'TBL6' テーブルがスキャンされています...
    テーブル: 'TBL6' (2137058649)、インデックス ID: 1、データベース ID: 7
    TABLE レベルのスキャンが実行されました。
    - スキャンされたページ数.....................: 19
    - スキャンされたエクステント数...............: 8
    - エクステントの切り替え回数.................: 8
    - エクステントごとの平均ページ数.............: 2.4
    - スキャン密度 [最善値:実際値]...............: 33.33% [3:9]
    - 論理スキャンの断片化 ......................: 31.58%
    - エクステント スキャンの断片化 .............: 62.50%
    - ページごとの平均空きバイト数...............: 266.0
    - 平均ページ密度 (全体) .....................: 96.71%

     

    -3.実行プランの詳細の確認

    1行あたりの結果の表示が多くて、記載仕切れなさそうなのですが、

    記載したほうがいい項目がどれか、教えていただけると助かります。

    以下、StmtTextです。

     

      |--Sort(ORDER BY:([TESTDB].[dbo].[TBL5].[FIELD5] ASC))
           |--Nested Loops(Inner Join, OUTER REFERENCES:([TESTDB].[dbo].[TBL5].[FIELD5]))
                |--Clustered Index Seek(OBJECT:([TESTDB].[dbo].[TBL5].[PK_TBL5]), SEEK:([TESTDB].[dbo].[TBL5].[FIELD5] < '' OR [TESTDB].[dbo].[TBL5].[FIELD5] > ''),  WHERE:([TESTDB].[dbo].[TBL5].[FIELD3]='1') ORDERED FORWARD)
                |--Top(TOP EXPRESSION:((1)))
                     |--Nested Loops(Inner Join, WHERE:([TESTDB].[dbo].[TBL2].[FIELD4]=[TESTDB].[dbo].[TBL4].[FIELD4]))
                          |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1017], [TESTDB].[dbo].[TBL6].[FIELD1]))
                          |    |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1009], [TESTDB].[dbo].[TBL6].[FIELD1], [Expr1043]) WITH UNORDERED PREFETCH)
                          |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([TESTDB].[dbo].[TBL1].[FIELD7], [TESTDB].[dbo].[TBL1].[FIELD1], [TESTDB].[dbo].[TBL6].[FIELD2], [Expr1042]) WITH UNORDERED PREFETCH)
                          |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([TESTDB].[dbo].[TBL1].[FIELD7], [TESTDB].[dbo].[TBL1].[FIELD1]))
                          |    |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([TESTDB].[dbo].[TBL1].[FIELD7], [TESTDB].[dbo].[TBL1].[FIELD1], [Expr1041]) WITH UNORDERED PREFETCH)
                          |    |    |    |    |    |--Filter(WHERE:(rtrim(isnull([TESTDB].[dbo].[TBL1].[FIELD8],'         '))<>'' AND [TESTDB].[dbo].[TBL1].[FIELD8]<>'000000000'))
                          |    |    |    |    |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([TESTDB].[dbo].[TBL1].[FIELD6], [Expr1040]) OPTIMIZED WITH UNORDERED PREFETCH)
                          |    |    |    |    |    |         |--Nested Loops(Inner Join, OUTER REFERENCES:([TESTDB].[dbo].[TBL6].[FIELD1], [Expr1039]) WITH UNORDERED PREFETCH)
                          |    |    |    |    |    |         |    |--Nested Loops(Inner Join, WHERE:([TESTDB].[dbo].[TBL6].[FIELD5]=[TESTDB].[dbo].[TBL5].[FIELD5] as [TBL5X].[FIELD5]))
                          |    |    |    |    |    |         |    |    |--Clustered Index Scan(OBJECT:([TESTDB].[dbo].[TBL6].[PK_TBL6]),  WHERE:([TESTDB].[dbo].[TBL6].[FIELD3]='1') ORDERED FORWARD)
                          |    |    |    |    |    |         |    |    |--Table Spool
                          |    |    |    |    |    |         |    |         |--Clustered Index Scan(OBJECT:([TESTDB].[dbo].[TBL5].[PK_TBL5] AS [TBL5X]), WHERE:([TESTDB].[dbo].[TBL5].[FIELD3] as [TBL5X].[FIELD3]='1'))
                          |    |    |    |    |    |         |    |--Index Seek(OBJECT:([TESTDB].[dbo].[TBL1].[IDX1_TBL1]), SEEK:([TESTDB].[dbo].[TBL1].[FIELD1]=[TESTDB].[dbo].[TBL6].[FIELD1] AND [TESTDB].[dbo].[TBL1].[FIELD7] >= '20080601' AND [TESTDB].[dbo].[TBL1].[FIELD7] <= '20080630') ORDERED FORWARD)
                          |    |    |    |    |    |         |--Clustered Index Seek(OBJECT:([TESTDB].[dbo].[TBL1].[PK_TBL1]), SEEK:([TESTDB].[dbo].[TBL1].[FIELD6]=[TESTDB].[dbo].[TBL1].[FIELD6]) LOOKUP ORDERED FORWARD)
                          |    |    |    |    |    |--Stream Aggregate(DEFINE:([Expr1009]=MAX([TESTDB].[dbo].[TBL2].[FIELD2])))
                          |    |    |    |    |         |--Clustered Index Seek(OBJECT:([TESTDB].[dbo].[TBL2].[PK_TBL2]), SEEK:([TESTDB].[dbo].[TBL2].[FIELD1]=[TESTDB].[dbo].[TBL1].[FIELD1] AND [TESTDB].[dbo].[TBL2].[FIELD2] <= [TESTDB].[dbo].[TBL1].[FIELD7]),  WHERE:([TESTDB].[dbo].[TBL2].[FIELD3]='1') ORDERED FORWARD)
                          |    |    |    |    |--Stream Aggregate(DEFINE:([Expr1017]=MAX([TESTDB].[dbo].[TBL3].[FIELD2])))
                          |    |    |    |         |--Clustered Index Seek(OBJECT:([TESTDB].[dbo].[TBL3].[PK_TBL3]), SEEK:([TESTDB].[dbo].[TBL3].[FIELD1]=[TESTDB].[dbo].[TBL1].[FIELD1] AND [TESTDB].[dbo].[TBL3].[FIELD2] <= [TESTDB].[dbo].[TBL1].[FIELD7]),  WHERE:([TESTDB].[dbo].[TBL3].[FIELD3]='1') ORDERED FORWARD)
                          |    |    |    |--Filter(WHERE:([TESTDB].[dbo].[TBL6].[FIELD2]=[Expr1031]))
                          |    |    |         |--Stream Aggregate(DEFINE:([Expr1031]=MAX([TESTDB].[dbo].[TBL6].[FIELD2])))
                          |    |    |              |--Clustered Index Seek(OBJECT:([TESTDB].[dbo].[TBL6].[PK_TBL6]), SEEK:([TESTDB].[dbo].[TBL6].[FIELD1]=[TESTDB].[dbo].[TBL1].[FIELD1] AND [TESTDB].[dbo].[TBL6].[FIELD2] <= [TESTDB].[dbo].[TBL1].[FIELD7]),  WHERE:([TESTDB].[dbo].[TBL6].[FIELD3]='1') ORDERED FORWARD)
                          |    |    |--Clustered Index Seek(OBJECT:([TESTDB].[dbo].[TBL2].[PK_TBL2]), SEEK:([TESTDB].[dbo].[TBL2].[FIELD1]=[TESTDB].[dbo].[TBL6].[FIELD1] AND [TESTDB].[dbo].[TBL2].[FIELD2]=[Expr1009]),  WHERE:([TESTDB].[dbo].[TBL2].[FIELD3]='1') ORDERED FORWARD)
                          |    |--Clustered Index Seek(OBJECT:([TESTDB].[dbo].[TBL3].[PK_TBL3]), SEEK:([TESTDB].[dbo].[TBL3].[FIELD1]=[TESTDB].[dbo].[TBL6].[FIELD1] AND [TESTDB].[dbo].[TBL3].[FIELD2]=[Expr1017]),  WHERE:([TESTDB].[dbo].[TBL3].[FIELD3]='1') ORDERED FORWARD)
                          |--Clustered Index Scan(OBJECT:([TESTDB].[dbo].[TBL4].[PK_TBL4]), WHERE:([TESTDB].[dbo].[TBL4].[FIELD5]=[TESTDB].[dbo].[TBL5].[FIELD5] AND [TESTDB].[dbo].[TBL4].[FIELD3]='1'))

     

    -4.蛇足

    EXISTS句を使用したところ、実行時間が18秒程度に短くなりました。

     

    SQLサーバ2005 StandardEdition 32bitでも、

    同様に遅い現象を確認いたしました。

     

    よろしくお願いします。

     

    2008年7月17日 3:29
  • こんにちは、naginoです。

     

    追加の情報ありがとうございます。

    検討項目が増えてきましたので、一部引用で記載します。

     

    これまでいただいた情報を鑑みるに、処理時間が遅い原因のうち、良くあるものについてはおおよそ該当しない状況のようです。

    実行プランの差から一つ一つ確認していかざるを得ないと思われます。

     

    実際のクエリのご提示、ありがとうございます。

    ざっと見た限りでは、実行プランがSeekになっていることからも、問題なさそうだという第一感です。

    TBL4のみScanになっているとのことですが、この場合はデータ件数が非常に少ないため、問題ありません。

     

    > 実際のSQLでは少し不可解(足したら100%を超えています)なプランとなっていました。

    こちらは、実行プランをグラフィカルに表示し、各アイコンの下に表示されている%表示を足し合わせたら、ということでしょうか?

    その場合、端数を(おそらく)四捨五入などして表示しているため、合計しても100%にならないことがありえます。

     

    > SQLサーバ2000と2005では、まったく同じテーブル・インデックスを使用しています。

    以前記載いただいた内容からデータは2環境間で同一で、統計情報も最新に更新いただいていることとあわせて考えますと、

    実行プランに差が出る原因が考えにくい状況です。

     

    > -2.データの格納状況の確認(SQL2005)

    情報ありがとうございます。

    特にTBL1の「スキャン密度」を確認すると、十分な高密度となっており、以前のインデックスの再構築・再編成とあわせて考えると、

    インデックスやデータのフラグメンテーションが原因では無いと考えて良いと思います。

     

    > -3.実行プランの詳細の確認

    StmtTextの情報ありがとうございます。

    こちらは「SQL Server 2005」側の情報で相違ありませんでしょうか?

    また、追加で以下の列の情報を、StmtTextを提示いただいたときと同じ行の並びのままで、いただけますでしょうか?

    実行プランの差からアタリをつける必要がありますので、可能ならば両方の環境についてお願いします。

    (SQL Server 2000側については、StmtTextも加えてお願いします。)

    NodeId、Parent、PhysicalOp、LogicalOp、EstimateRows、EstimateIO、EstimateCPU、AvgRowSize、TotalSubtreeCost、EstimateExecutions

    全てを記載するのが難しい場合は、とりあえず以下の情報について記載いただけますでしょうか?

     ・2環境両方について、各テーブルの結合が行われる順番

     ・2環境間で、上記列のうち特に差異が大きい箇所


    > EXISTS句を使用したところ、実行時間が18秒程度に短くなりました。

    こちらは、「SQL Server 2005」側で「実行時間が18秒」ということでしょうか?

    であれば、もともと140秒程度ということですので、ちょっと信じがたいほどの高速化です。

    18秒程度短縮して120秒強、ということであれば、思いのほか大きい効果ではありますが、妥当な範囲内という印象です。

     

    > SQLサーバ2005 StandardEdition 32bitでも同様に遅い現象を確認いたしました。

    ご確認ありがとうございます。

    64bit環境限定ではないということですので、特殊ではない原因がありそうです。

     

    お手数をおかけしますが、よろしくお願いします。

    2008年7月17日 4:48
  • こんにちは、ヅダです。

     

    いろいろ説明不足な箇所があるにもかかわらず、

    対応・指摘いただきとても感謝しております。

     

     

    > こちらは、実行プランをグラフィカルに表示し、各アイコンの下に表示されている%表示を足し合わせたら、ということでしょうか?

    > その場合、端数を(おそらく)四捨五入などして表示しているため、合計しても100%にならないことがありえます。

    はい、グラフィカルに表示した%の集計です。

    端数で合わないことがあるのは承知していたのですが、

    どうもそういうレベルではないようです。

    全部足すと、172%になってしまいました。

    (1つ前の投稿の、実行プランの%がそうです)

     

    >> -3.実行プランの詳細の確認

    > こちらは「SQL Server 2005」側の情報で相違ありませんでしょうか?

    はい、「SQL Server 2005」側の情報となります。

     

    >> EXISTS句を使用したところ、実行時間が18秒程度に短くなりました。

    > こちらは、「SQL Server 2005」側で「実行時間が18秒」ということでしょうか?

    はい、「SQL Server 2005」側で「実行時間が18秒」となりました。

    もともと、「SQL Server 2000」側で、「実行時間が10秒」だったので、

    だいぶ近い数字となりました。

    (ハード性能を比べると、もっと早くてもよさそうですが・・・)

     

    「SQL Server 2005」側で、遅いクエリを実行時にパフォーマンスモニタを見ていると、

    クエリ実行時間のほとんどは、CPUが使用しており、

    DISKへのアクセス時間はほとんどありませんでした。

     

    文字数が足りないようなので、

    実行プランは、別に投稿します。

     

    2008年7月17日 5:52
  • SQL Server 2005

    NodeId Parent PhysicalOp LogicalOp
    2 1 Sort Sort
    4 2 Nested Loops Inner Join
    5 4 Clustered Index Seek Clustered Index Seek
    7 4 Top Top
    8 7 Nested Loops Inner Join
    9 8 Nested Loops Inner Join
    10 9 Nested Loops Inner Join
    12 10 Nested Loops Inner Join
    14 12 Nested Loops Inner Join
    15 14 Nested Loops Inner Join
    18 15 Filter Filter
    19 18 Nested Loops Inner Join
    22 19 Nested Loops Inner Join
    24 22 Nested Loops Inner Join
    25 24 Clustered Index Scan Clustered Index Scan
    26 24 Table Spool Lazy Spool
    27 26 Clustered Index Scan Clustered Index Scan
    29 22 Index Seek Index Seek
    31 19 Clustered Index Seek Clustered Index Seek
    37 15 Stream Aggregate Aggregate
    38 37 Clustered Index Seek Clustered Index Seek
    42 14 Stream Aggregate Aggregate
    43 42 Clustered Index Seek Clustered Index Seek
    47 12 Filter Filter
    48 47 Stream Aggregate Aggregate
    49 48 Clustered Index Seek Clustered Index Seek
    54 10 Clustered Index Seek Clustered Index Seek
    55 9 Clustered Index Seek Clustered Index Seek
    56 8 Clustered Index Scan Clustered Index Scan

    2008年7月17日 6:04
  •  

    EstimateRows EstimateIO EstimateCPU AvgRowSize
    31 0.01126126 0.000339609 24
    12.79052 0 0.00066462 24
    159 0.003865741 0.0003814 25
    1 0 1.00E-07 9
    1 0 0.6819535 9
    14.70558 0 0.6956069 9
    15 0 0.6956069 22
    15 0 1.149418 28
    24.78593 0 1.149418 49
    24.78593 0 1.149418 41
    24.78593 0 0.3244904 33
    24.78697 0 1.149466 42
    24.78697 0 1.044668 43
    1 0 1.70475 20
    1.643559 0.01645833 0.0029785 24
    159 0.01 0.00012872 10
    159 0.003865741 0.0003814 11
    22.52711 0.003125 0.000333154 30
    1 0.003125 0.0001581 16
    1 0 1.10E-06 15
    1 0.003125 0.0001581 16
    1 0 1.10E-06 15
    1 0.003125 0.0001581 16
    1 0 4.80E-07 9
    1 0 1.10E-06 15
    1 0.003125 0.0001581 16
    1 0.003125 0.0001581 10
    1 0.003125 0.0001581 9
    1 0.0032035 9.94E-05 13
    2008年7月17日 6:35
  • TotalSubtreeCost EstimateExecutions
    4.849513 1
    4.837912 1
    0.004247141 1
    4.832886 159
    4.83287 159
    4.731151 159
    4.297799 159
    3.784361 159
    3.071604 159
    2.370392 159
    1.539501 159
    1.53485 159
    0.1704829 159
    0.09001872 159
    0.02837881 159
    0.0480436 262
    0.004247141 1
    0.03239155 161
    2.584361 7865.972
    1.55751 7858.772
    1.545094 7858.772
    1.309651 7858.772
    1.297234 7858.772
    1.070949 6316.024
    1.067918 6316.024
    1.057938 6316.024
    0.9363595 4756.385
    0.7809036 4721.876
    0.0521671 2339

     

    2008年7月17日 6:35
  • SQL Server 2000

     

      |--Sort(DISTINCT ORDER BY:([TBL5].[FIELD5] ASC))
           |--Hash Match(Inner Join, HASH:([TBL5X].[FIELD5])=([TBL6].[FIELD5]), RESIDUAL:([TBL6].[FIELD5]=[TBL5X].[FIELD5]))
                |--Clustered Index Scan(OBJECT:([TESTDB].[dbo].[TBL5].[PK_TBL5] AS [TBL5X]), WHERE:([TBL5X].[FIELD3]='1'))
                |--Merge Join(Inner Join, MERGE:([TBL6].[FIELD1], [TBL6].[FIELD2])=([TBL3].[FIELD1], [Expr1023]), RESIDUAL:([TBL6].[FIELD2]=[Expr1023] AND [TBL6].[FIELD1]=[TBL3].[FIELD1]))
                     |--Clustered Index Scan(OBJECT:([TESTDB].[dbo].[TBL6].[PK_TBL6]),  WHERE:([TBL6].[FIELD3]='1') ORDERED FORWARD)
                     |--Sort(ORDER BY:([TBL1].[FIELD1] ASC, [Expr1023] ASC))
                          |--Nested Loops(Inner Join, OUTER REFERENCES:([TBL2].[FIELD2], [TBL3].[FIELD2], [TBL3].[FIELD1]))
                               |--Hash Match(Inner Join, HASH:([TBL5].[FIELD5])=([TBL4].[FIELD5]), RESIDUAL:([TBL4].[FIELD5]=[TBL5].[FIELD5]))
                               |    |--Clustered Index Seek(OBJECT:([TESTDB].[dbo].[TBL5].[PK_TBL5]), SEEK:([TBL5].[FIELD5] < '' OR [TBL5].[FIELD5] > ''),  WHERE:([TBL5].[FIELD3]='1') ORDERED FORWARD)
                               |    |--Hash Match(Inner Join, HASH:([TBL4].[FIELD4])=([TBL2].[FIELD4]), RESIDUAL:([TBL2].[FIELD4]=[TBL4].[FIELD4]))
                               |         |--Clustered Index Scan(OBJECT:([TESTDB].[dbo].[TBL4].[PK_TBL4]), WHERE:([TBL4].[FIELD3]='1'))
                               |         |--Nested Loops(Inner Join, OUTER REFERENCES:([TBL3].[FIELD1]) WITH PREFETCH)
                               |              |--Clustered Index Scan(OBJECT:([TESTDB].[dbo].[TBL3].[PK_TBL3]),  WHERE:([TBL3].[FIELD3]='1') ORDERED FORWARD)
                               |              |--Clustered Index Seek(OBJECT:([TESTDB].[dbo].[TBL2].[PK_TBL2]), SEEK:([TBL2].[FIELD1]=[TBL3].[FIELD1]),  WHERE:([TBL2].[FIELD3]='1') ORDERED FORWARD)
                               |--Nested Loops(Inner Join, OUTER REFERENCES:([TBL1].[FIELD7], [TBL1].[FIELD1]))
                                    |--Filter(WHERE:([TBL3].[FIELD2]=[Expr1019]))
                                    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([TBL1].[FIELD7], [TBL1].[FIELD1]))
                                    |         |--Filter(WHERE:([TBL2].[FIELD2]=[Expr1015]))
                                    |         |    |--Nested Loops(Inner Join, OUTER REFERENCES:([TBL1].[FIELD7], [TBL1].[FIELD1]))
                                    |         |         |--Filter(WHERE:((rtrim(Convert(isnull([TBL1].[FIELD8], '')))<'' OR rtrim(Convert(isnull([TBL1].[FIELD8], '')))>'') AND ([TBL1].[FIELD8]<'000000000' OR [TBL1].[FIELD8]>'000000000')))
                                    |         |         |    |--Bookmark Lookup(BOOKMARK:([Bmk1000]), OBJECT:([TESTDB].[dbo].[TBL1]))
                                    |         |         |         |--Index Seek(OBJECT:([TESTDB].[dbo].[TBL1].[IDX1_TBL1]), SEEK:([TBL1].[FIELD1]=[TBL3].[FIELD1] AND [TBL1].[FIELD7] >= '20080601' AND [TBL1].[FIELD7] <= '20080630') ORDERED FORWARD)
                                    |         |         |--Hash Match(Cache, HASH:([TBL1].[FIELD7], [TBL1].[FIELD1]), RESIDUAL:([TBL1].[FIELD7]=[TBL1].[FIELD7] AND [TBL1].[FIELD1]=[TBL1].[FIELD1]))
                                    |         |              |--Stream Aggregate(DEFINE:([Expr1015]=MAX([TBL2].[FIELD2])))
                                    |         |                   |--Clustered Index Seek(OBJECT:([TESTDB].[dbo].[TBL2].[PK_TBL2]), SEEK:([TBL2].[FIELD1]=[TBL1].[FIELD1] AND [TBL2].[FIELD2] <= [TBL1].[FIELD7]),  WHERE:([TBL2].[FIELD3]='1') ORDERED FORWARD)
                                    |         |--Hash Match(Cache, HASH:([TBL1].[FIELD7], [TBL1].[FIELD1]), RESIDUAL:([TBL1].[FIELD7]=[TBL1].[FIELD7] AND [TBL1].[FIELD1]=[TBL1].[FIELD1]))
                                    |              |--Stream Aggregate(DEFINE:([Expr1019]=MAX([TBL3].[FIELD2])))
                                    |                   |--Clustered Index Seek(OBJECT:([TESTDB].[dbo].[TBL3].[PK_TBL3]), SEEK:([TBL3].[FIELD1]=[TBL1].[FIELD1] AND [TBL3].[FIELD2] <= [TBL1].[FIELD7]),  WHERE:([TBL3].[FIELD3]='1') ORDERED FORWARD)
                                    |--Hash Match(Cache, HASH:([TBL1].[FIELD7], [TBL1].[FIELD1]), RESIDUAL:([TBL1].[FIELD7]=[TBL1].[FIELD7] AND [TBL1].[FIELD1]=[TBL1].[FIELD1]))
                                         |--Stream Aggregate(DEFINE:([Expr1023]=MAX([TBL6].[FIELD2])))
                                              |--Clustered Index Seek(OBJECT:([TESTDB].[dbo].[TBL6].[PK_TBL6]), SEEK:([TBL6].[FIELD1]=[TBL1].[FIELD1] AND [TBL6].[FIELD2] <= [TBL1].[FIELD7]),  WHERE:([TBL6].[FIELD3]='1') ORDERED FORWARD)

     

    2008年7月17日 6:36
  • NodeId Parent PhysicalOp LogicalOp
    2 1 Sort Distinct Sort
    3 2 Hash Match Inner Join
    4 3 Clustered Index Scan Clustered Index Scan
    5 3 Merge Join Inner Join
    6 5 Clustered Index Scan Clustered Index Scan
    7 5 Sort Sort
    8 7 Nested Loops Inner Join
    9 8 Hash Match Inner Join
    10 9 Clustered Index Seek Clustered Index Seek
    11 9 Hash Match Inner Join
    12 11 Clustered Index Scan Clustered Index Scan
    13 11 Nested Loops Inner Join
    15 13 Clustered Index Scan Clustered Index Scan
    16 13 Clustered Index Seek Clustered Index Seek
    19 8 Nested Loops Inner Join
    20 19 Filter Filter
    21 20 Nested Loops Inner Join
    22 21 Filter Filter
    23 22 Nested Loops Inner Join
    24 23 Filter Filter
    25 24 Bookmark Lookup Bookmark Lookup
    26 25 Index Seek Index Seek
    50 23 Hash Match Cache
    51 50 Stream Aggregate Aggregate
    52 51 Clustered Index Seek Clustered Index Seek
    61 21 Hash Match Cache
    62 61 Stream Aggregate Aggregate
    63 62 Clustered Index Seek Clustered Index Seek
    72 19 Hash Match Cache
    73 72 Stream Aggregate Aggregate
    74 73 Clustered Index Seek Clustered Index Seek

     

    2008年7月17日 6:39
  • EstimateRows
    EstimateIO EstimateCPU AvgRowSize
    201.2477 0.01126126 0.02295456 24
    1401.568 0 0.03405027 30
    159 0.03831924 0.0003029 40
    1878.655 0 0.01559552 80
    2565 0.05091183 0.0029 46
    1878.655 0.01126126 0.03197284 42
    1878.655 0 0.006224769 129
    1489.179 0 0.03260817 48
    159 0.007069241 0.000304328 40
    1489.179 0 0.0297797 40
    19 0.0375785 9.94E-05 30
    1489.179 0 0.006224769 80
    1111 0.04498591 0.0013017 43
    1.340395 0.0063285 8.00E-05 45
    1 0 1.25E-05 90
    1 0 4.80E-07 81
    1 0 1.25E-05 81
    1 0 6.06E-07 73
    1.261537 0 1.25E-05 73
    1.261537 0 3.13E-06 65
    1.261537 0.00625 1.39E-06 65
    1.261537 0.0063285 7.99E-05 56
    1 0 7.46E-05 15
    1 0 2.50E-07 15
    1 0.0063285 7.96E-05 45
    1 0 6.80E-05 15
    1 0 2.50E-07 15
    1 0.0063285 7.96E-05 43
    1 0 6.80E-05 15
    1 0 2.50E-07 15
    1 0.0063285 7.96E-05 46

     

    2008年7月17日 6:41
  • TotalSubtreeCost EstimateExecutions
    18.93039 1
    18.89618 1
    0.03862214 1
    18.8234 1
    0.05381183 1
    18.75276 1
    18.70952 1
    0.2611294 1
    0.007373569 1
    0.2210467 1
    0.0376779 1
    0.153577 1
    0.04628761 1
    0.09981606 1111
    18.44217 1489.179
    18.39636 1489.179
    18.39564 1489.179
    18.34983 1489.179
    18.34893 1489.179
    18.30309 1489.179
    18.29844 1489.179
    8.744357 1489.179
    0.02716222 4467.538
    0.006653194 2.084006
    0.006651836 2.084006
    0.02713719 4467.538
    0.006634754 2
    0.006633444 2
    0.0271371 4467.538
    0.006634672 2
    0.006633361 2

     

    以上となります。

    よろしくお願いします。

     

    2008年7月17日 6:43
  • こんにちは、naginoです。

     

    多数の詳細情報ありがとうございます。

     

     

     

    まず、Existsを使用することでかなり高速になったとのこと、ご確認ありがとうございます。

    これより、少なくとも実行プラン、ないしクエリ次第で「SQL Server 2005」側の環境でも十分高速に処理できるだけの

    処理能力があることが判ります。

     

    もし、クエリの変更が可能で、かつ速度改善を第一の目的とされる場合は、クエリのチューニングによる高速化を

    目指すというのも選択肢としてありかと考えます。

    クエリの変更が不可、あるいは原因の追究を目指す場合は、時間がかかりますし突き止められるか解りませんが、

    実行プランの解析を進めることになると考えます。

    業務上の様々な制約にもよりますので、一度ご検討ください。

     

     

     

    実行プランの詳細について、まずは簡単に確認しました。

    実行プラン上は2005側の方がコストを小さく見積もっているようですので、2005側のオプティマイザが判断を誤っているのでは、

    という印象がありますが、断定できないところです。

     

    実行プランを見て確認いただきたいことがあります。

    2000、2005で使われているクエリはまったく同一だと思われますが、なぜか2005側でのみTBL1のFIELD6にある

    クラスタ化インデックスを処理の過程で使用しているように見受けられます。

    TBL1のFIELD6には、2000、2005両環境ともにクラスタ化インデックスPK_TBL1がありますでしょうか?

    また、2000、2005両環境の実際のクエリでTBL1のFIELD6を使用していますでしょうか?

     

    少し前のコメントで以下の記述があったかと思います。

    > SQLサーバ2005では、

    > TBL1のIndexSeekには1%、TBL2のIndexSeek90%、NestedLoopsで1~2%ずつとなっています。

    こちらの「TBL2のIndexSeek90%」というのは、実行プランに2つあるClustered Index Seekの合計値ということでしょうか?

    また、その場合、それぞれの値はいくつでしょうか?

     

    念のため、2000と2005両環境の照合順序を確認していただけますでしょうか?

    SQL Server Management Studioであれば、データベースのプロパティから確認いただけます。

    あるいは、以下のクエリで確認いただけます。

    SELECT [name], collation_name FROM sys.databases

     

    > 全部足すと、172%になってしまいました。

    実行計画の%を合計して170%を超えるというのは、初めての経験です。

    そのようなことが発生するということに驚きです。

     

     

    よろしくお願いします。

     

    2008年7月17日 13:05
  • こんばんは、ヅダです。

     

    Existsを使用すると、クエリの実行時間が早くなるということがわかり、

    プログラムの対応はできそうです。

    ありがとうございます。

     

    しかしながら、他にもこのような現象が出ないか心配なところです。

     

     

    > 実行プランを見て確認いただきたいことがあります。

    > 2000、2005で使われているクエリはまったく同一だと思われますが、なぜか2005側でのみTBL1のFIELD6にある

    > クラスタ化インデックスを処理の過程で使用しているように見受けられます。

    > TBL1のFIELD6には、2000、2005両環境ともにクラスタ化インデックスPK_TBL1がありますでしょうか?

    > また、2000、2005両環境の実際のクエリでTBL1のFIELD6を使用していますでしょうか?

    PK_TBL1は、2000、2005両環境で作成してあります。

    同一のSQLでテーブル作成しているので、間違いないかと思います。

    実際のクエリは、前に記載したSQL(複雑なほうの)そのままですので、

    FIELD6は使用していません。

    ただ、TBL1のクラスタ化インデックスはFIELD6で、FIELD1は非クラスタ化インデックスとなっています。

     

    > 少し前のコメントで以下の記述があったかと思います。

    > > SQLサーバ2005では、

    > > TBL1のIndexSeekには1%、TBL2のIndexSeek90%、NestedLoopsで1~2%ずつとなっています。

    > こちらの「TBL2のIndexSeek90%」というのは、実行プランに2つあるClustered Index Seekの合計値ということでしょうか?

    > また、その場合、それぞれの値はいくつでしょうか?

    申し訳ありません。

    前述のとおり、「TBL2のIndexSeek90%」という数字は、このSQL文では出ておらず、

     

    TBL1:NestedLoops:キー参照:53%

    TBL1:NestedLoops:IndexSeek:1%

    TBL2:NestedLoops:ClusteredIndexSeek:19%

    TBL2:NestedLoops:ClusteredIndexSeek:32%

    TBL3:NestedLoops:ClusteredIndexSeek:16%

    TBL3:NestedLoops:ClusteredIndexSeek:27%

    TBL4:NestedLoops:ClusteredIndexScan:1%

    TBL6:NestedLoops:ClusteredIndexSeek:22%

    TBL6:NestedLoops:ClusteredIndexScan:1%

     

    という数字がグラフィカルな実行プランでは表示されます。

     

    > 念のため、2000と2005両環境の照合順序を確認していただけますでしょうか?

    2000では「Japanese_BIN」、2005では「Japanese_CI_AS」となっていました。

    まったく同じ環境になっておらず、申し訳ありませんでした。

     

    > > 全部足すと、172%になってしまいました。

    > 実行計画の%を合計して170%を超えるというのは、初めての経験です。

    > そのようなことが発生するということに驚きです。

    私もなぜこのような数字になるのか、不思議で仕方ありません。

    画像がアップできればいいのですが・・・

    2008年7月18日 9:59
  • こんにちは、naginoです。

     

    > しかしながら、他にもこのような現象が出ないか心配なところです。

    ごもっともです。

    何かはっきりした原因が特定できれば良いのですが、実行プランを解析しながらとなると、

    実際のDBに触れない状況では芳しい進捗が得られない点はご容赦ください。

    技術的な解決を迅速に目指すのであれば、これまでの情報を元に有償サポートへ

    お問い合わせされることもご検討ください。

     

    > ただ、TBL1のクラスタ化インデックスはFIELD6で、FIELD1は非クラスタ化インデックスとなっています。

    ご確認ありがとうございます。

    疑うような質問ばかりで申し訳ないのですが、こちらは2000、2005両環境どちらも、ということで相違ないでしょうか?

     

    実行プランを見る限り、何らかの理由でTBL1の処理方法について異なる判断を下されているのが気になります。

    2005側のオプティマイザがTBL1のFIELD6にあるPK_TBL1を使ったほうが高速、あるいは使わざるを得ないと

    判断したその理由がポイントになりそうだと推測しています。

    今の時点ではクエリや実行プランなどから、TBL1ないしTBL2に原因がある可能性が一番高いと推測しています。

    TBL1には、IDX1_TBL1という非クラスタ化インデックスも作成されているかと思いますが、このインデックスは

    2000、2005両環境間で差異はありませんでしょうか?

    2000側だけ複数の列に対してインデックスを作成している、などの可能性も含めてご確認いただけますでしょうか?

    他の管理者の方が変更を加えていたなどの可能性もありますので、できる限り現在の実際の状態の確認を

    お願いします。

    もし可能ならば、お手数ですが両環境の現在のTBL1を、インデックスなども含めてEnterprise Managerや

    SQL Server Management Studioの機能でCREATE文として出力し、そこに何らかの差異が無いかを

    確認いただければと思います。

     

    > 前述のとおり、「TBL2のIndexSeek90%」という数字は、このSQL文では出ておらず、

    > (中略)

    > という数字がグラフィカルな実行プランでは表示されます。

    了解しました。

    TBL3やTBL6のClustered Index Seekの数値が大きいのは違和感があるため、そこも原因の可能性があり、

    本当であれば調査すべきところではありますが、とりあえずは保留とさせてください。

     

    > 2000では「Japanese_BIN」、2005では「Japanese_CI_AS」となっていました。

    > まったく同じ環境になっておらず、申し訳ありませんでした。

    こちらは確認のために質問させていただいたので、お気になさらないでください。

    差異が1つ見つかったというのは一歩前進だと思います。

    格納されているデータの特長にもよりますが、比較やソートの結果が若干異なるものの、一般には

    「Japanese_BIN」のほうが処理が多少高速です。

    ただし、例外的な場合を除いて、速度が劇的に変化するようなことは稀だと思われます。

    もしこの点について確認されたい場合は、クエリの比較処理(ON句、WHERE句など)全ての後ろに1つ1つ

    「COLLATE Japanese_BIN」などとつけて照合順序を変更することで簡易的に確認できます。

    ただし、一時テーブルの照合順序などの問題もありますので厳密ではなく、厳密に確認を行うとなると

    それなりに手間がかかってしまう点はご留意ください。

    詳細については照合順序について以下のページをご参照いただくか、話題が変わりますので別途スレッドを作成して

    いただければと思います。

    http://msdn.microsoft.com/ja-jp/library/ms188046.aspx

     

    > 172%

    単純にそういうものであるならば、話の種として面白いということで済むのですが、もし何らかの理由で

    正常にコストの見積もりが出来ないことが原因で発生しているのであれば、万に一つより少ないですが、

    オプティマイザの不具合の可能性もありえない話ではないかもしれません。

     

    色々とお手間を取らせてすみませんが、よろしくお願いします。

     

    2008年7月18日 11:44
  • こんばんは、ヅダです。

     

    > 技術的な解決を迅速に目指すのであれば、これまでの情報を元に有償サポートへ

    > お問い合わせされることもご検討ください。

    承知しました。

    アドバイスありがとうございます。

     

    > もし可能ならば、お手数ですが両環境の現在のTBL1を、インデックスなども含めてEnterprise Managerや

    > SQL Server Management Studioの機能でCREATE文として出力し、そこに何らかの差異が無いかを

    > 確認いただければと思います。

    テスト環境を作成する際に、2000で出力したスクリプトで2005のテーブルを作成しているので、

    差異はないかと思います。

    双方のTBL1のSQLスクリプトを2005のManagementStudioから出力しました。

    なお、実際のテーブルには、もっとフィールドが存在するのですが、

    SQLで使用している項目のみに割愛しております。

     

    SQL Server 2000

     

    USE [TESTDB]
    GO
    /****** オブジェクト:  Table [dbo].[TBL1]    スクリプト日付: 07/18/2008 22:55:16 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING OFF
    GO
    CREATE TABLE [dbo].[TBL1](
     [FIELD6] [char](10) NOT NULL,
     [FIELD7] [char](8) NOT NULL,
     [FIELD8] [char](9) NULL,
     [FIELD9] [numeric](3, 0) NOT NULL,
     [FIELD1] [char](5) NULL,
     CONSTRAINT [PK_TBL1] PRIMARY KEY CLUSTERED
    (
     [FIELD6] ASC
    ) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    CREATE NONCLUSTERED INDEX [IDX1_TBL1] ON [dbo].[TBL1]
    (
     [FIELD1] ASC,
     [FIELD7] ASC,
     [FIELD9] ASC
    ) ON [INDEX_SPACE]
    GO

     

    SQL Server 2005

     

    USE [TESTDB]
    GO
    /****** オブジェクト:  Table [dbo].[TBL1]    スクリプト日付: 07/18/2008 22:54:55 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    SET ANSI_PADDING ON
    GO
    CREATE TABLE [dbo].[TBL1](
     [FIELD6] [char](10) NOT NULL,
     [FIELD7] [char](8) NOT NULL,
     [FIELD8] [char](9) NULL,
     [FIELD9] [numeric](3, 0) NOT NULL,
     [FIELD1] [char](5) NULL,
     CONSTRAINT [PK_TBL1] PRIMARY KEY CLUSTERED
    (
     [FIELD6] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO
    SET ANSI_PADDING OFF
    GO
    CREATE NONCLUSTERED INDEX [IDX1_TBL1] ON [dbo].[TBL1]
    (
     [FIELD1] ASC,
     [FIELD7] ASC,
     [FIELD9] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [INDEX_SPACE]
    GO

    > 単純にそういうものであるならば、話の種として面白いということで済むのですが、もし何らかの理由で

    > 正常にコストの見積もりが出来ないことが原因で発生しているのであれば、万に一つより少ないですが、

    > オプティマイザの不具合の可能性もありえない話ではないかもしれません。

    その可能性も考慮すると、

    やはりサポートに問い合わせたほうがいいかもしれませんね。

    ご助言ありがとうございます。

     

    2008年7月18日 14:12
  • こんにちは、naginoです。

     

    詳細な情報ありがとうございます。

    非クラスタ化インデックスの定義を拝見して、思い当たる節がありました。

    カバリングインデックス関連の動作がSQL Server 2000とSQL Server 2005で変更になっているのを思い出しました。

     

    クラスタ化インデックスがFIELD6に、非クラスタ化インデックスがFIELD1、FIELD7、FIELD9に対して作成されています。

    SQL Server 2000では、FIELD1を使用したJOINと、FIELD7を使用したWHERE句があるので、そこで非クラスタ化インデックスを

    使用し、FIELD8を使用したWHERE句があるのでBookmark Lookupを行っています。

    通常Bookmark Lookupは高速ではないので、Index SeekとStream Aggregateの組み合わせになるようにカバリングインデックスを

    作成したりするのが、クエリチューニングの手法の一つです。

     

    一方、SQL Server 2005では、非クラスタ化インデックスを使用しているのは同じですが、その後Bookmark Lookupをせずに

    クラスタ化インデックスをClustered Index SeekしてJoinしてからFIELD8を使用したWHERE句の条件でFilterしています。

    これは、Bookmark LookupがSQL Server 2005から使用されなくなったためです。

    http://msdn.microsoft.com/ja-jp/library/ms180920.aspx

    ですが、そのためにTBL1とTBL5のJOIN、そしてその結果に対してのJOINが発生し、行数の多いまま処理が進むために

    結果として遅くなっているようです。

     

    この変更は上記のURLにあるように仕様ですので、同じクエリ、同じスキーマ(構造)で実行プランが変化してしまったのも

    納得です。

     

    2005では、クエリとスキーマがこのままでは2000と同様の実行プランが作成されることはありませんので、

    クエリかスキーマを変更することになります。

     

    目立って速度が変化したクエリが今回問題となっているクエリのみであれば、Existsによるパフォーマンスチューニングが

    有効であることが分かっていますので、クエリのチューニングをお勧めします。

     

    他にも速度が変化したクエリがあるのであれば、それらのJOIN条件を調べて適切なカバリングインデックスを用意されることを

    お勧めします。

    ただし、SQL Server 2005からは付加列がありますので、インデックス追加によるディスク使用量や更新のオーバーヘッドが

    軽減されていますが0になっているわけではありませんので、これらが許容できない場合は1つ1つのクエリのチューニングを

    お勧めします。

    http://msdn.microsoft.com/ja-jp/library/ms190806.aspx

    http://msdn.microsoft.com/ja-jp/library/ms189607.aspx

     

    実行プランを提示いただいていながら、Bookmark Lookupをぼんやりと見過ごしていました。

    余計なお手数をおかけしてすみませんでした。

    一助になれば幸いです。

    2008年7月18日 15:40
  • こんにちは、ヅダです。

     

    今回の件では、いろいろとご尽力いただきまして、ありがとうございました。

    2000と2005での違いをもう一度確認し、チューニングしていきたいと思います。

    それと、もっと実行プランについて勉強しないと、チューニングができないですね^^;

    精進いたします。

     

    このたびはありがとうございました。

     

    2008年7月22日 4:39
  • もう見てないかも知れませんが、「最もよく参照された回答」にあったのでコメントします。
    2005なら非クラスタ化インデックスに付加列オプションを追加するのも一つの手です。2000ならカバリングインデックスでの対応でしたが。
    ただ、元のSQLを拝見すると、2000ではたまたま遅くなかっただけでデータ量が増えたり環境がちょっと変わるといきなり遅くなりそうだと感じました。
    2008年9月21日 13:39