【SQLServer】レスポンス対応

2018年12月13日

目次

【SQLServer】レスポンス対応(随時更新中)

レスポンス対応における原因切り分け方

レスポンス改善のための全体方針としては、ボトルネック(一番遅いところ)を改善することになります。

したがって、すぐに作りが悪いと疑うことは間違いです。

”処理が遅い”という場合には、いきなりSQL やアプリの改修ではなく、「どこが遅いのか」を調査しましょう。

 

SQLチューニングによる影響について

チューニングによる影響

SQLをチューニングすると、以下事象が発生する可能性があります。

・伝票入力と在庫更新処理が遅い(在庫更新処理は遅いと気がついていない状態)
⇒ 伝票入力処理を改善すると、在庫更新処理が遅いことも判明し、システム全体として遅くなる。

チューニング対応順序

レスポンス対応順序基本方針

レスポンス対応の課題に応じた対応方針

 

更新処理によるテーブルロックによりシステム全体処理が遅くなる

ロック範囲を明示的に指定するか、ロックエスカレーションを設定する方法があります。

 

テーブルロックから行ロックへ

 

ロックエスカレーション無効化

考え方:ロックするならテーブル全体をロックした方が速い。ロック範囲をエスカレーションするというもの。

SQLServerはデフォルト設定の場合、ロックエスカレーションすることがあります。

テーブルあたりのデータ量が少ない場合(300万件未満程度の場合)には、行ロックすることが多いですが、ある程度多くなると、テーブルロックしやすくなります。

※ デフォルト設定の場合SQLServer内部の統計情報やオプティマイザに依存するものなので、明示的に指定した方が良い場合があります。

まとめ処理など更新対象行が多くて問題ない場合には、ロックエスカレショーンは有効ですが、更新、参照頻度が高いテーブルの場合、ロック範囲が狭い方が良い場合があります。

以下により、ロックエスカレーションは無効化されます。

画面実行とManagementStudio等による処理時間の違い

ストアドプロシージャ等で発生しやすい事象です。

SQL内部に原因がある場合もありますが、定義するCREATE文のSETによる定義等のオプションが宣言されていない場合に発生しやすい事象となります。

 

 

 

カーソル処理の見直し

カーソルオプションのFAST_FORWORDが漏れており、処理速度が遅いことがあります。

カーソルオプションの見直し

 

参照ロックの待ちを緩和

オプションで対応

WITH(NOLOCK)

ダーティリード(更新途中の中身を参照可)が発生するので、使用時にはダーティリードが発生して良いか検討が必要になります。

デッドロック対策

先にロックを取得

SELECT時に行排他ロックを取得し、デッドロックを回避する方法になります。処理時間が掛かる機能の場合は、ロック専有時間が長く他処理に影響するので使用しないでください。

SELECT [列名]
FROM [テーブル名] WITH (ROWLOCK XLOCK)

 

定義変更によるチューニング

クラスタ化インデックス最適化

 

非クラスタ化インデックス追加

 

ストアドプロシージャ定義確認

ストアドプロシージャ中では、パフォーマンス向上のためオプションSET NOCOUNT ON の指定を推奨します。

SQLチューニング

インデックスの効かないSQLの改修

左辺に計算式を記述しているケース

インデックスを作成しているカラムに対し、計算式や関数を指定するとインデックスが効きません。

特にISNULL、NVLなどのnullを変換する関数は使用しがちの為、注意が必要です。

例:

— 計算式左辺に書くケース
SELECT T1.売上番号
FROM 売上 T1
WHERE T1.売上数量 – 1 = 20

–計算式右辺に書くケース
SELECT T1.売上番号
FROM 売上 T1
WHERE T1.売上数量 = 20 + 1

OR条件を指定しているケース

インデックスを作成しているカラムに対し、WHERE句でOR条件を指定するとインデックスが効きません。

対処として、in句やUNION ALLで実装することを検討してください。

※ UNION は重複排除処理があり、内部的に、ソートを行いながら重複排除するので遅いです。

例:

–ORを使うケース
SELECT T1.社員コード
FROM 社員マスタ T1
WHERE
T1.部門コード = 20 OR T1.部門コード = 30

–UNION ALLを使うケース
SELECT T1.社員コード
FROM 社員マスタ T1
WHERE
T1.部門コード = 20
UNION ALL
SELECT T1.社員コード
FROM 社員マスタ T1
WHERE
T1.部門コード = 30

結合時に件数を減らしていないケース

データ量が多くないテーブルに効果は感じられませんが、データ量が多いテーブルの場合、結合条件にWHERE句で指定するような条件を指定することで、結合にかかるコストを削減できます。

例:

–結合時に件数を減らしているケース(売上明細側を絞る)
SELECT T1.社員名,T2.売上金額
FROM 社員マスタ T1
JOIN 売上明細 AS T2
ON T1.社員コード = T2.社員コード
AND T2.社員コード = ‘0001’      — ★追加★
WHERE
T1.社員コード = ‘0001’

副問合せを多数利用しているケース

