これまでの連載ではDMVを通じてHW、SW、動作パラメータ、データベース配置、データ量、など、SQL Serverの一連の構成情報を確認し一区切りがついたので、今回はDMVから少し脱線し、SQL Serverのデータベース配置におけるベストプラクティスを紹介します。
紹介の前にまずは物理設計に関わる用語とその位置関係について下記イメージに整理したので、一通りおさらいしてから読み進めてみてください。
データベースの物理設計ではこのようなスタックを意識することがとても重要です。それでは、SQL Serverの物理設計のベストプラクティスをそれぞれ解説していきます。
１．ストレージへのデータベース配置はファイルごとのI/O特性や性能要件を踏まえて分散配置する
一番の優先事項はトランザクションログファイル（ログファイル）を可能な限り独立させたPoolへ配置することです。データベースを構成するデータファイルとログファイルは前者がランダムアクセス、後者がシーケンシャルアクセスの異なるI/Oパターンでアクセスされるため、それらが同じ物理デバイスに配置されると、磁気ヘッド移動の効率が劣化するためです。また、ログファイルのI/O性能はデータベース性能に大きく影響するため、性能の観点でも独立配置が望ましいです。
-- データファイルとログファイルを異なるボリュームへ配置
CREATE DATABASE [UserDB]
ON PRIMARY
(NAME = N'data', FILENAME = N'Y:\datafile.mdf')
LOG ON
(NAME = N'log', FILENAME = N'Z:\xlogfile.ldf')
GO
補足：広く一般的に利用されるようになったフラッシュデバイスの場合は、磁気ヘッドを持たないその仕組み上、異なるI/Oパターンが混じることでのI/O効率劣化の問題は大きく低減されています。
次にtempdbを分けることです。データベースにはユーザが作成するデータベースのほかにSQL Serverが内部的に利用するシステムデータベース（tempdb、msdb、model、master）があり、中でもtempdbについては一時テーブルやテーブル変数などのユーザオブジェクトの格納、作業用（結合、集計、ソート、index再構築など）の内部オブジェクト格納、バージョンストアの格納、などグローバルな共有リソースとして様々な用途で使用されることから、より高速なI/Oデバイスに配置することが望ましいです。
-- 1. tempdbを既定の場所から異なるボリュームへ移動 ALTER DATABASE tempdb MODIFY FILE (NAME = N'tempdev' ,FILENAME = N'T:\tempdb.mdf') ALTER DATABASE tempdb MODIFY FILE (NAME = N'templog' ,FILENAME = N'L:\templog.ldf') GO -- 2. SQL Serverを再起動 -- 3. 元の場所にあったデータファイルとログファイルをファイルシステムから手動で削除
