SQL Server インスタンスのベスト プラクティス

このページは、Microsoft SQL Server を実行する Google Compute Engine インスタンスを最適化する方法についての情報を提供しています。高いパフォーマンスを発揮するよう SQL Server インスタンスを設定する方法の詳細については、チュートリアルをご覧ください

Windows を構成する

このセクションでは、Compute Engine 上で実行中の Microsoft Windows オペレーティング システムを SQL Server 用に最適化する方法に関する構成トピックを取り上げます。

Windows ファイアウォールを設定する

ベスト プラクティス: Windows Server の高度なファイアウォールを使用して、クライアント コンピュータの IP アドレスを指定します。

Windows の高度なファイアウォールは、Windows Server の重要なセキュリティ コンポーネントです。他のクライアント マシンからデータベースに接続できるように SQL Server 環境を設定するには、受信トラフィックを許可するようファイアウォールを構成します。

netsh advfirewall firewall add rule name="SQL Access" ^
dir=in action=allow ^
program="%programfiles%\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" ^
remoteip=[LOCAL_SUBNET]

このファイアウォール ルールを使用する場合、クライアント マシンの IP アドレスを指定することをおすすめします。[LOCAL_SUBNET] の代わりに、remoteip パラメータで空白のない IP アドレスのカンマ区切りリストを指定します。また、program パラメータのパスは、使用する SQL Server のバージョンに応じて変わる可能性があります。

SQL Server アプリケーション イメージには、SQL Server Windows ファイアウォール ルールが含まれている可能性があります。このルールにはほとんど制限がないため、システムが本番環境に移行する前に無効にすることを検討してください。

ネットワーク接続を調整する

ベスト プラクティス: オペレーティングシステムのデフォルトのネットワーク設定を使用します。

ほとんどのオペレーティング システムのデフォルトのネットワーク設定は、ある程度高速なネットワークに接続している小さなコンピュータの構成用に設定されています。通常、このような設定で十分です。さらに、保守的なデフォルトでは、ネットワーク トラフィックがネットワークや接続されているコンピュータにあまり負荷を与えないようにします。

Compute Engine では、仮想マシン インスタンスは、大容量とパフォーマンスを提供する Google によって設計されたネットワークに接続しています。Compute Engine インスタンスを実行している物理サーバーは、このネットワークの容量を利用するよう高度に最適化されています。インスタンスの仮想ネットワーク ドライバも最適化されており、ほとんどのユースケースではデフォルト値で十分です。

ウィルス対策をインストールする

ベスト プラクティス: ウィルス対策ソフトウェアについては、Microsoft のガイダンスに従ってください。

Windows を実行している場合は、なんらかのウィルス対策ソフトウェアを実行している必要があります。不正なソフトウェアとソフトウェア ウィルスにより、ネットワークに接続されたシステムが重大なリスクにさらされるため、ウィルス対策ソフトウェアがデータの保護に使用できる簡単な軽減方法となります。ただし、ウィルス対策ソフトウェアを正しく構成していないと、データベースのパフォーマンスに悪影響を及ぼす可能性があります。 Microsoft は、ウィルス対策ソフトウェアの選択方法についてアドバイスを提供しています

パフォーマンスと安定性を最適化する

このセクションでは、Compute Engine の SQL Server のパフォーマンスとオペレーション動作を最適化し、問題なく実行できるようにする方法についての情報を提供しています。

データファイルとログファイルを新しいディスクに移動する

ベスト プラクティス: ログとデータファイルには別の SSD 永続ディスクを使用します。

デフォルトでは、SQL Server の事前構成されたイメージは、ブート永続ディスクにすべてのものがインストールされており、C: ドライブとしてマウントされます。セカンダリ SSD 永続ディスクを接続して、ログファイルとデータファイルを新しいディスクに移動することを検討してください。

ローカル SSD を使用して IOPS を向上させる

ベスト プラクティス: tempdb と Windows のページング ファイルを保持するローカル SSD を 1 つ以上使用して新しい SQL Server インスタンスを作成します。

