MS SQL Serverの24時間365日の情報システムデータベースによるメンテナンス作業

まえがき

この記事では、24時間365日の情報システム（つまり、ダウンタイムがない）のデータベースを使用した基本的なルーチン作業と、MS SQL Serverでの実装へのアプローチについて説明します。 また、この記事は簡単な概要であり、すべての作品が十分に詳細であるとは限らないことに注意してください。 ただし、この情報は、必要に応じて、1つまたは別のルーチン作業をより詳細に調査するのに十分です。



この記事への修正や追加がコメントに含まれていれば、とても感謝しています。

メンテナンス作業

データベースでの次の主なルーチン操作は区別されます。



1）スケジュールされたバックアップとそれに続くリカバリなしの検証

2）パフォーマンスを完全に検証するために、以前に作成されたバックアップのスケジュールされた復元

3）システムおよび必要なすべてのデータベースが配置されているストレージメディアの分析

4）必要なサービスの定期チェック

5）スケジュールされたシステムパフォーマンスの最適化

6）スケジュールされたデータ整合性チェック

7）スケジュールされたデータ検証



最初の3つのポイントは、さまざまな障害の後にシステムを回復できるため、最も重要です。 ただし、最後の3つのポイントは計画どおりに実行する必要があります。これにより、ユーザーは快適に作業でき（すべての要求が迅速に、または許容時間内に完了し）、さまざまなレポートシステムでデータが正しくなりました。



定期的なメンテナンスを自動化するために、これらの操作の一部をエージェントまたはWindowsスケジューラのタスクとして形式化できます。



条項6は、 CHECKDBコマンドに基づいています 。



条項7は、情報システムで使用されるサブジェクト領域に関連して実装されます。



パラグラフ6および7については詳しく説明しません。



次に、最初の5つのポイントをより詳細に分析します。

スケジュールされたバックアップとその後のリカバリなしの検証

このトピックについては十分な記事が書かれています（ 組み込みツールを使用してデータベースをバックアップおよび復元するための自動プロセスを実装する例を参照してください）。このスケジュールされた作業は、メインサーバーではなく、常にバックアップで行うことが望ましいことに注意してください。 このようなバックアップサーバーには、最新のデータが必要です（たとえば、レプリケーションを通じて）。 また、MS SQL Serverの各インスタンスのすべてのシステムデータベース（tempdbを除く）をバックアップする必要があります。



バックアップが失敗した場合（またはバックアップチェックで問題が明らかになった場合）、この情報を管理者に報告する必要があります。 たとえば、メールを送信する（ MS SQL Serverでメール通知を設定する ）。



次の質問にも答えるバックアップ戦略を定義することが重要です。



1）バックアップを作成する頻度とタイミング（フル、差分、トランザクションログ）

2）バックアップを削除する期間と時期。

パフォーマンスを完全に検証するために、以前に作成されたバックアップのスケジュールされた回復

また、サードパーティのユーティリティまたはRESTOREコマンドを使用して、バックアップサーバーでこの手順を実行することをお勧めします。



バックアップのリカバリが失敗した場合、この情報を管理者に報告する必要があります。 たとえば、メールを送信する（ MS SQL Serverでメール通知を設定する ）。



また、チェックとして、システムデータベースのバックアップコピーを復元する必要があります。 これを行うには、システムデータベースの名前とは異なる名前を持つ通常のユーザーデータベースとして復元するだけで十分です。

システムおよび必要なすべてのデータベースが配置されているストレージメディアの分析

ここでは、各データベースが占めるスペースの量、ファイルサイズの変更方法、メディア全体の空きスペースの変更方法を分析する必要があります（たとえば、このタスクの一部は、MS SQL Serverのオペレーティングシステムのデータベースファイルおよび論理ドライブのデータの自動収集を使用して実行できます） ）



このようなチェックは毎日行うことができ、結果を送信できます。 たとえば、メールを送信する（ MS SQL Serverでメール通知を設定する ）。

システムデータベースを監視することも必要です。 これが行われない場合、たとえば、これが発生する可能性があります。



また、メディア自体の摩耗や不良セクターをテストすることも重要です。 この種の検証ではメディアに大きな負荷がかかるため、このようなテストの際には、メディアを作業から外し、データを別のメディアに移動する必要があります。



このタスクはすでに純粋にシステム管理者のタスクであるため、詳細には検討しません。 完全に制御するには、システム管理者と交渉して、テストレポートをメールで自動的に送信することをお勧めします。



このチェックは、年に1回行うのが最適です。

必要なサービスの定期チェック

一般的に言えば、サービスは決して落ちるべきではありません。 バックアップサーバーはこれを目的としており、プライマリサーバーに障害が発生した場合に、プライマリサーバーになります。 ただし、ログを定期的に表示する必要があります。 また、情報の自動収集を検討した後、管理者に通知することもできます。 たとえば、結果をメールで送信する（ MS SQL Serverでメール通知を設定する ）。



