都内で働くSEの技術的なひとりごと / Technical soliloquy of System Engineer working in Tokyo

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

SQL Server のチューニングについてまとめてみる - その6 - ( CONVERT_IMPLICIT、暗黙の型変換の怖さを知ろう )

 おお、錦織選手決勝進出しましたね!おめでとうございます♪ 優勝が見えてきました。


【速報】錦織 日本人初で自身初のGS決勝進出、世界ランク1位のジョコビッチ撃破<男子テニス> (tennis365.net) - Yahoo!ニュース

 いやーよかった。それでは、本題に戻りましょう。開発が佳境に入ってくると、『もうデータが取れればいいや!』と勢いだけで SQL を書いてしまいがちです。そのような過程でやってしまいがちのことの一つが『暗黙の型変換』だと思います。場合によっては、かなり恐ろしい事象も発生することがあります。

 早速、暗黙の型変換の例をみてみましょう。今回は、AdventureWorks2012 の HumanResources.Employee テーブルを使用します。HumanResources.Employee のテーブル構成は下図の通りです。今回は、赤枠の列を Where 句の条件とします。

f:id:koogucc11:20140905194530p:plain

 それでは、まず暗黙の型変換を発生させる下記のクエリを SQL Server Management Studio で実行してみましょう。

USE AdventureWorks2012
SET STATISTICS PROFILE ON
SELECT *
FROM HumanResources.Employee
WHERE NationalIDNumber = 295847284

f:id:koogucc11:20140905194741p:plain

 上図から、Index Scan 部分を参照してみましょう。暗黙の型変換 ( CONVERT_IMPLICIT ) が発生しているのがわかります。これは大問題です。左辺が int 型に変換されています。これによりインデックスが有効にならないため、Index Scan が発生しています。( 左辺に関数とか使ったらダメなのは基本ですよね? )

|--Index Scan(OBJECT:([AdventureWorks2012].[HumanResources].[Employee].[AK_Employee_NationalIDNumber]),  WHERE:(CONVERT_IMPLICIT(int,[AdventureWorks2012].[HumanResources].[Employee].[NationalIDNumber],0)=(295847284)))

 IndexScan を発生させないためには、暗黙の型変換を発生させないように、正しい型で条件を設定する必要があります。nvarchar の列に対して、正しい型で条件を指定しました。下記のクエリを SQL Server Management Studio で実行してみましょう。

USE AdventureWorks2012
SET STATISTICS PROFILE ON
SELECT *
FROM HumanResources.Employee
WHERE NationalIDNumber = N'295847284'

f:id:koogucc11:20140905195850p:plain

 初めに実行したクエリは、Scan でしたが、今回は Seek になっています。プラン内容を参照してみましょう。いい感じになってますね。

|--Index Seek(OBJECT:([AdventureWorks2012].[HumanResources].[Employee].[AK_Employee_NationalIDNumber]), SEEK:([AdventureWorks2012].[HumanResources].[Employee].[NationalIDNumber]=[@1]) ORDERED FORWARD)

 上記の内容から 暗黙の型変換の怖さが実感できたかと思います。暗黙の型変換のすべてがここまで重症化するとは限りませんが、ゼロにするのが理想だと思います。たとえば、 下記のクエリを実行すると 暗黙の型変換が発生します。

USE AdventureWorks2012
SET STATISTICS PROFILE ON
SELECT *
FROM HumanResources.Employee
WHERE NationalIDNumber = '295847284'

f:id:koogucc11:20140905200231p:plain

 プランを参照してみましょう。条件を UNICODE で指定しなかったので、暗黙の型変換が発生しています。この場合は、右辺が変換されているので、インデックスはうまく使用されています。

|--Index Seek(OBJECT:([AdventureWorks2012].[HumanResources].[Employee].[AK_Employee_NationalIDNumber]), SEEK:([AdventureWorks2012].[HumanResources].[Employee].[NationalIDNumber]=CONVERT_IMPLICIT(nvarchar(4000),[@1],0)) ORDERED FORWARD)

 最後に 暗黙の型変換が発生しているクエリを抽出してみましょう。前回の記事で、クエリプランの XMLXQuery で処理しました。今回も同じような方法で、暗黙の型変換が発生しているクエリを特定したいと思います。


SQL Server のチューニングについてまとめてみる - その5 - ( クエリプランから不足インデックス情報を取得する ) - 都内で働くSEの技術的なひとりごと

 下記のクエリを SQL Sever Management Studio で実行してみましょう。

WITH XMLNAMESPACES ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
SELECT [データベース名] = qp.query_plan.value('(//sp:ColumnReference/@Database)[1]', 'nvarchar(100)'),
       [スキーマ名] = qp.query_plan.value('(//sp:ColumnReference/@Schema)[1]', 'nvarchar(100)'),
       [テーブル名] = qp.query_plan.value('(//sp:ColumnReference/@Table)[1]', 'nvarchar(100)'),
       [SQL文] = SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
                           ((CASE qs.statement_end_offset
                                 WHEN -1 THEN
                                     DATALENGTH(st.text)
                                 ELSE
                                     qs.statement_end_offset
                                 END -
                                     qs.statement_start_offset)/2
                           ) + 1),
       [暗黙知変換情報] = qp.query_plan.query('//sp:ScalarOperator[contains(@ScalarString, ''CONVERT_IMPLICIT'')]'),
       [クエリプラン詳細] = qp.query_plan
FROM sys.dm_exec_query_stats qs
     CROSS APPLY sys.dm_exec_query_plan(plan_handle) qp
     CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
WHERE qp.query_plan.exist('//sp:ScalarOperator[contains(@ScalarString, ''CONVERT_IMPLICIT'')]') = 1

 赤枠の部分をクリックします。

f:id:koogucc11:20140905203920p:plain

 下記の XML が表示されます。暗黙の型変換が発生しているのが確認できます。

<sp:ScalarOperator xmlns:sp="http://schemas.microsoft.com/sqlserver/2004/07/showplan" ScalarString="CONVERT_IMPLICIT(int,[AdventureWorks2012].[HumanResources].[Employee].[NationalIDNumber],0)=(295847284)">
  <sp:Compare CompareOp="EQ">
    <sp:ScalarOperator>
      <sp:Convert DataType="int" Style="0" Implicit="1">
        <sp:ScalarOperator>
          <sp:Identifier>
            <sp:ColumnReference Database="[AdventureWorks2012]" Schema="[HumanResources]" Table="[Employee]" Column="NationalIDNumber" />
          </sp:Identifier>
        </sp:ScalarOperator>
      </sp:Convert>
    </sp:ScalarOperator>
    <sp:ScalarOperator>
      <sp:Const ConstValue="(295847284)" />
    </sp:ScalarOperator>
  </sp:Compare>
</sp:ScalarOperator>

 クエリでまめに実行プランをチェックして、暗黙の型変換をなくしていきましょう♪

※↓この本、プライム会員だったら 0円ですね。早速、呼んでみよう♪

f:id:koogucc11:20140906202003p:plain