DO’s&DONT’s #6: 絶対にやってはいけないこと – ストアドプロシージャ内でのパラメータ値の変更

神谷 雅紀SQL Server Escalation Engineer   ストアドプロシージャに渡されたパラメータが、ストアドプロシージャ内のクエリで参照されている場合、パラメータ値をストアドプロシージャの中で変更してはいけません。 悪い例   CREATE PROCEDURE GetDetails (@BranchCode smallint default 0)ASBEGIN– 支店コードが指定されていない場合は本店IF @BranchCode = 0 THEN SELECT @BranchCode = 100  — パラメータ値を変更している! SELECT * FROM SalesDetailsWHERE EntryDate = GETDATE() and BranchCode = @BranchCodeEND   なぜ?   ストアドプロシージャは、渡されたパラメータの値を用いて、コンパイル及び最適化されます。これは、parameter sniffing と呼ばれます。 上の例では、ストアドプロシージャ GetDetails 内の SELECT は、ストアドプロシージャが呼び出された時に設定されていたパラメータ値の時に最も効率的な実行プランが選択されます。もし、@BranchCode に 0 が指定されて GetDetails が呼び出された場合には、SELECT は @BranchCode =…


DO’s&DONT’s #4: やらない方がいいこと – クエリの 条件句 (WHERE や JOIN ON 等) で参照されている列の加工

神谷 雅紀SQL Server Escalation Engineer   パフォーマンスに優れたクエリを書こうとするのであれば、クエリの WHERE 句や結合句で参照されるテーブルの列を関数などで加工してはいけません。   悪い例 SELECT * FROM Sales WHERE convert(char(10),EntryDate, 111) = ‘2010/12/01’ SELECT * FROM Sales WHERE FistName + Last Name = @name   なぜ? その列にインデックスがあったとしても、そのインデックスによる行の絞込みが行えないからです。 例えば、上の例の最初のクエリでは、以下の処理が必要です。 テーブルから行を読み取る。 列 EntryDate の値を convert 関数に渡す。 convert 関数から返された値を定数 ‘2010/12/01’ と比較する。 比較の結果、一致すれば、その行をクエリ結果へ入れる。一致しなければ 1 へ戻り次の行を処理。 このように、クエリ条件に一致しているかどうかを判定するためには、値を関数に渡す必要があり、関数に渡すためには必ず行を読み取ることが必要になるため、EntryDate がインデックスの先頭列であったとしても、そのインデックスを使用して、読み取り範囲を ‘2010/12/01’ に限定することができず、テーブルの全行を読み取る必要があります。読み取り量が多いということは、処理するデータ量が多いということであり、処理するデータ量が多ければ、処理完了までには時間がかかることになります。つまり、クエリの実行時間が長くなります。   どうするか? クエリが同じ結果を生成し、かつ、列の値を加工しない別の方法を検討します。…


DO’s&DONT’s #3: やらなければいけないこと – 非典型的パラメータ値が存在する場合の再コンパイル

神谷 雅紀 SQL Server Escalation Engineer   非典型的パラメータの問題   非典型的パラメータ値に起因する問題があります。これは、ストアドプロシージャやパラメータ化クエリなどに、典型的ではない、普段使わないような特殊な値のパラメータ値が渡され、その値を用いてコンパイル及び最適化された実行プランがキャッシュされることによって引き起こされる問題です。この問題は、パラメータとして渡された値によって、検索範囲や結果セットが大きく変わる場合に発生します。このような実行プランは、parameter sensitive plan と呼ばれることがあります。また、このような非典型的なパラメーターに起因する問題は、atypical parameter problem と呼ばれることがあります。   それによって、何が起こるのか?   パフォーマンスの問題が発生します。 以下のようなケースを考えてみましょう。 「休業日のデータを検索した。その後、普段は時間のかからない営業日のデータ検索に時間がかかるようになった。」 「本店で、全支店のデータ検索が行われた。その後、支店別のデータを検索すると、いつもに比べて非常に時間がかかる。」 ストアドプロシージャやクエリが実行される時、まず、それらの実行プランが既にキャッシュされているかどうかが確認されます。キャッシュされていれば、キャッシュされている実行プランを用いて、処理が実行されます。キャッシュされていなければ、指定されているパラメータ値を使用して、コンパイル及び最適化がおこなわれ、実行プランが生成されます。その実行プランはキャッシュされます。 先の例では、キャッシュされている実行プランがなければ、「休業日のデータ検索」のためにストアドプロシージャを実行した時に、「休業日のデータ検索」に最適な実行プランが生成され、その実行プランはキャッシュされます。それ以降、このストアドプロシージャが実行される時には、キャッシュされている「休業日のデータ検索」に最適な実行プランを用いて実行されます。その実行プランが「営業日のデータ検索」にも最適であればパフォーマンスの問題は発生しません。しかし、「休業日のデータ検索」には最適であっても、「営業日のデータ検索」には最適でなければ、言い換えれば、「休業日のデータ検索」の時に指定されたパラメータが非典型的であれば、「営業日のデータ検索」にはパフォーマンスの問題が発生することになります。   どのように対応するか?   非典型的なパラメータが存在すると予想される場合には、実行ごとにコンパイル及び最適化することを指定します。そのための方法としては、以下の方法があります。 ステートメントに OPTION(RECOMPILE) を指定する。 (SQL Server 2005 以降のみ) SELECT * FROM tabname WHERE colname = 123 OPTION(RECOMPILE) ストアドプロシージャの場合には、 ストアドプロシージャを WITH RECOMPILE 指定で作成する。 CREATE PROCEDURE p (@param1 int) WITH…


