出所

みなさんこんにちは！ 今日は、クラウドデータベースについて、さらに正確には、パフォーマンスを直接保証するプロアクティブな1回限りのイベントについてお話します。

クラウドデータベースは、容量の急速な増加と新しいデータベースの立ち上げの問題を長い間解決してきましたが、今日では、管理および監視タスクのプロバイダーへの移転のおかげで、あらゆる規模の企業にとってほぼ緊急のニーズになっています。

テクノサーブクラウドプラットフォーム上で新しいクラウドデータベースサービスの立ち上げと最適化を行い、もちろん多くの問題に遭遇し、それらを解決する独自のアプローチを開発しました。 サービスがテストされ、機能するようになったので、経験を皆さんと共有したいと思います。この資料を読むことで、他の人の間違いの繰り返しを避けたり、新しい何かを発見したりすることができます。

今日、企業は増え続けるデータ量に対処する必要があるため、これらの膨大な情報配列すべてを管理するタスクを何らかの形で緩和する必要がありました。 このソリューションは、クラウドコンピューティングプラットフォームの導入とクラウドデータベースの作成を通じて発見されました。

計算によると、構造化データと非構造化データの両方のボリュームは、年間平均60％増加します。 これまで、従来のデータベースはこのすべての情報を保存する場所として機能していましたが、これでは十分ではなく、クラウドテクノロジーが役に立ちました。 ユーザーはデータベースに独自のコンピューティング能力を割り当てる必要性をなくし、この責任をクラウドサービスプロバイダーに委ねました。 このアプローチは、データベースのパフォーマンスと可用性の向上、およびスケーラビリティの向上という点で非常に生産的であることが実証されています。

この記事では、MS SQL Serverデータベースのパフォーマンスを確保するための最適な設定とメカニズムの簡単な概要を説明することにしました。 開発者の推奨事項と「業界のベストプラクティス」は時間の経過とともに調整されるため、このリストは決して網羅的なものではありません。 これらすべての変更を追跡し、正しく実装できるのは、専門のDBA（データベース管理者）のチームのみです。 クラウドデータベースサービスプロバイダーが持つのはこの種のスタッフであり、ほとんどのお客様はそのようなチームを持つことを自慢できません。

マイクロソフトの推奨事項およびベストプラクティスに従って、クラウドでホストされるMS SQL Serverデータベースの高可用性と高性能を確保するために、次のアクティビティを実行します。

注：推奨事項の主な部分は一般的ですが、特定の各サーバーでのアプリケーションの適用は多くの要因に依存します。 したがって、以下は、関連するMicrosoftドキュメントへのリンクであり、詳細情報が含まれています。

MS SQL Server設定を最適化します

すべてのMS SQLサーバーに最新のService Pack /累積更新/セキュリティ更新をインストールします。

2016年以降、Microsoft SQL Serverの更新スキームは簡素化されました -現在、更新は定期的にリリースされています。

更新プログラムをインストールするための一般的なシーケンスを以下に示します（運用サーバーにインストールする前のすべての更新プログラム-最初はテスト環境でテストします）。

•最新のService Pack（SP）をインストールします。

•Service Packの最新の累積更新プログラム-累積更新プログラム（CU）をインストールします。

•セキュリティ更新プログラムがリリースされた場合、それらもインストールします。

•問題が発生した場合、Critical On-Demand（COD）を検索して適用します-除去を修正します。

注：マイクロソフトは、最新のCUがリリースされたらすぐにインストールすることを推奨していますが、ほとんどの企業は最新のSPのみをインストールし、CUにサーバーの問題の修正が含まれる場合にのみCUをインストールします。 内部の指示に従って、このプロセスをダウンロードと調整しますが、Microsoftの公式の推奨事項を最初に検討することを常にお勧めします。

MS SQL Serverの最適なメモリ使用量パラメーターと最適なMaxDOPパラメーターを構成します。