また、エージェント（またはWindowsタスクスケジューラ）自体のタスクを確認する必要もあります。 たとえば、MS SQL Serverで完了したタスクの自動データ収集を使用して、エージェントタスクの検証を自動化できます 。

スケジュールされたシステムパフォーマンスの最適化

これには、次のコンポーネントが含まれます。



1） MS SQL Serverデータベースのインデックスデフラグを自動化する

2） MS SQL Serverでのデータベーススキーマの変更に関するデータの自動収集 （たとえば、 dbForgeを使用して、目的のバックアップを復元し、変更を比較できるようにします）

3） MS SQL Serverのハングしたプロセスの自動削除

4） 手続き型キャッシュのクリーニング （いつ、何を正確にクリーニングするかを決定することが重要です）

5） パフォーマンス指標の実装

6）インデックスの開発と変更（ クラスター化インデックスの物語 ）



各項目の詳細は説明しませんが、ポイント5〜6について説明します。

パラグラフ5では、データベースの速度を示すシステムを作成する必要があります。 この方法でも実行できます。



また、ほとんどの場合、データベースパラメータでAUTO_CLOSEパラメータを無効にすることをお勧めします。



クエリ自体の最適化についてもう少し話しましょう。



場合によっては、さまざまな理由で（明確でない場合もあります）、オプティマイザーは要求を並列化することを決定します。 そして、彼は常にそれを最適に行うとは限りません。



一般的な推奨事項があります。



1）処理（サンプリング、変更）の結果として大量のデータが存在する場合、並列処理をそのままにします。

2）処理（サンプリング、変更）の結果としてデータがほとんどない場合、ほとんどの場合、並列処理は計画を最適に構築しません。



MS SQL Serverのインスタンスの設定の2つのパラメーターが並列処理を担当します。



1） 最大並列度（並列度をオフにするには、値を「1」に設定します。つまり、クエリプランの実行に関与するプロセッサは常に1つだけです）

2） 並列処理のコストしきい値（ほとんどの場合、デフォルトのままにしておくことをお勧めします）



2つの主要な行があります。



1）プロセッサー時間（QCPUキュー）の待機-これは、要求プロセスが既に開始されており、プロセッサーが実行を待機しているときです（例えば、別のプロセスが要求プロセスを押し出した）

2）リソースの待機（QRキュー）-これは、プロセスが実行のためにリソースの解放を待機するか、実行を継続する（ディスク、RAMなど）場合です。



クエリ実行時間T全体は、次の式で決定されます。



T = TP + TQR + TCPU + TQCPU、ここで：



1）TP-コンパイル時間の計画

2）TQR-リソースのタイムアウト（プロセスがQRキューにある時間）

3）TQCPU-必要なプロセッサーのリリースの待機時間（プロセスがQCPUキューにある時間）

4）TCPU-クエリの実行時間（すべてのプロセッサーでの合計時間）



システムビューsys.dm_exec_query_statsなどで：



1）total_worket_time = TP + TCPU + TQCPU

2）total_elapsed_time = TQR + TCPU



このことから、ビルトインでは、要求が実際にどれだけ実行されたかを正確に言うことができないことを意味します。 ただし、ほとんどの場合、total_elapsed_timeを使用すると、リクエスト自体の実行時間に近い時間を取得できます。 クエリ呼び出しの開始と終了が何らかの方法でデータベースのログテーブルに記録される場合、トレースを使用するか、情報システム自体を使用して、クエリ実行時間をより正確に決定することができます。 ただし、トレースによってシステムがロードされるため、バックアップサーバーでもトレースを実行し、メインサーバーからデータを収集することをお勧めします。 その後、負荷はネットワークのみになります。



並列化中、要求にはnプロセスが割り当てられます（Standart n <= 4のリリース）。 そして、そのような各プロセスの実行にはプロセッサー時間が必要です（各コアで常に1つのプロセスが実行されるわけではありません）。 プロセスが多いほど、1つ以上のプロセスが他のプロセスに取って代わられる可能性が高くなり、TQCPUの増加につながります。



次の場合、並列化するとクエリの実行が遅くなる可能性があります。



1）システムのディスクサブシステムのスループットが非常に弱い場合、要求を分析するとき、その分解は並列処理なしよりも長くかかる可能性があります。

2）並行して使用され、後で起動される別のプロセスによって生成されたプロセスのデータをスキューしたり、データ範囲をブロックしたりすることが可能です。

3）述語のインデックスがない場合、テーブルスキャンにつながります。 クエリ内の並列操作により、一貫性のある実行計画と正しいインデックスを使用して、クエリがはるかに高速に実行されるという事実がわかりにくくなる場合があります。



推奨事項：