データ量の多いテーブルを副問い合わせなどで何度も使用すると、パフォーマンスが悪化します。同じような副問い合わせがあればWITH句を使用して1箇所にまとめ、レスポンス改善させましょう。

 ※ データ量が少ない場合や、抽出条件でIndexSeekできる場合には効果が現れません。以下の例のような同じ条件の場合は、WITHを利用して使い回すことで、改修漏れを防ぐことにもつながります。

例:

SQLを分けた方が良いケース

1回のSQLで取得した方が良いイメージがありますが、大量のデータを扱うSQLを実行するとサーバーに負荷がかかってしまい、かえって遅くなってしまうことがあります。

メインのSQLと結合で取得していた部分を2回のSQLで実行して取得し、マージするようなやり方でレスポンス改善の効果はあります。

※ SQLの組み方やデータ構造にもよりますので一概に言えないです。

件数を数えるケース

COUNT(*)ではなく、COUNT([インデックスが存在する列名])を使用してください。(*)の場合、インデックス利用ができない場合がありますので、基本的に、列名指定とします。

集計やソートするケース

集計(GROUP BY)や、ソート(ORDER BY)の場合もインデックスを利用できます。DBのバージョンによっては利用できない場合もあるようです・・・が意識してください。

列番号を使用しているケース

恐らく使用している人はいないと思いますが、「ORDER BY 1, 2」と記述すると、列名読み替え処理が発生するので使用しないでください。

テーブル名に別名をつけるケース

別名をつけた方が解析処理が速いそうです。どのテーブルにどの列があるかの判定処理が内部で行われるようですが、別名指定すると、その判定処理をスキップできるので速いとのことです。

IN句には、最もありそうなキーを左寄せするケース

IN句は左から右に処理をしますので、左にありそうなものを指定してください。

1件でもヒットすると、処理がTrueで終えるので無駄な処理を省略できます。

 

条件に「IS NULL」を使用するケース

インデックスには「NULL」は存在しません。よって、NULLを指定するとインデックスが効きません。

条件に否定を使用するケース

否定形の構文にはインデックスが使用できません。

例:

(<>, NOT EQUAL, NOT IN)に関するもの

・ [列名] !=   ‘XXX’
・ [列名] <>  ‘XXX’

LIKE検索を使用するケース

前方一致検索のみインデックスを使用できます。

後方一致は並び替え関数等を活用するとインデックスが効くかもしれませんが、試してません・・・。並び替えた列を作っておけば最終手段として利用できます。

テーブル結合について(★結合オプション★)

テーブルを結合する場合、結合の仕方というものがあります。このアクセスパスがパフォーマンスにかなり影響します。

ネステッドループ結合

ネステッドループ結合は、ループしながら結合する方法です。

メインテーブルの検索結果の「結合列」をキーとし、もう一方のテーブルをインデックススキャンしながら結合する方法です。最初に検索するテーブル(ここではメインテーブル)を駆動表といい、駆動表が小さいほどループ回数が減るのでパフォーマンス向上します。

ハッシュ結合

ハッシュ結合は、メモリに保持しながら結合する方法です。

駆動表(メインテーブル)の検索結果をメモリにキャッシュ(保持)し、もう一方のテーブルの全件を検索、メモリから最初のテーブルの情報にアクセスする方法です。メモリに駆動表の情報が収まる場合にはネステッドループ結合より速いことがあります。

ソート・マージ結合

ソート・マージ結合はテーブル全体処理には向いている結合方法です。

両方のテーブルをフルスキャンして、ソート、マージするという方法です。テーブルの大半のレコードを検索対象とする場合には有効になります。基本的には遅いです。

おすすめは?

以下のアクセスパスが適しています。

すべてのレコードを取得する方のテーブルをメインテーブルとしてフルスキャンし、もう一方のテーブルの結合列にあるインデックスを利用し、ネステッドループ結合をしましょう。結合される側はインデックスがあることが前提です。

インデックスの作成基準

カーディナリティが高い列に対して作成することが望ましいです。

カーディナリティとは

列に登録されているデータの種類がどのくらいあるのか(値の種類)を、カーディナリティという。

例:

カーディナリティが低い場合

性別の場合男と女の二種類の場合、データの種類がテーブルのレコード数に比べて二種類と少ないことになります。このことを カーディナリティが低いといいます。

カーディナリティが高い場合

顧客番号ならたくさんの番号(種類)が存在することになります。
データの種類がテーブルのレコード数に比べて多い場合、 カーディナリティが高いといいます。

複合のインデックスを作成する時は、 カーディナリティの高い順に貼っていくことが重要になります。直感的に顧客番号の方が検索する回数が多そう、よって先に絞った方が良い、といった考えで良いです。

 

チューニング結果の反映

統計情報を更新

データ量に変化が起きた場合(増減)や、内容の分布にばらつきが発生してきた場合には、件数や分布情報を統計情報として更新(反映)することが必要です。

これを行わなければ、反映されない為、少ないデータのときの検索の仕方がベストとSQLServer(オプティマイザ)は思い込んでいます。

オプティマイザの制御を強制的に変更したい場合には、よく利用されているオプション(WITHなど)を活用します。
最初から使用するのはNGです。

原因別調査方法

未分類

Posted by 管理者