既定では、MS SQL Serverはシステムリソースの可用性に基づいてメモリ要件を動的に変更できます。 デフォルトでは、min server memoryパラメーターは0に設定され、max server memoryパラメーターは2,147,483,647 MB​​に設定されます。 MS SQL Serverの最適なメモリ使用オプションの選択については、 こちらをご覧ください 。

MS SQL Serverがマルチプロセッサコンピュータで実行されている場合、最適な並列処理の度合い、つまり、各並列実行プランに対して1つの命令を実行するのに必要なプロセッサの数が決定されます。 並列実行に関してプロセッサの数を制限するには、max degree of parallelismパラメーターを使用できます。 最適なMaxDOPパラメータの選択については、 こちらをご覧ください 。

必要に応じて、トレースフラグを使用します。

MS SQL Serverのトレースフラグは、デフォルトから別のサーバー動作の一種の「切り替え」です。 トレースフラグに関する情報は、 ここにあります 。

「TempDB」データベースおよびその他のシステムデータベースの設定を最適化します。

MS SQL Serverには、次のシステムデータベースが含まれています。

•「マスター」-このデータベースには、MS SQL Serverのインスタンスのすべてのシステムレベルのデータが保存されます。

•「msdb」-アラートとタスクをスケジュールするためにMS SQL Serverエージェントによって使用されます。

•「モデル」-MS SQL Serverのインスタンスで作成されたすべてのデータベースのテンプレートとして使用されます。 モデルデータベースのサイズ、並べ替えパラメーター、復旧モデル、およびその他のパラメーターを変更すると、変更後に作成されるすべてのデータベースの対応するパラメーターが変更されます。

•「リソース」は読み取り専用データベースです。 MS SQL Serverの一部であるシステムオブジェクトが含まれています。 システムオブジェクトは物理的にリソースデータベースに格納されますが、データベースのsysスキーマには論理的に表示されます。

•「TempDB」-一時オブジェクトまたは結果セットの相互作用のためのワークスペース。

TempDBデータベースの最適なパフォーマンスをチューニングするための推奨事項は、 ここにあります 。

データファイル/ログファイルのデフォルト設定を正しく構成します。

データファイルの場所とログファイルを明示的に指定せずにMS SQL Serverで新しいデータベースを作成すると、MS SQL Serverはこれらのファイルをデフォルトの場所に作成します。 このデフォルトの場所は、MS SQL Serverのインストール中に構成されます。 データファイル/ログファイルのデフォルトの場所の設定については、 こちらをご覧ください 。

Windowsサーバー設定を最適化する

ディスクサブシステムの最適な設定（64Kのクラスターサイズでフォーマットされた高速SSDドライブ）を使用します。

MS SQL Serverには独自のデータストレージ機能があります。 この点で、これらの機能を考慮して、物理レベルと論理レベルの両方でディスクサブシステムを準備すると、パフォーマンスに重大な影響があります。 詳細については、 こちらをご覧ください。

「データベースファイルの即時初期化」を設定します。

MS SQL Serverでは、データファイルを即座に初期化できます。 インスタントファイルの初期化は、スペースをゼロで埋めないことにより、ディスクスペースを解放します。 代わりに、新しいデータがファイルに書き込まれると、ディスクの内容が上書きされます。 ログファイルはすぐに初期化できません。 詳細はこちら 。

「ユーザー」と「システム」の負荷に異なるネットワークインターフェイスを使用します。

当社のサーバーには複数のネットワークインターフェースがあり、個々のインターフェースは、たとえば定期的なバックアップトラフィックなどの専用タスクに使用できます。 この構成には利点があります。たとえば、さまざまなタスクの機能に対するインターフェイスの使用を最も厳密に区別することができます。

ユーザーデータベースの設定を最適化する

出所

「自動縮小」および「自動閉じる」パラメータがオフになっていることを確認します。

「自動縮小」は、MS SQL Serverが定期的にデータベースファイルを圧縮することを示します（詳細はこちら ）。

