SQL に関する Q&A: データを管理しやすくする
今月は、SQL Server のコラムニストが、異なるデータを解析したり、戦略を立てたり、ファイル グループを使ってデータを管理する方法を紹介します。
Paul S. Randal
数字は嘘を付かない
Q. 一部のデータベースの I/O 待機時間を計測しようとしていますが、動的管理ビュー (DMV) とパフォーマンス モニターのカウンターで異なる結果が返されます。これらの数値が同じでない理由を教えてください。
A. これら 2 つの手法では、sys.dm_io_virtual_file_stats という DMV (この DMV の使用方法の詳細については、私のブログ記事 (英語) を参照してください)、パフォーマンス モニターの Physical Disk オブジェクトの Avg. Disk sec/Read カウンターと Avg. Disk sec/Write カウンターを使用しています。この 2 つのメカニズムで異なる結果が返されることは十分あり得ます。
DMV では、読み取りと書き込み処理の累積の停止時間や待機時間を計測します。この計測は、データベースがオンラインになったときから (通常 SQL Server インスタンスの前回の再起動から) すべてのデータベースのすべてのファイルに対して実行されます。この合計を、読み取りと書き込みの回数で割ることで、読み取りと書き込みの I/O 待機時間の平均を取得しています。この平均は、最初に DMV をクエリしたときから、そのデータベースがオンラインになったときまでの期間について算出されたものです。
パフォーマンス モニターのカウンターでは、より短期間の平均を返します。詳細は、Windows Server コア チームが執筆した 2 つの優れたブログ記事「Windows Performance Monitor Disk Counters Explained (Windows パフォーマンス モニターのディスク カウンターの説明、英語)」と「Measuring Disk Latency with Windows Performance Monitor (Perfmon) (Windows パフォーマンス モニター (Perfmon) を使ったディスク待機時間の測定、英語)」を参照してください。パフォーマンス カウンターでは、ディスクの読み取りと書き込みの待機時間の瞬間的な値が表示されます。このように、この 2 つの待機時間の測定手法は大きく異なるため、異なる結果が返されることがあります。
DMV では、SQL Server のデータベース ファイルの待機時間のみが測定されます。パフォーマンス モニターでは、データベース ファイルがあるボリュームのすべての I/O の待機時間が測定されます。共有記憶域環境では、SQL Server 以外の多数のファイルが I/O 待機時間の負荷の一因になっていることが考えられます。これにより、パフォーマンス モニターに関しては、多数のファイルの I/O の測定しているため、I/O 待機時間の平均が低くなる可能性があります。DMV では、パフォーマンス カウンターよりも、より少ないファイルの I/O を測定しているので、DMV の平均は、パフォーマンス カウンターより高くなる可能性があります。
同じ理由から、SQL Server で I/O 処理が行われていなくても、そのボリュームのパフォーマンスが、しばらくの間低下することがあります。パフォーマンス モニターのカウンターには、そのパフォーマンスの低下が反映されます。DMV では、SQL Server の I/O のみが測定されるので、DMV の結果はそのようなパフォーマンスが低下している期間の影響は受けません。
また、DMV では集計データが集められることを思い出してください。SQL Server でのみ I/O 処理が発生しているパフォーマンスの低下期間が存在する場合、パフォーマンスが改善した後も、DMV の結果は、その低いパフォーマンスで実行された I/O の影響を受けます。パフォーマンス モニターでは、パフォーマンスの低下期間の長い待機時間が反映されますが、パフォーマンスが改善したときには短い待機時間が反映されます。
このように、異なる値の意味を理解するには、実際に測定されているものを考慮する必要があります。残念ながら、測定対象のデーターベースを一時的にオフラインにする以外に、DMV のカウンターをリセットする方法はありません。
万能な HA 戦略
Q. SQL サーバーの新しい高可用性 (HA) 戦略を検討する依頼を受けています。考慮すべきことやどこから着手すればよいかに関するガイドを探しています。何かアドバイスをいただけませんか。
A. 1 つの問題は、戦略を策定する際にどの HA テクノロジを使用するか考えることです。多くの企業では、既に使用されているというだけの理由で既存のテクノロジを使用しています。さらに悪いことに、明確な要件がない場合には、任意のテクノロジを選択することがあります。
正しい HA 戦略を策定するためには、要件をまとめて分析する必要があります。この重要な手順を踏まないと、ビジネス要件を満たした HA 戦略を策定できる見込みはありません。HA 戦略を適用する各データについては、次の質問に答える必要があります。
- このデータは他のデータに比べてどの程度重要ですか。保有しているデータが少量であれば、すべてのデータが非常に重要で平等に保護する必要があると主張しても問題ありませんが、複数の SQL Server インスタンスにまたがって数 TB ものデータがある場合、そのような戦略は非現実的です。
- 会社は、どの程度のデータ損失を許容できますか。当然のことながら、経営者はデータ損失を全面的に回避することを望みます。
- データは、どのくらいの期間利用できなくても業務に支障がありませんか。経営者はダウンタイムも全面的に回避することを望みます。近い状態を実現することは可能ですが、残念ながら、実際には実現することはできません。
- 1 日のさまざまな時間帯または週末の間に項目 1 または項目 2 は変化しますか。これは要件に対応するうえで大きな影響があります。ダウンタイムとデータの損失を回避するという目標は、24 時間 365 日ではなく、平日の 9 a.m. から 5 p.m. などのように期間を限定すれば、達成しやすくなります。
- データの可用性と耐久性を確保するために、ワークロードのパフォーマンスが低下することは許容できますか。データの損失を完全に回避できる唯一のテクノロジでは、トランザクション ログ レコード (データベース ミラーリングまたは SQL Server 2012 可用性グループ) または I/O サブシステムの書き込み (SAN レプリケーション) を同期的にミラーリングする必要があります。これらのテクノロジは、どちらも処理の遅延を伴いますが、データの可用性と耐久性を確保するためのトレードオフになります。
- データのセカンダリ コピーは読み取り可能または書き込み可能にする必要がありますか。
このような要件を検討したら、ビジネスによって課せられた制限に対処して、妥協点を見つけることができます。制限があるということは、すべての要件を満たすことができない可能性があることに注意してください。このような場合では、IT 担当者とビジネス部門のマネージャーが妥協案に同意する必要があります。そうでなければ、策定した HA 戦略が期待に沿うことはありません。さらに、ソリューションは複数のテクノロジで構成される可能性が高いので、各テクノロジの制限だけでなく、テクノロジを組み合わせた際のしくみを把握することが HA 戦略の策定を成功に導くうえで重要です。
次のような制限に直面する可能性があります。
- 予算
- 利用可能な電力
- 新しいサーバーのための物理的なスペース、ラック、および空調設備
- 人員 (新しいシステムを管理できる人員がいないか、または必要な新しいテクノロジを実装して管理するスキルを持っている人員が現在いない)
次の 2 つのホワイト ペーパーでは、テクノロジと戦略の例について詳しく説明しています。
これらのホワイト ペーパーは SQL Server 2008 と SQL Server 2008 R2 を対象に記述されていますが、すべての内容は SQL Server 2012 にも適用されます。また、最新の SQL Server 2012 テクノロジのホワイト ペーパー「AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using AlwaysOn Availability Groups (AlwaysOn アーキテクチャ ガイド: AlwaysOn 可用性グループを使用して高可用性ソリューションと障害回復ソリューションを構築する、英語)」もご覧ください。
グループ化する
Q. 複数の同僚から、新しいデータベースでは、単一データ ファイルではなく、ファイル グループを使用すべきだと言われています。そうした場合のメリットとデメリットを教えてください。
A. ファイル グループを使用するデメリットは、度を超えて何百ものファイル グループを使う可能性があること以外に思い付きません。メリットに関しては、データベースが大きくなると (50 ～ 100 GB 以上)、複数のファイル グループが必要になります。また、ファイル グループを使うのには 3 つの主な理由があります。
ファイル グループを使うと、障害時に、すばやく、的を絞った復元が可能になります。1 TB のデータベースがあり、その領域のほとんどが 2009 年から現在までのデータを含む販売テーブルで占められているとします。そのデータベースが障害で破損した場合、復元可能な最小のデータ量はどのくらいですか。1 つのファイル グループに全データを格納している場合、選択肢はありません。すべての古いデータを含む 1 TB 全体を復元する必要があります。
プライマリ、2009 年、2010 年、2011 年、2012 年、2013 年という別個のファイル グループがある場合、より優れた障害回復のアプローチが使用できます。障害発生時には、2013 年のデータをできるだけ早くオンラインに復旧する必要があります。オンライン トランザクション処理 (OLTP) セールス システムをサポートしているのは、この処理です。SQL Server Enterprise Edition があれば、部分的なデータベースの可用性を利用し、WITH PARTIAL 構文を使って PRIMARY ファイル グループを復元することにより、この処理を開始できます。次に、PRIMARY 以外のすぐオンラインに復旧する必要のあるファイル グループを復元し、復元シーケンスを終了できます。
その他のファイル グループも、都合のよいときにオンラインに復旧できます。ここで、オンラインの段階的な部分復元と呼ばれるもう 1 つの SQL Server Enterprise Edition の機能を利用します。これは非常に大規模なデータベース (VLDB) 用の機能を組み合わせたものです。これらの機能を使うと、障害発生時に、ダウンタイムを短縮して、復元シーケンスの優先順位付けで高い柔軟性がもたらされます。また、なんらかの理由でデータベースの一部分だけが破損した場合、段階的な部分復元を実行できます。これにより、障害発生時のダウンタイム要件が限定されます。
ファイル グループを使用する 2 つ目の理由は、パーティション分割とより優れた管理容易性をサポートするためです。パーティション分割を使うと、多数のトランザクション ログを生成せずに、大きなテーブルのデータを、簡単かつすばやく、読み込んで、削除することができます。パーティション分割のメリットについて詳しく説明するのは、このコラムの範囲を超えているので説明しませんが、次のホワイト ペーパーでは、異なる例を使ってパーティション分割のメリットについて詳しく説明しています。
もう 1 つの管理容易性機能を使うと、断片化に対応できます。前と同じ販売テーブルの例を使うと、断片化された販売テーブルのインデックスがあり、テーブルとインデックスがパーティション分割されていない場合、ALTER INDEX ... REBUILD コマンドや ALTER INDEX ... REORGANIZE コマンドを使って、断片化されたインデックス全体を操作する必要があります。古いデータは断片化されませんが、この状況が当てはまります。テーブルを複数のパーティションに分割し、それぞれが別個のファイル グループにあれば、断片化されたインデックスのパーティションだけを最適化できます。このようにすると、多くの時間とリソースを節約できます。
最後に、ファイル グループを使うと、データベース内の異なるワークロードを I/O サブシステムの異なる部分に分離できます。たとえば、いくつかの使用頻度が低いテーブルと、使用頻度が高く、頻繁に更新される少数のテーブルがあるとします。すべてのテーブルが 1 つのファイル グループに含まれている場合、更新頻度が高いテーブルの操作が、使用頻度が低いテーブルの操作のパフォーマンスに影響することがあります。
このような場合、使用頻度の低いテーブルを、I/O サブシステムで専用の領域を確保している単独のファイル グループに分離することができます。次に、使用頻度と更新頻度が高い各テーブルを、それぞれ別個のファイル グループに格納します。これらのテーブルも I/O サブシステムで専用の領域を確保している単独のファイルグループに分離します。このようにすると、I/O 負荷が分離されるので、ワークロードが相互に支障を来たすことはありません。
最新のデータに OLTP ワークロードがあり (たとえば、前述の販売テーブル)、古いデータにデータ ウェアハウス ワークロードがある場合も、単一テーブルで、この処理を行わなければならないことがあります。これは、パーティション分割が必要で、ワークロードを別のファイル グループの異なるパーティションに制限し、ワークロードを相互に分離する必要がある事例です。
.jpg)
Paul S. Randal は SQLskills.com の代表取締役であり、Microsoft Regional Director でもあり、SQL Server MVP でもあります。1999 年から 2007 年までは、マイクロソフトの SQL Server ストレージ エンジン チームに所属していました。また、SQL Server 2005 では DBCC CHECKDB/repair コードを記述し、SQL Server 2008 の開発時にはコア ストレージ エンジンを担当していました。Randal は障害回復、高可用性、およびデータベース メンテナンスの専門家であり、世界中のカンファレンスで定期的に講演を行っています。彼のブログは、SQLskills.com/blogs/paul (英語) で公開しており、Twitter は twitter.com/PaulRandal (英語) でフォローできます。