SQL Server のクエリチューニングを行う際に、チューニング対象となるクエリの実行プランの結合の処理を問題が発生している処理と同等の方法にして確認を行いたいケースがあります。
結合ヒント が使用できる個所であれば、ヒント句を追加して確認をすればよいのですが、ヒント句が指定できないようなクエリとなっている場合、ヒント句では明示的な制御ができないことがあります。
本投稿では、ヒント句で結合方法が制御できない場合の対応方法の一例を紹介します。
今回対象とするクエリ
今回対象とするクエリは次のクエリとなります。
SELECT P_PARTKEY FROM PART WHERE NOT EXISTS (SELECT * FROM PARTSUPP WHERE PARTSUPP.PS_PARTKEY = PART.P_PARTKEY) AND PART.P_SIZE =50
このクエリを私の環境で実行した場合、次のような実行プランが生成されています。
NOT EXISTS によりデータを間引くため、「Left Anti Semi Join」の論理操作が実行されていますが、この際の物理操作は「Hash Match」となっています。
FROM 句で 結合ヒント が指定できるのであれば、結合方法を制御することができますが、今回のクエリは NOT EXISTS により結合を実施しているため、結合方法を明示的に指定することができないという認識でいます。
使用されるクエリルールを制御することで結合ルールを制御する
それでは先ほどのクエリの結合方法を制御したいと思います。
結合ヒントは使用することができないためそれ以外の方法で制御を行う必要があります。
先ほどのでは、結合の方法として、次の操作が行われていました。
- 論理操作: Left Anti Semi Join
- 物理操作: Hash Match
実行プランを生成する際のルールとして、これらの操作が行われるためのルールの使用を明示的に禁止することで、結合ルールを制御するという方法があります。
このようなときに使用することができるのが「QUERYRULEOFF」というアンドキュメントなオプションとなります。
内部利用を目的とした情報となるのですが、SQL Server では「sys.dm_exec_query_transformation_stats」というアンドキュメントの動的管理ビュー (DMV) が提供されています。
この DMV は、実行プランを生成する際に各操作をどのように変換したのかの統計の情報となるのですが、この DMV で確認できる内容が実行プランを生成する際に使用されるルールとなります。
SQL Server は実行プランを生成する際に、上記の DMV で確認できるようなルールを適用した際のコストを算出し実行プランの生成を行っています。
実行プランを生成する際の情報は Memo Structure と呼ばれ、情報を確認することができます。
ここで使用されているルールを「QUERYRULEOFF」で使用を制限することで、期待した実行プランの生成を促すことができます。
冒頭に記載した実行プランであれば、「LASJNtoHS」「RASJNtoHS」のルールの使用を制限することで、Hash Match による Anti Semi Join が使用されないプランを生成することができます。
具体的には、次のようなクエリとなります。
SELECT P_PARTKEY FROM PART WHERE NOT EXISTS (SELECT * FROM PARTSUPP WHERE PARTSUPP.PS_PARTKEY = PART.P_PARTKEY) AND PART.P_SIZE =50 OPTION ( RECOMPILE , QUERYRULEOFF LASJNtoHS,QUERYRULEOFF RASJNtoHS )
このようなクエリを実行すると、次のような実行プランとなります。
「LASJNtoHS」「RASJNtoHS」の使用を制限したため、「Left Anti Semijoin」の物理操作として「Hash Match」ではなく「Nested Loops」を使用する実行プランが生成されました。
本番環境での使用は推奨できませんが、結合ルールが使用できないようなクエリで検証目的で結合方法の制御を行いたい場合「QUERYRULEOFF」を使用することで検証で使用したい実行プランを生成することができるケースがありますので、このような方法を覚えておくと役に立つことがあります。