読者です 読者をやめる 読者になる 読者になる

都内で働くSEの技術的なひとりごと

都内でサラリーマンやってます。マイクロソフト系(たまに、OSS系などマイクロソフト以外の技術も...)の技術的なことについて書いています。日々の仕事の中で、気になったことを技術要素関係なく気まぐれに選んでいるので記事内容は開発言語、インフラ等ばらばらです。なお、当ブログで発信、発言は私個人のものであり、所属する組織、企業、団体等とは何のかかわりもございません。ブログの内容もきちんと検証して使用してください。よろしくお願いします♪

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

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

 夜は天一のラーメン。

 翌朝は牛肉入りのきしめん
f:id:koogucc11:20150723220030j:plain

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

 めんの話はさておき、今日は 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 の環境全部つぶしてしまった。)
f:id:koogucc11:20150723222105p:plain

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

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になっています。
f:id:koogucc11:20150724000754p:plain

 次に、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になっています。テーブル変数で宣言したテーブルも統計が有効になっています。
f:id:koogucc11:20150724001056p:plain

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

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

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

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