ローカル SSD テクノロジーはエフェメラル(一時的)という特性があるので、重要なデータベースやファイルに使用することは適切ではありません。ただし、tempdb と Windows のページング ファイルは両方とも一時ファイルであるため、どちらもローカル SSD に移動するのに適しています。これにより、SSD 永続ディスクから IO オペレーションの負荷を大幅に軽減します。この設定の詳細については、こちらをご覧ください。

並列クエリ処理

ベスト プラクティス: max degree of parallelism8 に設定します。

max degree of parallelism の推奨されるデフォルト設定は、サーバーの CPU 数と同じ数にすることです。ただし、クエリを 16 個または 32 個のチャンクに分割するポイントがあり、すべて異なる vCPU で実行してからすべてを 1 つの結果に統合すると、1 つの vCPU のみでクエリを実行した場合よりも時間がかかります。実際には、8 がデフォルト値として適しています。

ベスト プラクティス: CXPACKET の待機をモニタリングし、cost threshold for parallelism を徐々に増やします。

この設定は max degree of parallelism と関連しています。各ユニットは、シリアル実行プランが並列実行プランと見なされるようになるまで、シリアル実行プランでクエリを実行するのに必要な CPU と IO 作業の組み合わせを表します。デフォルト値は 5 です。デフォルト値の変更に具体的な推奨は示していませんが、注意して観察し、必要に応じて負荷テスト中にデフォルト値を 5 ずつ増やすことをおすすめします。この値を増やす必要があることを示す主なインジケーターの 1 つは、CXPACKET の待機の存在です。CXPACKET 待機の存在は必ずしもこの設定の変更が必要であることを示していませんが、この設定を変更してみてください。

ベスト プラクティス: さまざまな待機のタイプをモニタリングして、グローバル並列処理設定を調整するか、並列処理設定を個々のデータベース レベルで設定します。

データベースには、それぞれ異なる並列処理のニーズがあります。これらの設定をグローバルに設定して、Max DOP を個々のデータベース レベルで設定できます。固有のワークロードを観察して、待機をモニタリングし、それに合わせて値を調整する必要があります。

SQLSkills サイトは、データベース内の待機の統計を説明する便利なパフォーマンス ガイドを提供します。このガイドに従うと、待機とは何か、遅延を緩和するにはどうすればよいのか理解できます。

トランザクション ログを処理する

ベスト プラクティス: システム上のトランザクション ログの増加をモニタリングします。1 日の平均的なログの累積に基づき、自動拡張を無効にして、ログファイルを固定サイズに設定することを検討してください。

パフォーマンス損失や断続的な低下で最も見過ごされやすい原因の 1 つは、トランザクション ログの増加を管理していないことです。データベースが Full 復旧モデルを使用するよう構成されている場合、任意の時点まで復旧できますが、トランザクション ログがいっぱいになるのがより速くなります。デフォルトでは、トランザクション ログファイルがいっぱいの場合、SQL Server はトランザクションをさらに書き込むために何もないスペースを追加してファイルサイズを増やし、終了するまでデータベースのすべてのアクティビティをブロックします。SQL Server は、[最大ファイルサイズ] と [ファイル拡張] の設定に基づいて、各ログファイルを大きくします。

ファイルが最大サイズの制限に到達しそれ以上大きくできない場合、システムは 9002 エラーを発行して、データベースを読み取り専用モードにします。ファイルを大きくできる場合、SQL Server はファイルサイズを拡張して、空きスペースをゼロで埋めます。[ファイル拡張] の設定は、デフォルトではログファイルの現在のサイズの 10% です。これは、ファイルが大きくなると、空きスペースを新しく作成するのにそれだけ時間がかかるため、パフォーマンス的には適切なデフォルト設定ではありません。

ベスト プラクティス: トランザクション ログの定期的なバックアップのスケジュールを設定します。

最大サイズと拡張の設定がどのようなものであっても、定期的なトランザクション ログのバックアップのスケジュールを設定すると、デフォルトで古いログのエントリが切り詰められ、システムが既存のファイル スペースを再利用できます。このシンプルなメンテナンス タスクにより、トラフィックのピーク時間にパフォーマンスが低下することを回避できます。

