SQL Server 2012 SP2 のトレースフラグ 2453 を試してみた

　昨日は久しぶりの名古屋出張で、食したものは炭水化物のオンパレード。一泊二日の出張、まず昼はつけ麺。

　夜は天一のラーメン。

おやつカンパニー　ベビースタードデカイラーメン天下一品こってり味 68g×12袋

　翌朝は牛肉入りのきしめん。

　朝昼晩炭水化物だと体重が気になります。今日からしばらく食事量、炭水化物の摂取量を減らさねば。太り過ぎて、早死にしてしまいます....

炭水化物の食べすぎで早死にしてはいけません―生活習慣病を予防＆改善する糖質制限食３１のポイント

　めんの話はさておき、今日は SQL Server ネタについて書きたいと思います。ストアドでクエリ書いていると、どうしてもテーブル変数に頼りたく、いやどうしても使いたくなってしまいますよね？ただ、このテーブル変数は非常に便利なものですが、tempDB を使っちゃうとか、むやみに実テーブルと JOIN などしようものなら、推定行数が常に 1 となって、実行プランを滅茶苦茶にしてくれるこまった奴に変貌してしまいます。そう、少し食べていれば体にいいんですが、一杯たべちゃうと体に悪い炭水化物のよう....

　このテーブル変数、SQL Server 2012 SP2 より統計情報の扱いが見直されました。( https://support.microsoft.com/en-us/kb/2958429 )

Optional support for table variable row counts to improve query plans:
If a table variable is joined with other tables in SQL Server, it may result in slow performance due to inefficient query plan selection because SQL Server does not support statistics or track number of rows in a table variable while compiling a query plan.
In SQL Server 2012 SP2, a new trace flag is introduced that allows the query optimizer to use information about the number of rows inserted into a table variable in order to select more efficient query plan. Enable trace flag 2453 to activate this behavior.

Notes:
In some scenarios, enabling trace flag 2453 may result in some degradation of performance, due to additional compilation required to account for actual number of rows inserted into a table variable during execution time. Typically, you would benefit from this trace flag if a table variable has significant amount of rows joined with other tables, or has more than one row and used at the outer side of a nested loop join operator with a plan on the inner side that processes large amount of rows.
Similar behavior may be achieved on other versions of SQL Server through using OPTION (RECOMPILE) query hint. However, the query hint requires detecting and modifying all queries suffering from poor plan choice due to large amount of work driven by table variables, while enabling the trace flag 2453 can impact existing workloads.

　テーブル変数も一時テーブルと同様に統計情報を持たせることができるようになっています。この機能を有効にするには、下記の記事でも使用した DBCC TRACEON を使用します。ryuchan.hatenablog.com

　早速、トレースフラグ 2453 の効果を体験してみましょう。まず、SQL Server 2012 SP2 の環境を準備します。(ああ、最近 Azure に構築してた 2012 の環境全部つぶしてしまった。)

　まず、環境を整えましょう。データベースを作成します。

CREATE DATABASE StatisticsTest

　テーブルとインデックスを作成します。

USE StatisticsTest 
CREATE TABLE tbl (col1 INT) 
CREATE INDEX IX1_tbl on tbl(col1)

　作成したテーブルにデータを挿入します。

DECLARE @cnt INT 
SET @cnt = 0 
WHILE @cnt < 1000 
BEGIN 
    INSERT INTO tbl VALUES (@cnt) 
    SET @cnt = @cnt + 1 
END

　データを作成したら、統計情報を更新します。

UPDATE STATISTICS tbl

　これで、実テーブルの準備は完了です。まず、トレースフラグ 2453 無効時の動作をみてみましょう。2453のトレースを無効化します。

DBCC TRACEOFF(2453,-1)

　
　下記のクエリを実行します。

USE StatisticsTest

DECLARE @TF_tbl TABLE (col2 INT) 
DECLARE @cnt INT

SET @cnt = 0 
WHILE @cnt < 1000 
BEGIN 
    INSERT INTO @TF_tbl VALUES (@cnt) 
    SET @cnt = @cnt + 1 
END 

SET STATISTICS PROFILE ON
SELECT * FROM tbl
INNER JOIN @TF_tbl 
on col1= col2

　EstimateRows が1になっています。

　次に、2453のトレースを有効化します。

DBCC TRACEON(2453,-1)

　下記のクエリを実行します。

USE StatisticsTest

DECLARE @TF_tbl TABLE (col2 INT) 
DECLARE @cnt INT

SET @cnt = 0 
WHILE @cnt < 1000 
BEGIN 
    INSERT INTO @TF_tbl VALUES (@cnt) 
    SET @cnt = @cnt + 1 
END 

SET STATISTICS PROFILE ON
SELECT * FROM tbl
INNER JOIN @TF_tbl 
on col1= col2

　EstimateRows が1000になっています。テーブル変数で宣言したテーブルも統計が有効になっています。

　ちょっと感動したアップデートでした。

※ひさしぶりのきしめん美味しかった。

東亜食品 冷水ごねきしめん 450g×2袋