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

---

Microsoft SQL Server を実行する Compute Engine インスタンスを最適化するには、いくつかのベスト プラクティスを適用できます。高パフォーマンスの SQL Server インスタンスを設定する方法については、高パフォーマンスの SQL Server インスタンスの作成をご覧ください。

Windows を構成する

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

注: Workload Manager の SQL Server 評価では、最適なパフォーマンスを得るための一連の Google Cloud 推奨事項を使用して、Google Cloud コンソールから直接 SQL Server のデプロイをスキャンできます。詳細については、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 では、仮想マシン（VM）インスタンスは、大容量とパフォーマンスを提供する 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 永続ディスクから I/O オペレーションの負荷を大幅に軽減します。この設定の詳細については、TempDB を設定するをご覧ください。

並列クエリ処理

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

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

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

この設定は max degree of parallelism と関連しています。各ユニットは、シリアル実行プランが並列実行プランと見なされるようになるまで、シリアル実行プランでクエリを実行するのに必要な CPU と I/O 作業の組み合わせを表します。デフォルト値は 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 YOUR_DB
DECLARE @vlf_count INT
DBCC LOGINFO
SET @vlf_count = @@ROWCOUNT
SELECT VLFs = @vlf_count

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

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

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

テーブルのインデックスは断片化しやすく、これらのインデックスを使用するクエリのパフォーマンスが低下する可能性があります。定期的なメンテナンス スケジュールに、最もよく変更されるテーブルのインデックスの再編成を含めてください。データベースで次の Transact-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 つとしてスケジュールすることを検討してください。

データベースを別の場所から Google Cloud に復旧している場合は、インデックスを再ビルドするか少なくとも再編成してから、使用量を更新します。YOUR_DB は、実際のデータベースの名前に置き換えます。

dbcc updateusage (YOUR_DB) GO USE YOUR_DB
GO

セカンダリ ディスクをフォーマットする

ベスト プラクティス: セカンダリ ディスクを 64 KB のアロケーション ユニットでフォーマットします。

SQL Server がデータを格納する単位は、エクステントと呼ばれます。エクステントのサイズは 64 KB で、8 KB の連続するメモリページ 8 個から構成されます。64 KB のアロケーション ユニットでディスクをフォーマットすると、SQL Server によるエクステントの読み取りと書き込みの効率が向上し、ディスクの I/O パフォーマンスが改善します。

セカンダリ ディスクを 64 KB のアロケーション ユニットでフォーマットするには、次の PowerShell コマンドを実行します。このコマンドは、システム内の新しいディスクと初期化されていないディスクをすべて検索し、64 KB のアロケーション ユニットでディスクをフォーマットします。

Get-Disk | Where-Object {$_.PartitionStyle -eq 'RAW'} | Initialize-Disk -PartitionStyle GPT -PassThru | New-Partition -AssignDriveLetter -UseMaximumSize | Format-Volume -FileSystem NTFS -AllocationUnitSize 65536 -Confirm:$FALSE

バックアップ

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

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

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

モニタリング

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

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

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

バルクデータを読み込む

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

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

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

注: 一括ログは、一括読み込みオペレーションで検討すべきもう 1 つの復旧モデルです。これは、より多くのディスク容量を使用し、ディスク I/O も増加します。さらに、トランザクション ログのバックアップ ジョブを定期的に実行する必要がありますが、完全復旧モデルほどではありません。

設定を検証する

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

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

SQL Server Enterprise Edition を最適化する

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

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

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

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

注: テーブルとインデックスの圧縮オプションは、SQL Server Standard Edition 2016 SP1 以降でも使用できます。

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

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

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

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

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

SQL Server ライセンスを最適化する

同時マルチスレッディング（SMT）

ベスト プラクティス: ほとんどの SQL Server ワークロードで、コアあたりのスレッド数を 1 に設定します。

Intel プロセッサ上で一般的にハイパー スレッディング テクノロジー（HTT）と呼ばれる同時マルチスレッド（SMT）は、単一の CPU コアを 2 つのスレッドとして論理的に共有できる機能です。Compute Engine では、SMT はほとんどの VM でデフォルトで有効になっています。つまり、VM の各 vCPU は単一のスレッド上で実行され、物理 CPU コアごとに 2 つの vCPU によって共有されます。

Compute Engine では、コアあたりのスレッド数を構成すると実質的に SMT が無効になります。コアあたりのスレッド数が 1 に設定されている場合、vCPU は物理 CPU コアを共有しません。この構成は、Windows Server と SQL Server のライセンス費用に大きく影響します。コアあたりのスレッド数を 1 に設定すると、VM 内の vCPU の数は半減し、必要な Windows Server と SQL Server のライセンス数も半減します。これによりワークロードの総費用を大幅に削減できます。

ただし、コアあたりのスレッド数の構成もワークロードのパフォーマンスに影響します。マルチスレッド用に記述されたアプリケーションは、ンピューティング処理を複数の論理コアでスケジュール設定された小さな並列化されたチャンクに分割することでこの機能を活用できます。このように作業を並列化することで、使用可能なコアリソースの使用率が高くなるため、システム全体のスループットが向上することがよくあります。たとえば、1 つのスレッドが停滞した場合、他のスレッドがそのコアを利用できます。

SMT が SQL Server に及ぼす正確なパフォーマンスへの影響は、ワークロードの特性と使用するハードウェア プラットフォームによって異なります。これは、SMT の実装がハードウェアの世代によって異なるためです。OLTP ワークロードなど、小規模なトランザクションを大量に含むワークロードは、多くの場合、SMT の利点を活かしてパフォーマンスを大きく向上させることができます。対照的に、OLAP ワークロードなどの並列化可能性が低いワークロードは SMT から得られるメリットが少なくなります。これらのパターンは一般的に認識されていますが、ワークロードごとに SMT がパフォーマンスに及ぼす影響を評価し、コアあたりのスレッド数を 1 に設定した場合の影響を判断することを検討してください。

大半の SQL Server ワークロードに対して最も費用対効果の高い構成では、コアあたりのスレッド数が 1 に設定されています。より大きな VM を使用することで、パフォーマンスの低下を相殺できます。ほとんどの場合、ライセンス費用の 50% の削減は、より大きな VM によるコスト増加を上回ります。

例: SQL Server が n2-standard-16 構成にデプロイされていると仮定する

デフォルトでは、オペレーティング システムに表示されるコアの数は 16 個です。つまり、サーバーを実行するには Windows Server の vCPU 16 個と SQL Server ライセンスの vCPU 16 個が必要になります。

PS C:\> Get-WmiObject -Class Win32_processor | Select-Object NumberOfCores, @{Name="Thread(s) per core";Expression={$_.NumberOfLogicalProcessors/$_.NumberOfCores}}

NumberOfCores Thread(s) per core
------------- ------------------
            8                  2

SQL Server で SMT を無効にする手順を実行すると、新しい構成は次のようになります。

PS C:\> Get-WmiObject -Class Win32_processor | Select-Object NumberOfCores, @{Name="Thread(s) per core";Expression={$_.NumberOfLogicalProcessors/$_.NumberOfCores}}

NumberOfCores Thread(s) per core
------------- ------------------
            8                  1

オペレーティング システムに 8 つのコアしか表示されないため、サーバーを実行するために必要なのは、Windows Server と SQL Server の 8 つの vCPU だけになりました。

次のステップ

• 高パフォーマンスの SQL Server インスタンスの作成
• SQL Server インスタンスの作成
• Windows インスタンスの作成