「自動クローズ」は、すべてのリソースを解放し、すべてのユーザーを切断した後、データベースが閉じられることを示します（詳細はこちら ）。

「統計の自動作成」および「統計の自動更新」パラメーターが有効になっていることを確認します。

「統計の自動作成」パラメーターが有効になっている場合、クエリオプティマイザーは、必要に応じて、クエリプランの要素数の推定を改善するために、クエリ述語の個々の列の統計を作成します（詳細はこちら ）。

[統計の自動更新]オプションが有効になっている場合、クエリオプティマイザーは統計が古くなる可能性があることを検出し、クエリで使用されている場合は更新します（詳細はこちら ）。

必要に応じて、「コミットされたスナップショット分離の読み取り」を使用します。

「スナップショット」という用語は、トランザクション内のすべてのクエリが同じバージョン、またはデータベースのスナップショットを見つけるという事実を反映しています。これは、トランザクションが開始された時点のデータベースの状態に対応します。 スナップショットトランザクションは、ベース行またはデータページのロックを必要としません。これにより、以前の不完全なトランザクションでロックすることなく、別のトランザクションを実行できます。 データを変更するトランザクションは、データが読み取られるトランザクションをブロックしません。また、データを読み取るトランザクションは、データが書き込まれるトランザクションをブロックしません。これは、通常、MS SQLでデフォルトで設定される分離レベル「Read Committed」を使用しても観察されますサーバー ロックの拒否を提供するこのアプローチのアプリケーションは、複雑なトランザクションでのデッドロックの可能性を大幅に減らします。

「コミット済みスナップショット分離の読み取り」オプションを有効にすると、デフォルトの「コミット済み読み取り」分離レベルから行バージョンにアクセスできます。 「コミットされたスナップショット分離の読み取り」パラメーターがOFFに設定されている場合、行バージョンにアクセスするには、各セッションのスナップショットの分離レベルを明示的に設定する必要があります（詳細はこちら ）。

「ページ検証」パラメーターが「CHECKSUM」に設定されていることを確認します。

「ページ検証」データベースパラメータが「CHECKSUM」に設定されている場合、MS SQL Serverはページ全体のチェックサムを計算し、ページがディスクに書き込まれるときに値をページヘッダーに保存します。 ディスクからページを読み取ると、チェックサムが再計算され、ヘッダーの値と比較されます。 これにより、ファイル内のデータの整合性を高レベルで確保できます（詳細はこちら ）。

データファイル/ sおよびデータベースログファイルを最適に管理する

データファイル/ sとデータベースログファイルは、別々の物理ディスクに配置されます。

データファイルとログファイルを同じデバイスに配置すると、競合が発生し、パフォーマンスが低下する可能性があります。 ファイルを異なるディスクに配置すると、データファイルとログファイルのI / O操作を並行して実行できます（詳細はこちら ）。

データベースログファイルを1つだけ作成します。

ログファイルはMS SQL Serverによって並列ではなく連続的に使用され、複数のログファイルを使用してもパフォーマンスは向上しません（詳細はこちら ）。

「仮想ログファイル（VLF）」DBの断片化は許可されません。

データベースログファイルは、内部的に仮想ログファイル（VLF）と呼ばれるセクションに分割され、ログファイルの断片化が多いほど、VLFの数が多くなります。 ログファイルのVLF番号が200を超えると、ログファイルの読み取り（トランザクションレプリケーション/ロールバックなど）、ログファイルのバックアップなど、ログファイルに関連する操作のパフォーマンスが低下する場合があります。 （詳細はこちら ）。

データファイル/ sとデータベースログファイルの正しい初期サイズを選択します。

データベースを作成するとき、データベース内の予想される最大データ量に応じて、データファイルをできるだけ大きくする必要があります。 たとえば、現在50 GBのデータがあり、さらに6か月後にさらに50 GBが追加されることがわかっている場合、データファイルの初期サイズはすぐに100 GBに等しくする方がよい（詳細はこちら ）。

