すっかり忘れていたのですが、SQL Server 2017 CU10 以降で QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_n というヒントが追加されていたことを思い出したのでメモとして。
SQL Server で利用可能なヒント句については ヒント (Transact-SQL) – Query に記載されているのですが、このヒント句に QUERY_OPTIMIZER_COMPATIBILITY_LEVEL というヒントが追加されています。
SQL Server 2019 や、SQL Database では、最新の互換性レベル 「150」がサポートされており、この互換性レベルでは、インテリジェントなクエリ処理 が使用できるようになっています。
通常、互換性レベルは DB 単位で変更していたと思いますが、QUERY_OPTIMIZER_COMPATIBILITY_LEVEL ヒントが使えるバージョンであれば、クエリ単位で互換性レベルを変更することができるようになっています。
互換性レベル 140 の SQL Database の tpch で動作を確認してみます。
次のようなクエリを互換性レベル 140 に対して実行してみます。
SELECT L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY) AS SUM_QTY,
SUM(L_EXTENDEDPRICE) AS SUM_BASE_PRICE, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS SUM_DISC_PRICE,
SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)) AS SUM_CHARGE, AVG(L_QUANTITY) AS AVG_QTY,
AVG(L_EXTENDEDPRICE) AS AVG_PRICE, AVG(L_DISCOUNT) AS AVG_DISC, COUNT(*) AS COUNT_ORDER
FROM LINEITEM
WHERE L_SHIPDATE <= dateadd(dd, -90, cast('1998-12-01' as datetime))
GROUP BY L_RETURNFLAG, L_LINESTATUS
ORDER BY L_RETURNFLAG,L_LINESTATUS
互換性レベル140 では、Batch Mode on Row Store をサポートしていませんので、列ストアインデックスが設定されていないテーブルの利用が、Row Mode on Row Store で実行されています。
それでは、このクエリに互換性レベル 150 のオプションを付けて実行してみます。
SELECT L_RETURNFLAG, L_LINESTATUS, SUM(L_QUANTITY) AS SUM_QTY,
SUM(L_EXTENDEDPRICE) AS SUM_BASE_PRICE, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS SUM_DISC_PRICE,
SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)*(1+L_TAX)) AS SUM_CHARGE, AVG(L_QUANTITY) AS AVG_QTY,
AVG(L_EXTENDEDPRICE) AS AVG_PRICE, AVG(L_DISCOUNT) AS AVG_DISC, COUNT(*) AS COUNT_ORDER
FROM LINEITEM
WHERE L_SHIPDATE <= dateadd(dd, -90, cast('1998-12-01' as datetime))
GROUP BY L_RETURNFLAG, L_LINESTATUS
ORDER BY L_RETURNFLAG,L_LINESTATUS
OPTION(USE HINT ('QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_150'))
こちらについては互換性レベルが 140 のデータベースでも Batch Mode on Row Store で動作していますね。
新しく追加されているヒントを使用することで、このように DB 単位の互換性レベルを変更することなく、クエリ単位で互換性レベルの変更の影響を確認することができるようになります。
読み取り専用セカンダリがある場合は、読み取り側で検索系のクエリについて、互換性レベルの変更の影響を確認してみるということができるのではないでしょうか。