仮想ログファイルを最適化する

ベスト プラクティス: 仮想ログファイルの増大をモニタリングして、ログファイルの断片化を回避します。

物理トランザクション ログファイルは、セグメントに分割されて仮想ログファイル(VLF)に入れられます。新しい VLF は、物理トランザクション ログファイルを拡張する必要があるときに、必ず作成されます。自動拡張を無効にしておらず、拡張が頻繁に発生する場合は、作成される VLF が多すぎることになります。このアクティビティにより、ログファイルの断片化が発生する可能性があります。これはディスクの断片化のように、パフォーマンスに悪影響を及ぼす可能性があります。

SQL Server 2014 は、自動拡張中に作成する VLF の数を判断する、さらに効率的なアルゴリズムを導入しました。一般的に、拡張が現在のログファイルの 1/8 のサイズより小さい場合、SQL Server はその新しいセグメント内に 1 つの VLF を作成します。これまでは、64 MB から 1 GB までの拡張には 8 個の VLF を作成し、1 GB を超える拡張には 16 個の VLF を作成していました。次の TSQL スクリプトを使用して、データベースに現在存在する VLF の数を確認できます。数千のファイルがある場合は、ログファイルを手動で減少させてサイズを変更することを検討してください。

--Check VLFs substitute your database name below
USE 
DECLARE @vlf_count INT
DBCC LOGINFO
SET @vlf_count = @@ROWCOUNT
SELECT VLFs = @vlf_count

VLF の詳細については、Brent Ozar のウェブサイトをご覧ください。

インデックスの断片化を回避する

ベスト プラクティス: 最もよく変更されるテーブルのインデックスを定期的にデフラグします。

テーブルのインデックスは断片化しやすく、これらのインデックスを使用するクエリのパフォーマンスが低下する可能性があります。定期的なメンテナンス スケジュールに、最もよく変更されるテーブルのインデックスの再編成を含めてください。データベースで次の T-SQL スクリプトを実行して、インデックスと断片化の割合を表示します。この例の結果では、PK_STOCK のインデックスが 95% 断片化していることがわかります。次の 'SELECT' ステートメントで、'[YOUR_DB]' を使用しているデータベースの名前に置き換えます。

SELECT stats.index_id as id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'[YOUR_DB]'), NULL, NULL, NULL, NULL) AS stats
    JOIN sys.indexes AS indx ON stats.object_id = indx.object_id
      AND stats.index_id = indx.index_id AND name IS NOT NULL;

RESULTS
-------------------------------
Id    name          avg_fragmentation_in_percent
-------------------------------
1 ORDERS_I1 0
2 ORDERS_I2 0
1 ORDER_LINE_I1 0.01
1 PK_STOCK95.5529819557039
1 PK_WAREHOUSE0.8

インデックスの断片化が激しい場合、簡単な ALTER スクリプトで再編成できます。以下は、テーブルのインデックスごとに実行できる ALTER ステートメントを出力するサンプル スクリプトです。

SELECT
'ALTER INDEX ALL ON ' + table_name + ' REORGANIZE;
GO'
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_CATALOG = 'YOUR_DB'

断片化が最も激しい結果セットのテーブルを選択して、これらのステートメントを増分的に実行します。このスクリプトや同じようなスクリプトを、定期的なメンテナンス ジョブの 1 つとしてスケジュールすることを検討してください。

バックアップする

ベスト プラクティス: バックアップの計画を立て、バックアップを定期的に実行します。

Ola Hallengren のサイトは、完全なバックアップとメンテナンス プランの実装方法を理解するための出発点として適しています。

定期的なデータベースのバックアップを実行する場合、永続ディスクの IOPS の利用が多すぎないよう注意してください。ローカル SSD を使用してバックアップをステージングしてから、Cloud Storage バケットに push します。

モニタリング

ベスト プラクティス: Stackdriver Monitoring を使用します。

Microsoft Windows 用のベータ版 Stackdriver Monitoring エージェントをインストールして、いくつかのモニタリング データポイントを Stackdriver システムに送信できます。