DO’s&DONT’s #2: 絶対にやらなければいけないこと – データ型を一致させる

神谷 雅紀 SQL Server Eascalation Engineer クエリを書く場合、比較を行うデータのデータ型は一致させておくことが必要です。   なぜ? データ型が一致していない場合、必ず、どちらかのデータがもう片方のデータのデータ型に変換された後に比較が行われます。つまり、SQL Server は、データ型変換という余分な処理を行わなければならなくなります。また、それだけではなく、データ型を一致させるために、本来読み取る必要のないデータを読み取らなければならなくなることもあります。   どのような影響があるのか? クエリのパフォーマンス悪化です。比較するデータのデータ型が一致していない場合、データ型の優先順位に従って、優先順位の低いデータ型のデータが、優先順位の高いデータ型に変換されます。   データ型の優先順位 (Transact-SQL) http://msdn.microsoft.com/ja-jp/library/ms190309.aspx   例えば、以下のクエリを考えます。   CREATE TABLE TableA (C1 varchar(10)) GO SELECT * FROM TableA WHERE C1=N’XXX’ GO   SELECT の WHERE 句に指定されている C1 は VARCHAR(10) ですので非 Unicode 型ですが、比較対象の N’XXX’は Unicode 型です。 この場合、何が発生するのでしょう?実行プランを見てみます。   |–Table Scan(OBJECT:([testdb1].[dbo].[TableA]), WHERE:(CONVERT_IMPLICIT(nvarchar(10),[testdb1].[dbo].[TableA].[C1],0)=[@1]))  …


SQL トレーススクリプトの作成、実行 (SQL Server 2000)

神谷 雅紀SQL Server Escalation Engineer   SQL Server 2000 トレーススクリプトの作成方法です。SQL Server 2005, 2008, 2008 R2 については以下を参照して下さい。 http://blogs.msdn.com/b/jpsql/archive/2011/01/26/sql.aspx   1. Profiler の起動 [スタート] – [すべてのプログラム] – [Microsoft SQL Server] – [プロファイラ] から、Profiler を起動します。 2. 新しいトレースの作成 [ファイル] メニュー – [新しいトレース] を実行します。 続いて、SQL Server  名を指定し、SQL Server へ接続します。 3. トレースプロパティの指定 [全般] タブで、[ファイルに保存]、 [ファイルロールオーバーを有効にする] 、「サーバーが SQl Server トレースを処理する」をチェックします。また、トレースの最大ファイルサイズを指定します。 4. トレース対象イベントの指定 [イベント] タブで、トレースしたいイベント名を選択します。 5. データ列の選択 [データ列]…


SQL トレーススクリプトの作成、実行 (SQL Server 2005 以降)

神谷 雅紀 SQL Server Escalation Engineer 以降の画面は、SQL Server 2008 R2 のものですが、SQL Server 2005 以降のバージョンでは同様の手順、同様の画面です。 SQL Server 2000 については、以下を参照して下さい。 http://blogs.msdn.com/b/jpsql/archive/2011/01/26/sql-sql-server-2000.aspx 1. Profiler の起動 [スタート] – [すべてのプログラム] – [Microsoft SQL Server 2008 R2] – [パフォーマンスツール] – [SQL Server Profiler] から、Profiler を起動します。 2. 新しいトレースの作成 [ファイル] メニュー – [新しいトレース] を実行します。   続いて、SQL Server  名を指定し、SQL Server へ接続します。   3. トレースプロパティの指定…

1

DO’s&DONT’s #1: やらない方がいいこと – 運用環境で、Profiler GUI を使用してトレースする

神谷 雅紀 SQL Server Escalation Engineer SQL Server において、やらなければいけないこと / やってはいけないことを、Do’s&Don’t’s として、シリーズで紹介したいと思います。初回の今回は、Profiler GUI についてです。 一定量の負荷がある環境において、Profiler GUI を用いてリアルタイムモニタをしたり、テーブルにトレースデータを保存したりすることは、パフォーマンスなどの問題を発生させる要因となります。絶対にやってはいけない、というほどではありませんが、ベストプラクティスとして、やらない方がよいことは確かです。   なぜ?   多数のクライアント接続があり、多数のリクエストを受け付けている SQL Server が生成するすべてのイベントをひとつの Profiler で受け取ることになるため、Profiler がボトルネックとなり、SQL Server 全体のパフォーマンスが著しく悪化し、実行中のクエリがタイムアウトする、ログインがタイムアウトする、場合によっては、クラスタやミラーリングのフェールオーバなどを招くこともあります。Profiler GUI は、ひとつの普通のクライアント接続を通じて、SQL Server からトレースイベントを受け取ります。当然、その接続で転送できる量は限られており、また、それをグラフィカル表示しなければならないため、SQL Server 側で生成されるイベント量が、Profiler が処理できるイベント量の限界を超える状況になると、SQL Server 側では新たなイベントを Profiler に渡せなくなり、イベントを生成している一般のクライアントは、イベント書き込み待ち状態になります。 さらに、Profiler を経由して SQL Server のテーブルへイベントを書き込んでいる場合は、SQL Server → Profiler → SQL Server とイベントデータが渡されることになり、パフォーマンスへの影響はより大きくなります。 テスト環境など負荷を制御できる環境や、非常に負荷の低く、ほとんど処理が行われていないような環境を除いて、何らかのトラブルシューティングのために Profiler GUI…

1