none
暗黙の型変換の影響

    質問

  • はせがわと申します。

    SQL2005のシステムで下記のSQL文を実行して、アプリケーションで使っているSQL文がひっかかる方はいるでしょうか?

     

     --CONVERT_IMPLICITのUNICODE変換をplanから見つける
    WITH XMLNAMESPACES
    ('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS p)
    select
    DISTINCT
     C.value(N'@ScalarString','varchar(16)') as ScalarOperator
     ,text
    FROM
     sys.dm_exec_query_stats
     CROSS APPLY sys.dm_exec_query_plan(plan_handle)
     CROSS APPLY sys.dm_exec_sql_text(sql_handle)
     CROSS APPLY query_plan.nodes(N'//pTongue TiedcalarOperator') as ro(C) WHERE
     C.value(N'@ScalarString','varchar(20)') like 'CONVERT_IMPLICIT(n%'
    go

    以前はJDBC経由でSQLServerを利用するときに良く見たのですが、最近VBなどで何も気にせずにアプリを作成すると

    SELECT文のWhere句で利用されるパラメータがUNICODEで作ってしまいます。

    検索対象のテーブルのカラムは昔の通りvarchareで作っていると暗黙の変換にはまります。

    データ件数が少ないと体感できないのですが、100万件以上のテーブルを作成して確認すると、パフォーマンスが100倍とか悪くなっていました。こんなのにはまっているのは私だけ?

     

     

    2007年4月18日 0:46

すべての返信

  • 内容は見てないですが、Service Pack の適用状況、HotFix の適用状況はどうなのでしょうか?

    問題は、アプリケーションの作り方次第だと思いますがね。

    2007年4月22日 12:39
  • おがわさん

    コメントありがとうございます。

    はせがわです。

     

    おっしゃるとおり、アプリケーションの作り方次第です。

    私の役割としては、トラブルが発生してからいろいろなシステムに

    よばれるのですが、運用を開始してから、型が違っているからだ

    ということが分かっても対応に苦慮します。

    ソースコードで修正対象は無数になっている場合がありますし、

    カラム側の修正はDB容量に影響します。

     

    データ件数が少ないと気が付かない事象ですし、

    エラーになるわけでもありません。

    ただ、2バイトで渡されたパラメータが1バイトコードの検索対象に

    適用された場合に、暗黙の型変換がパラメータ側を変換できない

    ために発生する事象のようです。

    ユーザ側には何が起こっているのかわからないが、異常なほど

    応答が遅くなる場合があることになります。

     

    DB上のカラム属性まで含めたソースコードのチェックが簡単に

    できればよいのですが、なかなかそのようなツールはありません。

     

    可能であれば、SQLServer側に暗黙の型変換が発生した場合には

    ログに警告をだせるような仕様がほしいと思うところです。

     

    MSDNフォーラムに参加されている方のレベルだと

    型を合わせることは一般的であまり問題は発生していないということ

    でしょうね。

     

    2007年4月23日 3:38
  • 明らかに設計ミスってことですね。

    とりあえず、SQL プロファイラでキャプチャして、問題を起こしている SQL 文を SQL Management Studio の実行プランを表示して、変な実行プランになっていないか確認して、ヒント句で解決できるのであれば、その SQL 文の修正で済むのでは?

    インデプスやあと何だっけかな、問題を見つけてくれる有償ツールを使うか、SQL Server のチューニングを専門にやっている会社や人にお願いしたほうがいいと思いますよ。

     

    一応、私もトラブルシュートが仕事の一部ですが。

    2007年4月23日 14:35
  •  米田です。

     

     暗黙の型変換だけでなく、テーブル側が下位なのが大きなパフォーマンスロスに繋がっていると思います。

     あとは「ストアドプロシージャを使用しなさい」という伝統をどうとるかしだいだと思いました。

    挙動的には、以前Blogで紹介した下記KBが近いと思います。

    http://blogs.sqlpassj.org/yoneda/archive/2006/05/06/16794.aspx
    WHERE句では、CASTで型を正確にあわせた方がよい(SPにより挙動差、性能差)
    http://support.microsoft.com/kb/271566/en-us
    The behavior is different when comparing between columns and constants with different data types in SQL Server 7.0 and later versions of SQL Server
    >Set a database compatibility-level of 70 by using sp_dbcmptlevel.

     テストとしては、テスト環境にDBをコピーして、SQL Server7.0互換状態に設定して、性能差がどの程度出るかで、状況を把握するくらいでしょうか。

    2007年4月23日 22:09
  • おがわ様

    長谷川です。

    コメントありがとうございます。

     

    「変な実行プラン」については、select文での区別は難しいかったですね。

    SQL2000のUpdate文でこの事象になったときは、1テーブルの1レコードを

    Whereで絞って更新するだけでワークテーブルができていたので、

    おかしいことに気づくことができました。このときはJDBC経由でしたが。

     

    ヒント句では解決できないようです。

    インデプスは見たことがありませんが、WaitType?やブロッキングの観点からだと

    難しいかもしれません。

    同じSQL文で実行プランも同じ。Where句に渡すパラメータが変わっただけで

    IO数が極端に変化するのが特徴です。

    (もちろん、インデックスの統計情報は更新済みで断片化無しの状態。)

     

    SQL2005なら最初にあげたクエリでわりと簡単に確認できるのですが、

    トラブルシュートを頼まれる私としては、SQLServerの機能として警告ぐらいは

    だしてほしいと思うのです。

     

    2007年4月24日 4:08
  • Service Pack の適用状態では、適切な実行プランを無視してくれるという問題を体験したことがあります。

    データ量が少ない時は問題なく動作するのですが、あるしきい値を超えると途端に変な動きをしたので、現象はかなり似てると思います。

    統計情報の更新してあって、インデックスのフラグメンテーションがない場合でもです。

    そのときは、データを分割(アーカイブ)して変な動きをしないように対処療法を行いましたが。

    ついでにアプリケーションに手を入れて修正も行っています。

    Visual Studio 2005 なら修正は比較的楽です。

    ただ、クラスの設計次第なのですが、フォームごとにデータベースへの接続するコードが書かれていたら、完全に修正するのは難しいでしょう。

     

    プログラムに手を入れられない場合、SQL Server 側でできることはかなり限られます。

    インデプス、スポットライトなどの有償ツールを使ってボトルネックを見つけてチューニングするとか、MAXDOP を 1 に制限する、論理 CPU 分のデータファイル、tempdb を超高速な RAM Disk 上に構築する、Soft-NUMA を構成するなど、かなり限られてきます。

    あとは、プレミアサポートに頼るしかないでしょうね。

    2007年4月24日 4:40
  • 米田様

    はせがわです。

     

    情報ありがとうございます。

    原因としては同じことを指していると思います。

     

    最近考えていたのは、エラーは発生していないが、一人で使っていても時々遅いという

    トラブルが発生してから呼ばれて、この事象を特定するには

    どうすればよいかということでした。

    エラーメッセージやツールを持ち込んで特定できれば良いのですが、

    なかなか難しいと感じています。そもそもSQLServer的にはエラーではありませんし。

    それで、実行プランから暗黙の変換を検索するのが一番はやそうと思っているところです。

    現在は設計が悪い可能性があるとして、テーブルスキャンとハッシュとUNICODEへの

    暗黙の変換を実行プランから検索して、SQL文を抽出しています。(SQL2005のみ)

     

    「ストアドプロシージャ」については、私が関わるところは、Oracleな開発者がSQLServer

    のシステムを作っていることが多いようで、その影響かどうかはわかりませんが

    残念ながら「ストアドプロシージャ」で実装されていることはほとんどありません。

    文化なのでしょうか。。。

     

     

     

    2007年4月24日 4:44
  • Oracle の開発者がストアドプロシージャを使わないのは、その人たちだけの文化でしょう。

    特に SELECT の結果を返すのに Ref Cursor を使わなければ返せない Oracle では面倒だと思っている人が多いためだと思います。

    私は Oracle でもストアドプロシージャを使います。

     

    SQL Server はご存じのとおり SELECT の結果を自動的に返すので変な小細工しないで済むし、プランキャッシュの乗るためパフォーマンスが向上します。

    ただこいつも Memory To Reserve の領域に確保されるので、SQL プロファイラでプリコンパイルなどのイベントが多発していると、Memory To Reserve 不足で発生しているので -g オプションで拡張する必要があります。

    あと、ついでですが、接続できる数をあえて制限する手もあります。

    これは大規模なシステムで Web ファームを構成されたシステムだと接続(Connection Pooling が効いてしまって)が膨大になりメモリ不足に陥って全体のパフォーマンスを落とすことがあるので、全体の接続数をあえて制限するという手法です。

    どれぐらいのシステムかというと証券会社系とか銀行系のように瞬間的に接続が急激に増えることがあるシステムレベルの話です。

    2007年4月24日 5:20
  • はせがわです。

    なんどもコメントありがとうございます。

     

    「SQLServerは自動チューニングだから大丈夫なんでしょ」とおえらい方には言われます。

    今回の事象やブロッキングは作りでなんとかすべき問題で、SQLServerでも最低限やることはやってほしいなと思う今日この頃。

     

    >あるしきい値を超えると途端に変な動き

    今回の例ですと、レコード件数が少ないと体感がなく、気が付かずに運用に入ってしまいます。

    実際に応答時間400msだったクエリが応答時間0ms(計測不可?)になったこともあります。

    (依頼元が400msを問題視するかどうかで、事象の解析依頼がくるかどうかがかわります。)

    150万レコードくらいの案件のときは、応答時間50秒くらいが、数百msになりました。

     

    ただ、発生するパターンのパラメータ側の暗黙の型変換ですむか、そうではないのかのパターンははっきりとはつかめていません。

     

    プレミアには相談する前に事象としては把握できているので、パートナー企業として

    情報共有しているくらいです。

     

     

    2007年4月25日 3:54
  • はせがわ様

     

    やまにょんと申します。

     

    型の異なる値を比較する行為について、プログラム上よくない (所謂「設計ミス」)  という

    皆さんの指摘はそのとおりだと考えます。

    ここは恐らくはせがわ様と回答者の方々の認識に相違ないと思います。

     

    しかし、SQL Server 上で型の異なる値を比較した場合、

    DB 側において型変換が自動的に発生し、その結果見た目正常に終了するため、

    型が異なるという設計ミスが表面化しにくいという状況は確かにあります。

    この暗黙の型変換によって、試験用の少量のデータには劣化しないパフォーマンスが、

    大量の運用データを投入することで顕在化してしまうという状況のため、

    問題の表面化が極めて手遅れに近い状況で起きるという状況だと私は考えています。

     

    恐らく、はせがわ様の立場 (というかトラブルシューター全体) において

    その手の「手遅れの設計ミス」を指摘し、正しく修復させることは非常に難しい状況にあるのではないでしょうか。

    要するにコスト・運用上、対策が不可能に近い改善方法を先方へ顧客に返答することが「最適ではない」と。

     

    で、何が最適かというと、いかにしてそれを予防するかという方法、つまり予防策を考えたい、

    という結論に達したというわけなんですね。

     

    私としては、暗黙の型変換をわかり易く判断する方法さえあれば、

    共通化された試験項目でも十分判定できるため、有用だと考えますがいかがでしょう。

    簡単なところで、例えばイベントログに警告を表示するとか何とか。

    実際そういう手段は SQL Server 2005 で提供されていないのでしょうか?

    2007年4月25日 7:01
  • 警告を出す手法は動的管理ビューである一定以上 CPU やリソースを食っているクエリが来た時には、イベントログに書き込むなどのジョブを作るしかないでしょう。

    もしくはパフォーマンスモニタで何とかするとか。

    一番楽な方法は MOM 2005 を導入することですね。

    そうでないと、何かしらのプログラミングが必要です。

    暗黙的型変換は SQL プロファイラで見ていればわかると思います。

    どのイベントがそうだったか忘れましたが、どっかに書いてあったと思います。

    ただ、SQL プロファイラは CPU の 20 ~ 30% ぐらい持っていく場合があります。

    動的管理ビューで定期的に監視した場合でも同様です。

    日揮情報さんだったか、Symantech だったか CA だったかに軽量な監視・監査ツールがあったはずです。

    Oracle でのインデプスのイメージがあるのですがそれらを使って調査するか、SQL Server のパフォーマンス調査を得意にする会社(熊澤さんのところ)にコンタクト取るというのもありだと思います。

    意外とプレミアサポートではつぶせないこともあったりするので。

    #熊澤さんの会社が知りたければ私のプロファイルに連絡先が書いてありますので聞いてください。

    2007年4月25日 11:07
  • やまにょんです。

     

    解決方法を整理するとこうですか?

    1. なんらかの方法で CPU 使用率を監視する
    2. SQL プロファイラで常にウォッチする (監視ツールを入手してそれに頼る)

    1. は本番運用中でしか検出できない。(これだとダメなんですよね?)

    2. は開発現場の人でコスト負担して頑張らなくてはならない。

     

    しっかし・・・SQL Server も余計なことすんな、って感じですね。

    このくらい ON/OFF の選択ができればいいのに、と思います。

    2007年4月26日 2:35
  • ちょっと言わせてもらうと、問題の根本は SQL Server 7.0 までさかのぼります。

    本来なら値が引っかからないはずのものをなぜか引っかかってしまう実装をしていたので、それに対処するために暗黙的型変換の機能が導入されたのです。

    確かに ON/OFF ができればいいのですが、それだと OFF にしたときに、正しい結果が得られないという根本的な問題に回帰してしまいます。

    暗黙的型変換ではない解決方法を MS が考え出してパフォーマンスも落とさない実装にするしか今の SQL Server では手段がありません。

    ただし、CAST/CONVERT などで問題が解決できるとワークアラウンドが出ている以上、Design Change Request(DCR) はほとんど無理です。

    仮に DCR が通すには、Business Impact が膨大であることを示す必要があります。

    現状のシステムの改修にかかる費用、影響受けるユーザ数などが、かなり膨大な数値に行く必要があります。

    Business Impact は大丈夫かも知れませんが、SQL Server の開発側が暗黙的型変換以外に方法がないと判断された場合、どんなにあがいても By Design で終わってしまいます。

    コードに手が入れられない場合は、プレミアサポートで隠しのトレースフラグを教えてもらうなどで対処する(暗黙的型変換機能の OFF などがあれば)しかないでしょう。

    後は、DCR が通ったとしても、相当時間がかかると思います。

    規模的にたぶん 6 か月以上かかるのではと推測します。

    #私が System.Net.Mail などで Content-Transfer-Encoding が sevenbit というアホな文字列になる実装を 7bit に DCR させるだけで 3 か月程度かかっています。

     

    後はハードウェアの増強で目をつぶるしかないのではと。

    Quad-Core の CPU の増強、RAM の追加、高速な HDD などなど。

    2007年4月26日 6:42
  • やまにょんです。

     

    たとえば ON/OFF だけではなく、警告イベントを残すとかその程度でもいいと思うんです。

    ただ本番運用に乗らないと (しかも規模が大きくないと) 発見しにくい、

    運用状態ではハードウェアの増強で逃げるしかない、というのでは、

    顧客にスマートな解決方法が提示できるとは思えないのです。

     

    動いてしまったものはしょうがないにして、今後のためにも何らかの手段は実装したほうが良い気がします。

    おがわさんもはせがわ様も、開発時にいちいちソースコードを目を皿のようにして眺めるより、

    そういうイベントが残ったほうがずっと解析が有利になるんじゃないかと?

     

    あとはほかの現場にてどこまでそういうニーズがあるかを知る必要があるんだと思うんですが、

    この辺は経験多い方に教えてもらいたいところ。

     

    そこまでわかれば、MVP Wish なりも MS に提案しやすいだろうと思いますし。

    2007年4月26日 7:13
  • Katmai のこともあるので話は出来そうですが、SQL CLR が OS の NLS を使って、データベースの照合順序を無視している Degin Bug があるんですが、Katmai でも無理そうな気配ですので、難しいでしょう。

    #去年の Tech・Ed 2006 Yokohama で私が公表して、SQL チームに伝えましたが、その段階でもうデザインが決定されていたようなので、むずかしいでしょうね。

    可能性があるのは隠しのトレースフラグだけですね。

    これにないといわれてしまえば、あとは SQL プロファイラが内部で使っているストアドプロシージャを使って、暗黙的型変換するイベントだけをフィルタするように設定して、そのイベントがあったらテーブルやイベントログに書き込むストアドプロシージャを書けば、そんなにリソースに負担をかけずに監視することもできます。

    SQL プロファイラと同じように一度開始すれば、停止するまで動き続けるので、ジョブのように定期的に実行する必要はないです。

    状況が把握できている(暗黙的型変換によるパフォーマンスの劣化)ので、ハードウェア増強で逃げるというのはありだとおもいますが。

    私だったら全部ストアドプロシージャで暗黙的型変換を防ぐように作り直しますが。

    2007年4月26日 7:29
  • お世話になります。

    下記のように考えてみました。

     

    問題: 「暗黙の変換」を現在の SQL Server 2005 で検知したい。

     

     この点については、Trace を実施し、イベント 'Showpaln Text (unencoded)' イベントをキャプチャし、

     フィールド TextData に、文字列 'CONVERT IMPLICIT' が発生した場合に、警告をイベントログに

     出力するプログラムを作成することにより実装可能と思われます。

     

    問題: SQL Server に、標準で警告する機能がほしい。

     

     この点については、要望をあげてみたいと思います。

     

     要望: WHERE において CONVERT IMPLICIT が発生する場合、イベントをあげてほしい。

         出力先は、イベントログ、トレースなど。

     理由: この現象に気付かずに開発を進めてしまうケースが多い。

         その結果、本番運用後、急激なパフォーマンス低下と、システム改修が必要になるケースが多い。

         開発初期段階にて、設計者、開発者が確認できる方法が必要。

     

    問題: 「暗黙の変換」を行わないでほしい。

     

     この点についても、同様に要望してみたいと思います。

     

     要望: WHERE において、CONVERT_IMPLICIT が必要になる場合、クエリーを実行せず、

         エラーとする設定を追加してほしい。

     理由: 同上

     

    このような考えでよろしいでしょうか。

    2007年4月26日 9:08
  • やまにょんです。

    かわばたさん、それが通れば最善じゃないかと思います。

    自分にも協力できることがあったら仰ってください。

    2007年4月26日 17:31
  • かわばた様

    はせがわです。

     

    #2日ちかくはずしている間に話が良い方向にまとまり始めていました。

    すばらしいです。

    1項目目については、試してみます。

    2項目目、3項目目については、理想とするところです。

     

    CONVERT IMPLICIT自体は良い機能だと思うのですが、

    その現象が分かりにくいことが問題です。

     

    よろしくお願いいたします。

    2007年4月27日 0:30
  • やまにょんさん

    #新参ものでまだフォーラムの書込みに慣れておりませんが、

    #この名前で返信するのは失礼にも感じられ・・・

     

    はせがわです。

    説明が下手なわたしに代わって話を分かりやすくまとめて頂き

    ありがとうございました。

    感謝感謝です。

     

     

    2007年4月27日 0:37
  • おがわ様

    はせがわです。

     

    いろいろコメントありがとうございます。

    私なんかは知らない話がたくさんありそうですね。

     

    ハードウェアの増強での解決は今回の場合ほとんど効果はありませんでした。

    あまり詳しくはかけませんが、私が対応した案件では、物理IO無しで

    IA64でCPUも1桁の個数ですが、かなり積んでいましたし、メモリもすでにあまっていましたから。

    APの作り直しに間に合う段階で、発見できるわかりやすい仕組みがほしいという

    ことであります。

     

    2007年4月27日 1:02
  • 現実的な話をさせてもらってもいいですか?

    まず、このフォーラムを MSKK SQL Server チームが見ていますが、確実に伝えるために PASSJ と MSKK SQL Server チームとのミーティングのときに議題の一つとして挙げさせていただきます。来月ミーティング予定ですので、タイムリーにとは行きませんが、少しでも良い方向に向かうように努力してみます。

    それと並行して Windows Server System - SQL Server の MVP として、HQ にも話をしたいとも思っています。

    ただ、これはかなりの困難(英語ベースで、開発者に理解してもらうには相当な苦労があります。)が待ち受けていますので、挫折するかもしれませんが。

    Global MVP Summit 2007 (3 月に行われた全世界の MVP 向けのイベント)のときは、MSHQ の SQL Server チームに話ができる機会があったときに、tempdb のパフォーマンスアップするためのアイディアを伝えるために、河端さんが画用紙に絵を描きながら説明してやっと伝わったなど、開発者はどうも現場の世界とは別の世界の住人が多く、意思疎通にかなりの困難が待ち構えています。

    とりあえず、できることを考えて、あの手この手で行こうかなと。

    #奥の手(MSHQ の某氏に頼み込むか、一番上にねじ込むか)使うしかないかもしれませんが。。。

     

    相当時間がかかることは承知してくださいね。

    #プレミアサポートに頼んだほうが早いかもしれませんが。

    2007年4月27日 6:18
  • このポイントでいいということなので、いくつかのアプローチを試みてみます。

     

    また、ポイント 1 について、試されましたら、ぜひ情報をいただければと思います。

    2007年4月27日 9:22
  • はせがわです。

    >問題: 「暗黙の変換」を現在の SQL Server 2005 で検知したい。

    > この点については、Trace を実施し、イベント 'Showpaln Text (unencoded)' イベントをキャプチャし、

    > フィールド TextData に、文字列 'CONVERT IMPLICIT' が発生した場合に、警告をイベントログに

    > 出力するプログラムを作成することにより実装可能と思われます。

     

    #警告をイベントログに出力するように実装したわけではありませんが、、、

     

    トレースにて暗黙の変換の検知をする方法を考えてみました。

    'Showpaln Text (unencoded)' のイベントフィルタで'CONVERT IMPLICIT' を定義してしまうと

    原因となるクエリがわかりにくくなります。

    よって下記の2つのイベントは最低限取得してみました。

    ・Showpaln Text (unencoded)

    ・SQL:BatchCompleted

    トレース結果は「CONVERT_IMPLICIT.trc」というファイルに保存することとして、

    トレースログから暗黙の変換を検索するとこんな感じになります。(あまりよくないSQL文かも)

    Code Snippet
    select StartTime,Reads,Writes,CPU,Duration,TextData
    FROM fn_trace_gettable('c:\MyData\CONVERT_IMPLICIT.trc', default)
    where StartTime IN(
    SELECT StartTime
    FROM fn_trace_gettable('c:\MyData\CONVERT_IMPLICIT.trc', default)
    where TextData Like '%CONVERT_IMPLICIT(n%')
    and EventClass = 12
    go

     

    UNICODEへの暗黙の変換が問題となりやすいと感じているので、”%CONVERT_IMPLICIT(n%”で検索しています。

    トレースの実行に関しては負荷をできるだけ抑えるため、”sp_trace_setstatus”で実行するようにして

    みました。

    コメントを頂いたときにSQL2000でも検出できるのではと一瞬思ったのですが、

    SQL2000の場合、暗黙でも単なるCONVERTとなってしまうため、機械的に判別するのは

    難しいと感じられました。

     

    参考までに本日サンプルにしてみた暗黙の変換は、20万件のレコードで

    Where句を使用するSELECT文。

    暗黙の変換発生:論理読み込み753,経過時間403ms

    暗黙の変換なし:論理読み込み6、経過時間0

    でした。

    select @@versionの結果は9.00.3054.00。

     

    本件のスレッドの先頭であげたクエリでの確認方法とトレースでの確認方法の効果の違いは、

    クエリの発生単位に確認ができることですね。

    実行プランは同じでWhere句にわたすパラメータが異なるだけで、現象が違うため、

    どのパラメータの時に影響が大きいのかわかったほうがよいかもしれません。

    実行プランでは、CONVERT_IMPLICITがあっても、ほとんど影響を受けないケースも

    ありましたので、IO数や経過時間を平均値でみてしまうと、見落とします。

    よって、トレースで個別にみるか、DMVで最大IO数や最大経過時間などを確認した方が

    よさそうです。スレッドの先頭であげたクエリを少し変更しました。

     

    どなたか、業務APのクエリで引っかかる方がいたら、報告して頂けると参考になります。

    いかがでしょうか?(うまくサンプルが投稿できていないかもしれません。)

    2007年5月1日 7:59