データ コレクション機能を使用してモニタリングする情報を細かく調整し、組み込みの管理データ ウェアハウスに送信できます。管理データ ウェアハウスはモニタリング対象のサーバーで実行できます。または、ウェアハウスを実行している別の SQL Server インスタンスにデータをストリーミングすることもできます。

バルクデータを読み込む

ベスト プラクティス: 別のデータベースを使用して、本番環境サーバーに移動する前に、バルクデータをステージングして変換できます。

定期的ではありませんが、少なくとも 1 回は、大量のデータをシステムに読み込まなければならない可能性があります。これはリソースを大量に必要とするオペレーションであり、一括読み込みを実行するときに、永続ディスクの IOPS の制限に到達する可能性があります。

一括読み込みオペレーションではディスクの IO と CPU の利用を削減する簡単な方法があり、その方法にはバッチジョブの実行を高速化する利点もあります。ここでの解決策として、Simple 復旧モデルを使用する完全に別のデータベースを作成して、そのデータベースをバルク データセットのステージングと変換に使用してから、本番環境データベースに挿入します。十分なスペースがある場合は、この新しいデータベースをローカル SSD ドライブに置くこともできます。これにより、一括オペレーションで使用されるリソースと、ジョブの実行にかかる時間が削減されます。最後の利点として、本番環境データのバックパック ジョブで、トランザクション ログのこれらの一括オペレーションすべてをバックアップする必要はないため、ジョブはより小さいものになり速く実行できます。

設定を検証する

ベスト プラクティス: 構成をテストして、期待どおりに実行されていることを確認します。

新しいシステムを設定する場合は、必ず構成の検証を計画して、いくつかのパフォーマンス テストを実行してください。このストアド プロシージャは、SQL Server の構成を評価するのに適したリソースです。構成フラグについて後でご覧になり、プロシージャをぜひ実行してください。

SQL Server Enterprise Edition を最適化する

SQL Server Enterprise Edition は、Standard Edition に多数の機能が追加されたものです。既存のライセンスを GCP に移行する場合は、実装を検討したほうがよいいくつかのパフォーマンスのオプションがあります。

圧縮されたテーブルを使用する

ベスト プラクティス: テーブルとインデックスの圧縮を有効にします。

テーブルの圧縮によりシステムが高速になる可能性があることは意外かもしれませんが、ほとんどの場合、高速になります。少量の CPU サイクルを使用してデータを圧縮することと、大きなブロックの読み書きに必要とされる余分なディスク IO を削減することがトレードオフになります。通常、システムで使用する IO が少なくなると、パフォーマンスが向上します。テーブルとインデックスの圧縮の見積もりと有効化の手順は、MSDN のウェブサイトにあります。

バッファプールの拡張機能を有効にする

ベスト プラクティス: バッファプールの拡張機能を使用して、データアクセスを高速化します。

バッファプールは、システムがクリーンページを保存する場所です。シンプルに表現すると、データのコピーを保存して、ディスク上と同じようにミラーリングします。メモリ内のデータが変更されると、ダーティページと呼ばれ、ディスクにフラッシュして変更を保存する必要があります。利用できるメモリよりデータベースが大きい場合、バッファプールに負荷がかかり、クリーンページがドロップされる可能性があります。クリーンページがドロップされると、ドロップされたデータに次にアクセスする必要があるとき、システムはディスクから読み込む必要があります。

バッファプールの拡張機能により、クリーンページをドロップする代わりに、ローカル SSD に push できます。これは、仮想メモリと同じように機能する、つまりスワッピングすることで、ローカル SSD のクリーンページにアクセスできます。この方法は、通常のディスクでデータをフェッチして取得するより高速です。

このテクニックは、十分なメモリがある場合ほど高速ではありませんが、利用できるメモリが少ない場合にスループットがある程度増加します。Brent Ozar のサイトで、バッファプールの拡張機能の詳細や、いくつかのベンチマークの結果を確認できます。

次のステップ

このページは役立ちましたか?評価をお願いいたします。

フィードバックを送信...

Compute Engine ドキュメント