追加コスト 0 で行う SQL Server のパフォーマンス改善・ボトルネック解消 その9
こんにちは、nagino です。
この不況ということで、仕事が無くて窓際族は暇です。
いやまあ、首切りに備えなければいけないのですが（笑）。
# 笑っている場合ではない。。。
それはさておき、今回は分離レベルについてです。
分離レベルとパフォーマンスの関係を理解するには、ロック待ちの挙動を考えると分かりやすいかと思います。
通常 SQL Server では分離レベルが Read Committed になっています。
これは、SELECT する際に共有ロックをかけます。
共有ロックがかかっていると、共有ロックは重ねがけできますが、排他ロックがかけられません。
SQL Server は通常行レベルロックを行いますので、通常共有ロック自体は問題になりません。
しかし、全行検索や、クラスタ化インデックスが存在しないテーブルの SELECT、大量のロックによるロックエスカレーションなどが発生した場合、テーブル単位で共有ロックがかかることがあります。
そうすると、そのテーブルのどの行も排他ロックがかけられず、更新処理が待たされます。
良くあるのは、初期表示で全件表示しているシステム、あるいはもっと困るのは全件 SELECT していてプログラム内でページングやフィルタリングしているシステムで、そのテーブルの更新処理がやたら遅い、あるいは誰か更新している時に表示が遅い、という形でのパフォーマンス劣化です。
本質的には、プログラムを適切に修正することですが、そのデータベース全体でダーティリードしても差し支えないようなデータ、ないしはそういう前提で運用可能なシステムの場合、データベースの分離レベルを Read Uncommitted にしてしまうことで、共有ロックをかけなくなり、パフォーマンスが改善することがあります。
通常はダーティリードしても構わない処理毎に、プログラム側でトランザクションレベルで分離レベルを設定するのが筋ですが、処理がストアドプロシージャになっている場合は、ストアドプロシージャ内で分離レベルを設定することができますので、その場合は DB 管理者でなんとかすることができます。
まあ、建前上そうであっても、実際はプログラムでどのようにストアドプロシージャを使用しているか等々を考えると、なかなか手を出せないと思いますので、通常は開発時に適切に考慮するのが筋ですけれども。。。
そもそもプロシージャ化されているのであれば、クエリ自体をチューニングするのが本筋ですし。。。。。。追加コスト 0 で行う SQL Server のパフォーマンス改善・ボトルネック解消 その8
こんにちは、nagino です。
今日は FILL FACTOR と （PAD_INDEX と）ページ分割についてです。
SQL Server はデータやインデックスを 8KB のページという単位で管理します。
ですので、1 行が 5KB あるような巨大なデータは、ページあたり 1 行しか格納できず、残りの 3KB は使用できない空き領域となります。
ディスクで言うセクタに近いものになります。
さて、例えば 1 行が 800B あるデータが 1 ページに 10 行格納されているとします。
ここにデータを挿入する場合、ページに入りきらないため、データのおよそ半分を新しいページに移動します。
これがページ分割です。
他にも可変長列のデータが更新で大きくなったときにも、ページ分割は発生します。
ページ分割は、おおよそ 4KB ほどのデータの移動が発生し、またページの確保などが発生するため、パフォーマンスがその分劣化します。
一方、データを末尾に追加する場合、FILL FACTOR で指定した割合までしかデータを追加せず、空き領域を残します。
このため、ある程度空き領域を各ページに残しておくことができるため、その後データの挿入や可変長列の更新があっても、ページ分割が発生する可能性が減少します。
INSERT でも、データの追加される位置によって動作が異なるということになります。
ただし、FILL FACTOR を大きく指定すれば、同じデータ量でも使用するページ数が増えるため、その分データキャッシュが増えることでメモリを消費し、ディスク I/O が増加することでパフォーマンスが劣化します。
また、クラスタ化インデックスが IDENTITY の列の場合、データの挿入は起こらないため、可変長列の更新によるページ分割しか起こりません。
特にマスタや過去データのように、更新がほとんど行われないようなデータの場合は、FILL FACTOR をデフォルトの 0 ないし 100 にしておくほうがパフォーマンス上優れます。
一方、データの挿入や可変長列の更新が頻繁に発生する場合は、FILL FACTOR を若干押さえた値にすることで、更新処理のパフォーマンスの改善が期待できます。
ちなみに、インデックスの再構築時に PAD_INDEX を指定することで、インデックスのページにも FILL FACTOR と同様の充填率を指定することができます。
データの追加・挿入の特性、可変長列の更新の特性、検索と更新のバランス、ディスクとメモリのリソース量に応じて調整が必要な、ちょっと難しい領域ですが、OLTP 系でデータの更新処理のパフォーマンスが求められる際には検討の価値があるパラメーターになります。
まあ、たいていの場合は検索重視のため、デフォルトの 0 のままが最適なのですが・・・。
追加コスト 0 で行う SQL Server のパフォーマンス改善・ボトルネック解消 その7
こんにちは、nagino です。
SQL Server のメモリの使用方法について、ちょっとした基礎知識と、パフォーマンスの側面についてです。
SQL Server は、デフォルトではメモリをどんどんと使用していきます。
OS から見てメモリ不足にならない限りは、一度使用したメモリも開放しません。
これはデータベースのファイルと同じ考え方で、メモリ取得・開放の処理のオーバーヘッドを避けるためです。
このため、SQL Server を稼動している OS では、SQL Server に処理を行わせるとどんどんと空きメモリが減っていき、以後負荷をかけていない状態でも空きメモリが数 MB という状態が続きます。
一方、OS は OS でページファイルを利用した仮想メモリの仕組みがあります。
サーバー OS の場合はサイズ固定で小さくしていることも多いかと思います。
ですが、SQL Server と他のサービスやアプリケーション（例えば IIS など）を同居させていると、SQL Server がメモリの開放に積極的で無いため、他のサービスやアプリケーションがスワップすることがあります。
こうなるとサーバ全体としてパフォーマンスが劣化します。
ですので、他のサービスやアプリケーションが同居しているサーバーでスワップが顕著に見られるサーバーでは、SQL Server のメモリ使用量の最大値を設定することで制限したほうが良いことがあります。
追加コスト 0 で行う SQL Server のパフォーマンス改善・ボトルネック解消 その6
こんにちは、naginoです。
重要性、汎用性、効果について考慮せずに思いつくまま書き連ねる本シリーズも、いつの間にか 6 回目です。
ぶっちゃけ、設計者が設計段階でお金がかかるパフォーマンス関連の事項を、適切な余裕を持たせて設計していれば、運用段階で予算 0 でパフォーマンス改善に苦しむことも無いわけで、そもそも設計を云々、予算を云々としてしまえばそれまでなのですが、なかなかそうも行かない現実も多いわけですよね。
また、設計者は設計者で、パフォーマンスに関連する事項を全て適切に、というのはなかなか困難でして、いつか機会があればそちらも考えてみたいところです。
さて、今日は統計、統計情報について考えて見ます。
SQL Server はコストベースのオプティマイザを使用しているため、コストが推測できる必要があります。
そのため、テーブルの列毎に、値の分布の傾向（統計情報）が必要になります。
まあ、統計情報が無くても全行取得すれば値の分布の傾向は把握できますが、それをやってしまうと時間がかかりすぎるため、サマリともいえる統計情報を使用します。
さて、この統計情報ですが、データの追加や更新が行われると実体と乖離していくため、適宜統計しなおしています。
色々条件などもありますが、初期設定では概ね以下の頻度で自動更新されます。
● 500 行以下のテーブルでは、500 回データが更新される都度
● 500 行より多い場合、500 + (前回統計情報取得時の行数の20%) 回データが更新される都度
ということは、バッチなどで大量のデータ更新を行うと、その途中で統計情報の自動更新が行われます。
一方で、600 行のテーブルでは 620 回データ更新が行われると統計情報の更新が行われるため、統計情報と全く一致しない状態でも更新されないことがありえます。
また、統計情報の更新はデータの一部をサンプリングして取得する必要があり、負荷がかかります。
一方で実態と乖離した統計情報ではオプティマイザがパフォーマンスが良くない実行プランを選択することがありえます。
このため、テーブルの更新処理の傾向や処理傾向に応じて以下の対応をとることで、パフォーマンスが改善することがあります。
● 夜間バッチなどの大量更新作業中は自動更新を停止し、終了後に一括更新
● 更新頻度がそれほど高くないテーブルは夜間に一括更新
ただ、統計情報のメンテナンスを怠るとオプティマイザが適切な実行プランを選択できなくなるので、パフォーマンスが劣化します。
自動更新は有効にしたまま、夜間に必要に応じて手動更新するというのが無難ではあります。
まあ、実際のところ、統計情報関連のパフォーマンス面のトラブルは、適切なメンテナンスが行われていなかったというケースが多いので、個人的にはあまり触らないほうが良いと思います。
バッチによる大量更新の際は、場合によってはある程度効果があるのですけれども・・・。
追加コスト 0 で行う SQL Server のパフォーマンス改善・ボトルネック解消 その5
こんにちは、nagino です。
思いつきで書いている本シリーズですが、意外とまだネタがあるようです。
実際、ほとんどの処理がタイムアウトでお呼ばれ ⇒ インデックスの再構築 ⇒ すっかり解決・・・実は運用開始してから 1 年以上たつのに 1 度もインデックスのメンテナンスをしてなかった、なんていう笑い話のようなこともあったりするので、現実は小説より奇なり、です。
さて、今回はファイルの自動拡張のお話です。
データベースには、日々データが登録・修正・削除されます。
可変長の型もありますので、データ量は日々増減しているわけです。
そのため、データが格納されている mdf ファイルや ndf ファイルといったデータファイルのデータ量は日々変化しています。
ここで、HDD 上の記憶領域について OS と SQL Server のレベルでは扱いが異なることを理解している必要があります。
具体的には、以下のような 4 パターンがありえます。
-1. OS から見て空き領域
-2. OS から見てデータファイルとして使用中、SQL Server から見て空き領域
-3. OS から見てデータファイルとして使用中、SQL Server から見て使用領域
つまり、データファイルの中身は全てデータが記録されているとは限らず、データが書き込まれていないけれどもとりあえずファイルの一部となっている領域があります。
これは、ファイルサイズの変更にはファイルシステムの管理上のオーバーヘッドがかかり、それを避けるためにあらかじめデータファイルのサイズを大きめに確保しています。
さて、データベース毎に自動拡張と自動圧縮というパラメータがあります。
自動拡張というのは、データファイルの空き容量が不足した際にあらかじめ決められたサイズにファイルサイズを拡張します。
自動圧縮というのは、一定時間間隔でデータファイルの空き容量を開放します。
（SQL Server 2000 は 30 分間隔のようです。2005 以降はちょっと記載が見当たりません。）
つまり、これらは OS から見たときにデータファイルを拡張する、縮小するという処理になります。
通常のデータベースであれば、データは概ね増えていきますので、デフォルトでは自動拡張は有効、自動圧縮は無効となっています。
DELETE や UPDATE によってデータが減っても、いずれは他の増えたデータが記録されるので、ファイルとして領域を確保したままにしているということです。
このデフォルト設定は一番無難な設定となっていますが、自動拡張のオーバーヘッドは存在します。
そこで、パフォーマンスをあげるためにあらかじめファイルサイズを大きく確保しておき、自動拡張は有効にしておくものの万が一のための安全策として位置づける、という考え方があります。
ではどのぐらいのサイズにするか、というのは、データの増加傾向をみて判断するしかありません。
また、メンテナンス時や負荷が低いときに、必要に応じてファイルサイズを手動で拡張しておく必要があります。
一方自動圧縮は、パフォーマンスを考えると基本的には無効のままが一番良いです。
ディスクが不足していていかんともしがたいときの、様々な弊害覚悟での奥の手、という位置づけが妥当かと思います。
というのも、圧縮処理でパフォーマンスは悪化する、開放してもいずれは再拡張が行われる際にやはりパフォーマンスが悪化する、再拡張されるので一時的なディスク確保にしかならない、繰り返せばデータファイルが断片化していく、といった具合の弊害があります。
ちなみに自動拡張と自動圧縮は以下の KB がありますが、パフォーマンスを最速に、ということを考えると自動拡張やデータファイルのサイズの初期値は調整したほうが最善になると思いますのでご注意を。
http://support.microsoft.com/kb/315512/ja
なお、あんまりにもデータファイルのサイズを大きくしすぎると、デタッチしたファイルがその分大きくなりますので、デタッチ、アタッチを利用されている場合はご注意ください。
バックアップでは未使用領域は省かれるので、その場合は大丈夫です。