データファイル/ sおよびデータベースログファイルに対して正しい「自動成長」パラメーターを選択します。

データベースファイルのサイズが大きい場合、データベース自体を大きくするプロセスによりパフォーマンスが大幅に低下する可能性があるため、パーセントで「自動拡張」を使用することはお勧めしません。したがって、データベースをMB単位で固定サイズだけ増やすことがより好ましい（詳細はこちら ）。

データファイル/ sとデータベースログファイルのサイズを常に監視し、必要に応じて、データベースの最小負荷時にこれらを積極的に増やします。

実稼働システムでは、極端な状況でファイルサイズを増加させる手段としてのみ、自動拡張機能を使用する必要があります。 データベースデータファイルの増加を毎日管理するために使用することはお勧めしません。 通常、アラートまたは監視プログラムは、ファイルサイズを監視し、それらを事前に増やすために使用されます。 これにより、断片化が回避され、これらのメンテナンス操作が負荷が最小限の時間に移されます（詳細はこちら ）。

データベースを最適に提供します

出所

データベースデータの整合性チェックを実行します。

次の操作を実行して、データベース内のすべてのオブジェクトの論理的および物理的な整合性を検証します（詳細はこちら ）。

•データベースのDBCC CHECKALLOCステートメントの実行。

•データベース内の各テーブルおよび各ビューに対してDBCC CHECKTABLEステートメントを実行します。

•データベースのDBCC CHECKCATALOGステートメントの実行。

•データベース内の各インデックス付きビューの内容を確認します。

•FILESTREAMを使用してファイルシステムにvarbinary（max）データを保存するときに、リンクレベルでファイルシステムのファイルとディレクトリおよびテーブルメタデータ間の一貫性をチェックします。

•データベース内のService Brokerデータを検証します。

インデックスの断片化に応じて、カスタムインデックスの再構築/再編成を実行します。

MS SQL Serverは、基になるデータに対して挿入、更新、または削除操作を実行するときに、インデックスの状態を自動的に維持します。 時間が経つにつれて、これらの変更により、インデックス内のデータがデータベース全体に散在する（断片化される）という事実につながる可能性があります。 フラグメンテーションは、キー値に基づく論理的な順序がデータファイルの物理的な順序と一致しないページがインデックスに含まれている場合に発生します。 大幅に断片化されたインデックスは、クエリのパフォーマンスに深刻な影響を与え、アプリケーションの応答を遅くする可能性があります。 インデックスを再編成または再構築することで、断片化を解消できます（詳細はこちら ）。

統計を更新します。

デフォルトでは、クエリオプティマイザーは必要に応じて統計を更新し、クエリプランを改善します。 統計を更新すると、クエリが現在の統計でコンパイルされます。 ただし、統計を更新すると、クエリが再コンパイルされます。 クエリプランの改善によるパフォーマンスの向上とクエリの再コンパイルに要する時間のバランスを取る必要があるため、統計を頻繁に更新しないことをお勧めします。 妥協ソリューションを選択するための基準は、アプリケーション固有です（詳細はこちら ）。

たとえば、「通常の圧縮」などの「悪い」方法は使用しません。

ファイルの圧縮中に移動されたデータは、ファイル内の利用可能な場所に分散できます。 これにより、インデックスの断片化が発生し、インデックス範囲内で検索するクエリの実行時間が長くなる可能性があります（詳細はこちら ）。

必要に応じて、「古い」データからデータベースを定期的にクリーニングします。

多くの場合、企業は適用法の要件と内部要件を満たすために、しばらくの間データを保存する必要があります。 データが不要になった後、通常は削除することをお勧めします。これにより、MS SQL Serverのパフォーマンスが向上し、サーバーハードウェア要件の増加をより正確に予測できるようになります（詳細はこちら ）。

最適なデータベースバックアップを実施します

出所