大きなサンプルがないサーバーでのリクエストの並列化を禁止するには（TCPUとTQCPU、特に後者が減少する可能性があるため、total_worket_timeインジケーターが減少する必要があります。したがって、前者はすべてのプロセスが1つの線形プロセスに変わるため、あまり変化しないことがあります） これを行うには、クエリプランの実行に常に1つのプロセッサのみが関与するように、 最大並列度パラメーターを1に設定する必要があります。



他の既製のソリューションを使用して、データベースの速度を決定するシステムを構築することもできます。 これらのソリューションがどのように機能するかを明確に理解し、得られた数値を正しく解釈することが重要です。



請求項6によると、インターネット上には多くの包括的な情報もあります。 主なことは、インデックスが論理的にどのように配置され、どのように機能するかを理解することです。



主キーとクラスターインデックスは同じものではないことを思い出させてください。



主キーは、テーブルエントリを一意にするフィールドまたはフィールドのセットです（そのために、一意のクラスター化インデックスまたは一意の非クラスター化インデックスを作成できます）。 他のテーブルで外部キーとして使用され、データの整合性を確保します。



クラスターインデックスは基本的にBツリー（またはその変更）であり、そのリーフにはデータ（行）自体が含まれ、クラスターインデックスとして定義されたノード（フィールドまたはフィールドのグループ）です。 この定義は、Bツリーのリーフとノードに含まれるものに大まかに関連して与えられますが、この定義は、このツリーへの検索と挿入を理解するために必要です。 同時に、クラスターインデックスは一意ではない場合がありますが、一意にすることをお勧めします。



Bツリーは、クラスターインデックスによって並べ替えられた形式でデータを格納する構造であることを思い出してください。 したがって、クラスターインデックスとして選択されたフィールドが順番に増減することも重要です。 つまり、クラスター化インデックスの場合、整数型（ID）のフィールドと日付と時刻（tは常にツリーの最後に書き込まれます）のフィールドが適していますが、uniqueidentifier型のフィールドは不適切であり、tはBの一定の再配置につながります-tree。これにより、データベースが配置されているストレージメディアの読み取り値とレコードの数が増えます。



残念ながら、この事実は無料のリソースでは明らかにカバーされていないため、大手ソフトウェア開発者でも忘れられていることがありますが、インデックス自体とその仕組みを分析します。 そして、これから、クラスター化インデックスを使用して実行できること、および実行しない方が良いことを既に結論付けています。



また、 sys.dm_db_index_usage_statsシステムビューを使用して、インデックスが使用されていることを確認する必要があります。



PS：また、バックアップサーバー上のデータと、このデータ（レプリケーションなど）を同期するシステム自体が最新であることを常に確認し、 すべてのMS SQL Serverデータベースのテーブルおよびファイルの成長に関するデータを収集する情報を自動化する必要があります 。 そして主なことは、 典型的なDBAエラーを犯さないことです



以下は、 Zabbixを使用してMS SQL Serverデータベースを監視し、 MS SQL Server 2017 Standardで障害を実装する方法の概要です。

ソース：

» CHECKDB

» MS SQL Serverでの電子メール通知のセットアップ

» 復元

» MS SQL Serverのオペレーティングシステムのデータベースファイルおよび論理ドライブ上のデータの自動収集

» tSQLtおよびSQLQueryStressを使用したデータベースパフォーマンスのテスト

» AUTO_CLOSE

» 42GB msdbストーリー

» トップ（10）無料のSSMSプラグイン

「 毎日のサービスプラン-パート1：自動インデックスデフラグ

「 毎日のサービスプラン-パート2：統計の自動更新

「 毎日のサービスプラン-パート3：バックアップの自動作成

» MS SQL Serverで完了したタスクに関するデータの自動収集

» MS SQL Serverデータベースのインデックスデフラグを自動化する

» MS SQL Serverでのデータベーススキーマの変更に関するデータの自動収集

» データベース管理のためのdbForgeの便利な機能MS SQL Server ）

» MS SQL Serverのハングしたプロセスの自動削除

» 手続き型キャッシュのクリーンアップ

» クエリ、ストアドプロシージャ、およびトリガーのパフォーマンスインジケーターのMS SQL Serverへの実装。 自動ルーティング

» Microsoft SQL Serverの同時実行性と不確実性の度合い

» 期待のプレゼンテーション

» リクエスト実行スキーム

» 並列度

» T-SQLを使用したデータベースの探索

» クラスタードインデックスの物語

» すべてのMS SQL Serverデータベースのテーブルおよびファイルの成長に関するデータを収集する自動化

» 一般的なDBA MS SQL Serverエラーに関する13番目の記事

» MS SQL Server 2017 Standardでの障害の実装

» Zabbixを使用してMS SQL Serverデータベースを追跡する

» Sys.dm_db_index_usage_stats

» 組み込みツールを使用してデータベースをバックアップおよび復元する自動プロセスの例