RTO / RPOの顧客要件と世界のベストプラクティスに従って、最適なデータベースバックアップ戦略を決定します。

MS SQL Serverは、データベースに保存されている機密データに必要な保護を提供します。 不可逆的なデータ損失のリスクを最小限に抑えるには、データに加えられた変更が保存されるデータベースを定期的にバックアップする必要があります。 適切に設計されたバックアップおよびリカバリ戦略は、さまざまな障害による損傷が発生した場合のデータ損失からデータベースを保護します（詳細はこちら ）。

データベースバックアップの定期的なテストリカバリを実行します。

バックアップがテストされるまで、リカバリ戦略はないと言えます。 各データベースのバックアップ戦略を完全にテストし、データベースのコピーをテストシステムに復元することが非常に重要です。 使用する予定の各タイプのバックアップのリカバリをテストする必要があります（詳細はこちら ）。

データベースの高可用性のために最適なテクノロジーを使用します（要件に応じて）

Always Onフェールオーバークラスターインスタンス：

AlwaysOnフェールオーバークラスタリングインスタンスは、Windows Serverフェールオーバークラスタリング（WSFC）機能を使用して、サーバーインスタンス-フェールオーバークラスターインスタンス（FCI）レベルでの冗長性により、ローカルリソースの高可用性を提供します。 フェールオーバークラスターインスタンス（FCI）は、すべてのWindows Serverフェールオーバークラスタリング（WSFC）ノードにインストールされ、複数のサブネットにインストールされる可能性がある唯一のMS SQL Serverインスタンスです。 ネットワーク上のフェールオーバークラスターのインスタンスは、1台のコンピューターで実行されているMS SQL Serverのインスタンスのように見えますが、フェールオーバークラスターのインスタンスは、現在のノードが利用できなくなった場合に、WSFCノードを別のノードに移行するフェールオーバーを提供します（詳細はこちら ）。

Always On可用性グループ：

可用性グループAlwaysOnは、データベースミラーリングに代わる高可用性および障害復旧ソリューションです。 可用性グループは、可用性データベースと呼ばれる、一緒に別のリソースに移行する一連のユーザーデータベースのフェールオーバー環境を維持します。 可用性グループは、プライマリ読み取り/書き込みデータベースのセットと、対応するセカンダリデータベースの1〜8セットをサポートします。 さらに、セカンダリデータベースは、読み取り専用にしたり、一部のバックアップ操作用に作成したりできます（詳細はこちら ）。

データベースミラーリング：

データベースミラーリングは、MS SQL Serverデータベースの可用性を高めることを目的としたソリューションです。 各データベースのミラーリングは個別に実行され、完全復旧モデルを使用するデータベースでのみ機能します（詳細はこちら ）。

ログ配布：

MS SQL Serverでは、ソースサーバーインスタンスのソースデータベースから、ターゲットサーバーの他のインスタンスの1つ以上の受信者データベースにトランザクションログのバックアップを自動的に送信できます。 トランザクションログのバックアップは、各受信者データベースに個別に適用されます（詳細はこちら ）。

サーバーの状態を常時監視します（MS SQL + Windows）

サーバーの監視は重要なアクティビティです。 効果的な監視には、現在のパフォーマンスのスナップショットを定期的に取得して問題の原因となっているプロセスを検出し、パフォーマンスの成長または変化を追跡するためにデータを絶えず収集することが含まれます。

出所

データベースのパフォーマンスを常に評価することで、応答時間を最小化し、スループットを最大化することにより、最適なパフォーマンスを実現できます。 おおよそのネットワークトラフィック、ディスクI / O、およびCPU使用率は、パフォーマンスに影響する重要な要素です。 アプリケーションの要件を慎重に分析し、データの論理的および物理的構造を理解し、データベースの使用を評価し、オンライントランザクション処理（OLTP）と意思決定支援（詳細はこちら ）などの競合するワークロード間の妥協を達成する必要があります。