TechEd資料1

Download Report

Transcript TechEd資料1 

セッション ID: T4-401
SQL Server による大規模
Mission Critical システムの
展開
マイクロソフト株式会社
サーバープラットフォーム ビジネス本部
技術顧問
熊澤 幸生
セッションの目的とゴール
セッションの目的
基幹系システムを SQL Server 上で展開するための DB
物理設計と運用設計の重要性を理解していただく
SQL Server を基幹系業務で利用するための留意点を
理解していただく
セッションのゴール
最新のサーバーハードウエア、ストレージサブシステム
選択上の考慮点を、ベンチマークテスト結果を基に理解
していただく
基幹系システム展開のためのベスト プラクティスを理解
していただく
3
アジェンダ
-安定稼働する Mission Critical システムの実現-
OLTP アプリケーション アーキテクチャ
トランザクション処理設計
データベース物理設計と運用設計
SQL Server 固有の物理設計とは
安定した性能を保つための運用設計とは
サーバーハードウエアの選定
バランスド システム とは
最新ハードウエア環境によるベンチマーク結果
本稼働移行時の考慮点
最低限のプラットフォーム チューニング
本稼働後の継続的な性能監視
まとめ
4
アプリケーション アーキテクチャ
-トランザクション処理設計トランザクションの境界を理解する
暗黙的な宣言と、明示的な宣言
トランザクションの実行時間は必要最小限に
ブロッキング防止の考慮
共有ロックと、排他・更新ロックの競合を防止する
適切なロック ヒント、又は、RCSI (Read Committed Snapshot
Isolation) を、アプリケーションで利用することにより、回避可能
デッド ロックへの考慮
変換デッド ロック発生の防止
アプリケーション間で更新順序の統一化と回収処理
クエリ実行時の考慮
アドホック クエリとパラメーター化クエリ
適切なフィルタリングによる、結果セットサイズ
(中間結果セットを含む) と行数の制限を行う
5
データベース物理設計と運用設計
SQL Server のデータベース物理設計
データ ファイルの分割と配置
トランザクション ログ ファイルの配置
データ パーティショニング
インデックスの決定と設定カラムの選定
安定した性能を保つための、データベース運用
設計
インデックス再構築と再構成のタイミング
統計情報更新頻度とタイミング
6
SQL Server のデータベース物理設計
-データ ファイルの 分割と配置データ ファイル配置の階層化を理解する
ファイル グループ
データパーティショニングの分割単位
非クラスター化インデックスを分離格納が可能
規定値: Primary ファイル グループ
ファイル
ファイル グループに最低一つ必要
tempdb データ ファイルは、一時領域確保処理の、同時実行性向上のために、
CPU コア数と同じファイル数に分割する
データ ファイルの配置
ファイル グループは異なるドライブレター (LUN) 上に配置する
データパーティショニングでの分離格納方法
アクティブ領域: 高速回転 (15,000 rpm) の RAID 1 + 0
ヒストリカル領域: 大容量の RAID 6
領域サイズの見積もり
初期容量と増分指定
初期領域サイズは、将来のデータ量を見込んだサイズの 2.5 倍
増分指定はあくまでも、緊急時の保険
7
SQL Server のデータベース物理設計
-トランザクション ログ ファイルの配置ユーザー データベースごとに一つ必要
独立したドライブレター (LUN) の RAID 1 + 0 上に配
置する
トランザクション ログ ファイルの I/O 帯域が、サー
バー上の処理能力を左右する
シーケンシャルな書き込み処理のみが発生する
通常 1 回の I/O 処理時間は、1 ms (1/1,000 秒)
1 論理ディスク当たり 1,000 IOPS
複数の RAID 1 + 0 論理ディスク上に配置することにより、
サーバー全体の処理能力を高めることが可能
ファイル破損により、データベースが修復不可能にな
る可能性がある
定期的な DUMP Transaction (ログのアーカイブ) の実
行により、ファイルの肥大化を防止する
8
SQL Server のデータベース物理設計
-データパーティショニングSQL Server 2005 以降の Enterprise 版で
利用可能
テーブルを分割するためのキー項目が必要
例: 年月、地域コード (関東・中部・関西)
クエリは、複数のパーティションを透過的にアクセ
ス可能
データベース運用管理でとても便利な機能
アクティブなデータと、過去のデータ (Read Only) を分
離格納する
バックアップ処理、インデックス再構築等の局所化、並列処理
を可能とする
スライディング ウィンドウズ機能
直近の 24 か月分のデータを保持する
9
SQL Server のデータベース物理設計
-インデックスの役割クラスター化インデックスと非クラスター化
インデックスの違いを理解する
クラスター化インデックスの重要な役割
B-Tree 格納構造によりデータを高速に検索可能
インデックス情報は、ページ内最少キーを保持
行の Insert 時に、格納するターゲットページをインデックスにより決定する
テーブルには必ずクラスター化インデックスを定義することを基本とする
クラスター化インデックスを持つテーブル上の非クラスター化インデック
ス検索
非クラスター化インデックスを検索し、クラスター化インデックスのキーを取得す
る
再度、クラスター化インデックスを検索し、検索対象の行を取得する
非クラスター化インデックス情報は、データと 1: 1 の関係で保持される
非クラスター化インデックスのみから構成されるテーブル
インデックス情報は、データと 1: 1 の関係で保持される
非クラスター化インデックス キー + RID
データはヒープ構造として格納される
10
SQL Server のデータベース物理設計
-インデックスを作成する列の決定発生するデータを理解する
データの特性、利用方法
クラスター化インデックスを付与する列の場合、Insert する行のキー値
の分布を把握する
ランダム値、昇順値 (伝票番号等)
複合列 (複数列で構成するキー)
キー項目の更新の有無と頻度 (非クラスター化インデックス)
実行されるクエリの種類と実行頻度
インデックスを作成する列のガイド ライン
主キーと外部キー、結合処理で参照される列
煩雑に範囲検索される列
並び替え、集計処理で利用される列
キーの内容が変更されない列 (クラスター化インデックス)
インデックス作成に適していない列
クエリで参照されない列
一意の値を含まない列
text、ntext、image データ型属性を持つ列
null、可変長属性を持つ列
11
データベースの運用設計
-インデックス再構築と再構成のタイミング再構築とは
ALTER INDEX REBULD (DBCC DBREINDEX)
エクステント、論理ページ両方の格納領域の断片化 (Fragmentation) を
解消
オフライン操作
SQL Server 2008 以降の Enterprise 版は、オンライン再構築をサポート
自分自身の格納領域と、tempdb 領域を利用する
再構成とは
ALTER INDEX REORGANIZE (DBCC INDEX DEFRAG)
論理ページの格納領域の断片化を解消
オンライン操作が可能
実施タイミング
動的管理ビュー sys.dm_index_physical_stats による定期的な監視
エクステント、論理ページの 10% を超える断片化が発生したときに実施する
例) 再構成を週に 1 回、再構築を月に 1 回実施
sort in tempdb オプションを利用する
統計情報も同時に更新される
12
SQL Server のデータベース物理設計
-格納領域断片化の発生原因と防止方法格納領域断片化の原因
ページ分割の発生原因
データの追加と更新処理
insert 処理は、クラスター化インデックスのキー値により、格納ページを
決定する
空き領域がなかった場合、ページ分割処理が発生する
ページ分割により、分割された後半のデータ ページに対応するイン
デックス情報を追加する
update 処理は、可変長属性、null 属性をもつ列に更新が発生した場合、
物理的な行の長さが変わり、同一領域に格納できない時に
発生する
発生場所
データ ページとインデックス ページ
断片化の防止方法
インデックス再構築・再構成時に fillfactor を指定して、
将来の追加領域を確保する
クラスター化インデックス領域に指定可能 (0-100%)
インデックス キー値の分布状況と、再構築・再構成の実施頻度により
fillfactor の値を決定する
定期的にインデックスの再構築・再構成を実施する
13
データベースの運用設計
-統計情報とはクエリ オプティマイザーが最適なクエリ実行プランを作成するための
情報
インデックスを構成する列の値をサンプリングし、分布情報を格納
「どうのような値をもつデー-タが何件入っているか? 」
クエリ オプティマイザーは統計情報を使用して、クエリに対してどの
インデックスを使用するかの実行コストを予測し、利用の有無、利用方法を
判断する
パフォ-マンスに影響を及ぼす
デー-タの変更に伴い統計情報の定期的な更新が必要
インデックス再構築・再構成後に、データ更新処理により、実際の分布状態と、統計情報間
にデータの乖離が発生する
統計情報の作成
自動作成 (既定)
インデックス作成時に、インデックス列内の値の分布情報を自動的に作成
結合述語または WHERE 句で使用されるインデックスが作成されていない列の
利用状況を保持する (インデックス チューニングの推奨データ)
統計情報の保守
自動更新 (既定) : インデックス単位に保持している更新状況から、一定の
閾値を超えた時に実行される
手動更新: update statistics on <テーブル名>
14
データベースの運用設計
-統計情報更新とタイミング統計情報の更新が必要な理由
統計情報の自動更新 (AUTO_UPDATE_Statistic):
閾値による on/off 制御方式
更新が必要か, 否かの判定は単純ではない
閾値の 10% (OK)/70% (グレーイゾーン)/
90% (レッドゾーン)
クエリ実行時間バラツキー発生の最大の原因は、実データ
と乖離した統計上による不適切な実行プランが原因
実行頻度 (手動更新)
データ増減の激しいテーブルは毎日統計情報を更新する
バッチ処理により大量のバルクインサートの発生時
トランザクション処理で、大量のデータの追加・更新の発生時
バッチ処理で、非クラスター化インデックスとして参照される列に対
する更新の発生時
月に一度全部のテーブルの統計情報を更新
15
サーバー ハードウエアの選定
-バランスドシステムとはSQL Server リレーショナルエンジンに最適化され
たハードウエア構成
リファレンス アーキテクチャ
考慮すべき構成要素 (共有リソース)
プロセッサ
メモリ
ストレージ サブシステム
ネットワーク
SQL Server 専用サーバー上に配置する
将来のトランザクション ベースラインを明確化する
SQLOS の内部動作を理解する
16
サーバー ハードウエアの選定
-プロセッサ主流は、x 64 アーキテクチャ
NUMA アーキテクチャ サポートの有無
NUMA 対応 CPU
Intel Xeon 55 xx/56 xx/75 xx シリーズ
AMD Opteron
CPU ソケット内にローカル メモリ コントローラーと複数の
高速インターコネクトを内蔵
4 ソケット以上を推奨
マルチコア化が今後も加速
Intel Xeon 55 xx 4 Core/ソケット
Intel Xeon 56 xx 6 Core/ソケット
Intel Xeon 75 xx 8 Core/ソケット
AMD Opteron 6 Core/ソケット
クロック数と、キャッシュサイズも重要
CPU 占有率の監視より、コア数不足
(SQLOS スケジューラと 1: 1) を監視する
17
サーバーハードウエアの選定
-メモリSQL Server 2000 では、最もクリティカルな共有リ
ソースだった
現在 x 64 64 ビットアドレス方式が主流
SQL Server 2008 Enterprise は、8 TB の
メモリ空間を提供
必要な物理メモリサイズは?
NUMA アーキテクチャの場合
NUMA ノードあたり 8–16 GB を推奨
SMP アーキテクチャの場合
CPU コアあたり、4 GB をスタートラインに
OLTP の場合、ユーザー DB 容量の 10% を目安にメモ
リ見積もりを実施する
18
サーバー ハードウエアの選定
-ストレージサブシステム接続方法
HBA 経由ファイバー チャネル接続
(複数の HBA による MPIO 構成を推奨する)
iSCSI
DAS
デバイスタイプ
処理スピード順 (目的別の階層化を考慮)
SSD/FC ディスク/SAS/SATA
トランザクション ログ
順アクセスの書込み処理 (1,000 IOPS/物理ドライブ)
トランザクション処理
複数の高回転 (15,000 rpm) デバイスを利用
DWH
大容量の中速ディスクを利用
容量より、回転数と物理ディスクの数が重要
RAID 1 + 0 を推奨
(4 + 4) 2 LUN (ユーザー データ領域、tempdb 領域)
(3 + 3) 2 LUN (トランザクション ログ領域、Index 領域)
搭載する物理ディスク数は、データ ボリュームとトランザクション負荷により決定
する
19
サーバー ハードウエアの選定
-最新ハードウエア環境によるベンチマーク結果今回の検証環境
H/W 構成
Intel Xeon 7560 2.26 GHz 4 ソケット 32 コア
(Nehalem-EX NUMA アーキテクチャ)
Memory 128 GB
SAS 10,000 rpm 146 GB * 4 RAID 0 構成
system DB/tempdb を配置
SSD 160 GB * 2 RAID 0 構成
ユーザー DB データ ファイルを配置
SAS 10,000 rpm 146 GB * 2 RAID 0 構成
ユーザー DB トランザクション ログ ファイルを
配置
S/W 構成
Windows Server 2008 R2 Enterprise x 64
SQL Server 2008 Enterprise x 64 SP1 + CU 7
SQL Server 2008 R2 Enterprise x 64
20
SQL Server と NUMA ノード
メ
モ
リ
CPU
CPU
CPU
CPU
CPU
CPU
CPU
CPU
CPU
CPU
CPU
CPU
CPU
CPU
CPU
CPU
メモリ
コント
ローラー
メ
モ
リ
メモリ
コント
ローラー
メモリ
コント
ローラー
メ
モ
リ
メ
モ
リ
メモリ
コント
ローラー
インターコネクト
Windows
Node No
Node 0
Node 1
Node 2
Node 3
SQLOS
Node No
Node 1
Node 2
Node 0
Node 3
SQLOS
ユーザー ノード
SQLOS
グローバル・
リソース
システム ノード
SQLOS
ユーザー ノード
OS
グローバル・
リソース
SQLOS
ユーザー ノード
21
ベンチマークテストの目的と方法
目的
SQL Server 2008/2008 R2 上で、サーバーの搭載する、
ソケット数、コア数、動作クロック数による、スケール アップ、スケールア
ウト効果を検証する
NUMA、SMT (ハイパー スレッド機能) の有効性の検証する
同一環境での、SQL Server 2008/2008 R2 処理能力を比較する
テスト方法
オンメモリで動作する小規模データベースを利用し、ディスク I/O を最
少化するモデルを利用する
同時接続ユーザー数を、350/700/900/1,050 と変化させ、CPU 処理能
力の限界点をテストする
初期化されたデータベースをアタッチし、バッファー キャッシュをクリアし
た状態でテストを実施する
別紙トランザクションミックスで、5 分間の連続負荷を、
各クライアントからサーバーに送る
データ拡張後モデルを利用して、NUMA メモリ管理の有効性を
検証する
22
テーブル別行数とサイズ
テーブル名
行数
Data (KB)
Index (KB)
52,302
3,432
6,816
156,430
12,072
4,800
Customers
91
24
80
Employees
9
232
40
Products
77
8
88
Suppliers
29
24
40
Categories
8
112
24
15,904
11,888
Orders
Order Details
Total
23
データ ボリューム
テーブル別行数とサイズ (拡張後)
テーブル名
行数
Data (KB)
Index (KB)
Orders
2,137,185
136,696
276,088
Order Details
6,410,326
317,576
379,952
Customers
91
24
80
Employees
9
232
40
Products
77
8
88
Suppliers
29
24
40
Categories
8
112
24
454,672
656,312
Total
24
データ ボリューム
負荷テスト トランザクションミックス
Transaction Name
処理概要
参照・追加・更新テーブル名
Weight
1
2
GetCustInfo
顧客情報の照会
10
Customers
NewCustOrder
顧客からの受注処理
25
Orders
GetCustOrderDetails
顧客受注明細照会
5
Order Details Customers
GetCustOrderHistory
顧客受注履歴の照会
5
Products
UpdCustOrderShippedDate
出荷実績日時の更新
5
Orders
UpdCustOrderRequireDate
顧客希望納期の更新
5
Orders
GetCustProductOrderHistory
顧客別・商品別受注履歴照会
5
Products
GetCustContact
顧客担当窓口の検索
5
Customers
GetProductInfo
商品情報の検索
10
Products
GetSupplierInfo
商品納入先に検索
10
Suppliers
SalesByYear
年次売上明細の照会
5
Orders
SalesByCategoryByYear
カテゴリー別年次売上明細の
照会
5
Orders
GetEmployeeInfo
従業員情報の検索
5
Employees
Total
25
100
3
4
Order Details
Order Details
Orders
Customers
Order Details
Orders
Customers
Suppliers
Order Detail
Products
Categories
Xeon 5570 2P 8C vs 7560 4P 32C
Xeon 5570 2.93 GHz 2P/8C 48 GB Memory : Windows Server 2008 R2 + SQL Server 2008
300 Thread NUMA_on SMT_off
300 秒間の
トランザクション処理件数
50,007
350 Thread NUMA_om SMT_on
トランザクション数
/秒
166.69
300 秒間の
トランザクション処理件数
トランザクション数
/秒
57,165
190.55
Xeon 7560 2.27 GHz 4P/32C 128 GB Memory Windows Server 2008 R2 + SQL Server 2008 R2
1,050 Thread NUMA_on SMT_off
300 秒間の
トランザクション処理件数
155,936
Scaleout
係数
26
1,050 Thread NUMA_om SMT_on
トランザクション数
/秒
519.79
3.118
300 秒間の
トランザクション処理件数
162,223
トランザクション数
/秒
540.74
2.838
スレッド数増加に伴うスケールアウト効果
Windows Server 2008 R2 + SQL Server 2008 SP1
350 Thread NUMA_on SMT_off
350 Thread NUMA_on SMT_on
300 秒間のトランザクション処理件数
66,278
66,802
トランザクション数/秒
220.93
222.67
比率
N/A
N/A
700 Thread NUMA_on SMT_off
700 Thread NUMA_on SMT_on
125,515
130,052
418.38
433.51
1.894
1.947
1,050 Thread NUMA_on SMT_off
1,050 Thread NUMA_on SMT_on
142,797
151,301
475.99
504.34
2.154
2.265
350 Thread NUMA_on SMT_off
350 Thread NUMA_on SMT_on
300 秒間のトランザクション処理件数
66,981
67,274
トランザクション数/秒
223.27
224.16
比率
N/A
N/A
700 Thread NUMA_on SMT_off
700 Thread NUMA_on SMT_on
129,913
131,715
433.04
439.05
1.940
1.958
900 Thread NUMA_on SMT_off
900 Thread NUMA_on SMT_on
152,741
157,438
509.14
524.79
2.280
2.340
1,050 Thread NUMA_on SMT_off
1,050 Thread NUMA_on SMT_on
155,936
162,223
519.14
540.74
2.328
2.411
Windows Server 2008 R2 + SQL Server 2008 R2
27
SQL Server 2008 vs 2008 R2 比較
Windows Server 2008 R2 + SQL Server 2008 SP1
Windows Server 2008 R2 + SQL Server 2008 R2 RTM
300 秒間の
トランザクション処理件数
トランザクション数/秒
300 秒間の
トランザクション処理件数
トランザクション数/秒
比率
350 Thread NUMA_on SMT_off
66,278
220.93
66,981
223.27
1.011
350 Thread NUMA_on SMT_on
66,802
222.67
67,274
224.16
1.007
700 Thread NUMA_on SMT_off
125,515
418.38
129,913
433.04
1.035
700 Thread NUMA_on SMT_on
130,052
433.51
131,715
439.05
1.013
142,797
475.99
155,936
519.79
1.092
151,301
504.34
162,223
540.74
1.072
1,050 Thread NUMA_on
SMT_off
1,050 Thread NUMA_on
SMT_on
28
ベンチマークテストの考察
Xeon 5570 2.93 GHz 2 P 8 C と Xeon 7560 2.27 GHz 4 P 32 C の比較
プロセッサソケット数が 2 倍、コア数が 4 倍、
クロック数が 0.775 倍、を数値化したスケール アップ係数
3.08 倍
Transaction/sec の比較
NUMA
3.12 倍
NUMA + SMT
2.84 倍
Xeon 7560 2.27 GHz 4 P 32 C 上の SQL Server 2008 SP1 と SQL Server
2008 R2 の比較
NUMA
SQL Server 2008 R2 が 9.2% の処理能力向上
NUMA + SMT
SQL Server 2008 R2 が 7.2% の処理能力向上
Xeon 7560 4 P 32 C 上の最適な同時接続ユーザー数
(SQL Server 2008 R2 上)
900 ユーザーが最適な状況であるが、1,050 ユーザーの過負荷にも耐える処理
能力を持っている
過負荷状況において、SMT (ハイパー スレッド機能) は、10% 程度の
処理能力向上に寄与している
29
SQL Server 2008 R2 SMT_on
-900 ユーザー同時接続テスト時の Task Manager-
30
SQL Server 2008 R2 SMT_on
-900 ユーザー同時接続テスト時の CPU 負荷状態-
31
SQL Server 2008 R2 ベンチマーク
-5 秒間に処理したトランザクションの推移3 500
350 Threads
NUMA
3 000
700 Threads
NUMA
2 500
900 Threads
NUMA
2 000
1050 Threads
NUMA
350 Threads
NUMA_SMT
1 500
700 Threads
NUMA_SMT
1 000
900 Threads
NUMA_SMT
500
1,050 Threads
NUMA_SMT
0
5 20 35 50 65 80 95 110125140155170185200215230245260275290
32
SQL Server 2008 R2 SMT_on
-900 ユーザー同時接続テスト時の I/O 負荷状態DB 名
tempdb
Northwind
33
File 名
data 1
data 2
data 3
data 4
data 5
data 6
data 7
data 8
data 9
data 10
data 11
data 12
data 13
data 14
data 15
data 16
data 17
data 18
data 19
data 20
data 21
data 22
data 23
data 24
data 25
data 26
data 27
data 28
data 29
data 30
data 31
data 32
合計
log
data
log
読込み回数
1,827
1,836
1,831
1,829
1,820
1,829
1,839
1,876
1,858
1,789
1,835
1,860
1,817
1,843
1,846
1,825
1,962
1,746
1,881
1,795
1,828
1,796
1,903
1,911
1,842
1,786
1,900
1,772
1,904
1,857
1,774
1,895
58,912
0
332
0
読込み (KB)
108,472
108,560
108,096
109,072
106,512
108,472
108,048
110,544
109,880
105,176
107,680
110,608
107,816
110,000
107,168
107,664
114,480
103,768
110,528
106,616
107,200
106,160
111,176
113,048
109,408
104,968
111,704
104,992
112,264
109,504
105,008
111,592
3,476,184
0
24,776
0
書込み
1,752
1,745
1,748
1,754
1,730
1,750
1,756
1,784
1,767
1,697
1,743
1,779
1,731
1,764
1,741
1,744
1,856
1,665
1,799
1,713
1,736
1,702
1,816
1,817
1,761
1,701
1,814
1,688
1,801
1,769
1,688
1,800
56,111
1,122
717
48,946
書込み (KB)
108,472
108,560
108,096
109,072
106,512
108,472
108,048
110,544
109,880
105,176
107,680
110,608
107,816
110,000
107,168
107,664
114,480
103,768
110,528
106,616
107,200
106,160
111,176
113,048
109,408
104,968
111,704
104,992
112,264
109,504
105,008
111,592
3,476,184
67,320
24,072
136,665
SQL Server 2008 R2 ベンチマーク
-NUMA ノードの稼働状況 (dm_os_scheduler)テスト環境
NUMA Node No
Node Type
Scheduler Switch
I/O Complation Pass
# of Schedulers
# of Tasks
# of Runnable Queue
# of Worker Threads
# of Active Worker
テスト環境
NUMA Node No
Node Type
Scheduler Switch
I/O Complation Pass
# of Schedulers
# of Tasks
# of Runnable Queue
# of Worker Threads
# of Active Worker
34
Windows Server 2008 R2 + SQL Server 2008 R2
NUMA_on / SMT_on / 1,050 ユーザー接続
0
1
2
3
Windows OS +
SQLOS
User Node User Node
User Node
14,766
5,488
5,095
14,732
41,387
14,147
13,377
40,126
16
16
16
16
124
106
62
141
79
66
21
101
175
164
134
193
122
104
60
138
NUMA_on / SMT_on / 700 ユーザー接続
0
1
2
3
Windows OS +
SQLOS
User Node User Node
User Node
10,183
9,886
10,149
9,637
21,796
21,733
21,729
21,750
16
16
16
16
31
31
27
31
0
0
0
1
70
70
68
67
29
29
25
28
Total
40,081
109,037
64
433
267
666
424
Total
39,855
87,008
64
120
1
275
111
SQL Server 2008 R2 ベンチマーク
-SQLOS Wait 事象 (dm_os_wait_stats)NUMA_on / SMT_on / 1,050 ユーザー接続
wait_type
SOS_SCHEDULER_YIELD
PAGELATCH_EX
PAGELATCH_SH
WRITELOG
ASYNC_NETWORK_IO
IO_COMPLETION
LCK_M_S
LCK_M_X
LCK_M_U
PAGEIOLATCH_SH
LATCH_SH
PAGEIOLATCH_EX
LOGBUFFER
LATCH_EX
CMEMTHREAD
PAGEIOLATCH_UP
PAGELATCH_UP
THREADPOOL
OLEDB
LCK_M_SCH_M
35
waiting_tasks_count
wait_time_ms
max_wait_time_ms
signal_wait_time_ms
4,487,816
58,497
50,865
81,506
24,636
38,890
4,913
686
694
495
465
150
60
82
130
10
18
3
1,240
2
60,822,594
1,616,517
1,097,121
601,658
313,659
85,954
36,281
5,646
5,634
3,471
2,593
1,027
955
416
234
66
37
31
4
0
87
201
198
110
185
112
39
34
37
68
25
37
92
20
5
12
8
22
1
0
60,819,871
98,263
98,689
144,971
270,343
498
7,132
897
866
775
946
161
102
176
232
0
26
0
0
0
Xeon 7560 4 ソケット 32 コア
検証マシンによる負荷テスト
900 ユーザー接続による負荷テスト
本稼働移行時の考慮点
-最低限のプラットフォーム チューニングsqlserver.exe 起動アカウントへの、
ローカル ポリシー ユーザー権利の割り当ての
設定
ユーザー コネクション毎の並列処理最大限度
の設定
tempdb データ ファイル同時実行性の向上
37
メモリ内のページ ロックの指定
38
Large page Extensions の確認
06/18/2010 17: 35: 27, Node configuration: node 3: CPU mask: 0x00000000ff000000:0 Active CPU mask:
0x00000000ff000000:0.
06/18/2010 17: 35: 27, Node configuration: node 2: CPU mask: 0x000000000000ff00:0 Active CPU mask:
0x000000000000ff00:0.
06/18/2010 17: 35: 27, Node configuration: node 1: CPU mask: 0x00000000000000ff:0 Active CPU mask:
0x00000000000000ff:0.
06/18/2010 17: 35: 27, Node configuration: node 0: CPU mask: 0x0000000000ff0000:0 Active CPU mask:
0x0000000000 ff 0000:0.
06/18/2010 17: 35: 27, Lock partitioning is enabled.
06/18/2010 17: 35: 27, Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock
Owner blocks per node.
06/18/2010 17: 35: 27, Using locked pages for buffer pool.
06/18/2010 17: 35: 27, Large Page Allocated: 32 MB
06/18/2010 17: 35: 26, Large Page Allocated: 32 MB
06/18/2010 17: 35: 26, Large Page Allocated: 32 MB
06/18/2010 17: 35: 26, Large Page Allocated: 32 MB
06/18/2010 17: 35: 26, Large Page Granularity: 2097152
06/18/2010 17: 35: 26, Large Page Extensions enabled.
06/18/2010 17: 35: 26, Detected 32 CPUs.
06/18/2010 17: 35: 26, SQL Server is starting at normal priority base (=7) .
06/18/2010 17: 35: 26, Authentication mode is WINDOWS-ONLY.
06/18/2010 17: 35: 26, Microsoft SQL Server 2008 R2 (RTM)-10.50.1600.1 (X 64)
Copyright (c)
Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X 64>(Build 7600:)
39
並列処理の最大限度の設定
40
tempdb データ ファイルの分割
41
本稼働移行後の継続的な性能監視
-高負荷時間帯の継続的な監視 CPU 利用状況-
42
本稼働移行後の継続的な性能監視
-高負荷時間帯の継続的な監視 ユーザー接続数-
43
本稼働移行後の継続的な性能監視
-高負荷時間帯の継続的な監視 バッチ数-
44
本稼働移行後の継続的な性能監視
-メモリ上のクエリ プランからの分析text
cacheobj
type
objtype
218,493
155,648 Compiled
Plan
Prepared
(@I_ID nvarchar (4)) SELECT I. I_TITLE, A. A_FNAME, A. A_LNAME, I. I_SUBJECT, I. I_IMAGE, I. I_DESC, I. I_COST, I.
I_SRP, I. I_SRP-I. I_COST DISCOUNT, I. I_BACKING, I. I_PAGE, I. I_PUBLISHER, I. I_PUB_DATE, I. I_AVAIL, I.
I_DIMENSIONS, I. I_ISBN FROM ITEM I with (NOLOCK) , AUTHOR A with (NOLOC
46,767
32,768 Compiled
Plan
Prepared
(@I_ID nvarchar (3)) SELECT I_ID, I_THUMBNAIL FROM ITEM WHERE (I_ID= (SELECT I_RELATED 1 FROM ITEM
WHERE I_ID=@I_ID) OR I_ID= (SELECT I_RELATED 2 FROM ITEM WHERE I_ID=@I_ID) OR I_ID= (SELECT I_RELATED
3 FROM ITEM WHERE I_ID=@I_ID) OR I_ID= (SELECT I_RELATED 4 FROM ITEM
21,724
40,960 Compiled
Plan
Prepared
(@A_LNAME nvarchar (15)) SELECT TOP 50 A_FNAME, A_LNAME, I_ID, I_TITLE FROM ITEM I with (NOLOCK) ,
AUTHOR A with (NOLOCK) WHERE (I. I_A_ID = A. A_ID) AND (A_LNAME LIKE @A_LNAME) ORDER BY I. I_TITLE ASC
17,575
49,152 Compiled
Plan
Prepared
(@I_TITLE nvarchar (16)) SELECT TOP 50 A_FNAME, A_LNAME, I_ID, I_TITLE FROM ITEM I with (NOLOCK) ,
AUTHOR A with (NOLOCK) WHERE (I. I_A_ID = A. A_ID) AND (I. I_TITLE LIKE @I_TITLE) ORDER BY I. I_TITLE ASC
17,497
622,592 Compiled
Plan
Prepared
17,234
16,384 Compiled
Plan
Prepared
8,687
24,576 Compiled
Plan
Prepared
(@I_ID nvarchar (4)) SELECT I_ID, I_THUMBNAIL FROM ITEM WHERE (I_ID= (SELECT I_RELATED 1 FROM ITEM
WHERE I_ID=@I_ID) OR I_ID= (SELECT I_RELATED 2 FROM ITEM WHERE I_ID=@I_ID) OR I_ID= (SELECT I_RELATED
3 FROM ITEM WHERE I_ID=@I_ID) OR I_ID= (SELECT I_RELATED 4 FROM ITEM
(@C_ID nvarchar (9)) SELECT C_LNAME, C_FNAME FROM CUSTOMER WHERE C_ID=@C_ID
(@I_ID 1 nvarchar (4)) SELECT I. I_ID, I. I_COST, I. I_SRP, I. I_TITLE, I. I_BACKING FROM ITEM I, ITEM J WHERE J.
I_ID = @I_ID 1 AND J. I_RELATED 1 = I. I_ID
(@C_ID nvarchar (8)) SELECT C_LNAME, C_FNAME FROM CUSTOMER WHERE C_ID=@C_ID
size_in_bytes
6,401
16,384 Compiled
Plan
Prepared
(@I_ID nvarchar (4)) SELECT I_ID, I_COST, I_SRP, I_TITLE, I_BACKING FROM ITEM WHERE I_ID = @I_ID
6,278
16,384 Compiled
Plan
Prepared
(@I_ID nvarchar (3)) SELECT I. I_TITLE, A. A_FNAME, A. A_LNAME, I. I_SUBJECT, I. I_IMAGE, I. I_DESC, I. I_COST, I.
I_SRP, I. I_SRP-I. I_COST DISCOUNT, I. I_BACKING, I. I_PAGE, I. I_PUBLISHER, I. I_PUB_DATE, I. I_AVAIL, I.
I_DIMENSIONS, I. I_ISBN FROM ITEM I with (NOLOCK) , AUTHOR A with (NOLOC
5,587
32,768 Compiled
Plan
Prepared
(@SYSDATETIME nvarchar (4,000) , @EXPIRATIONDATETIME nvarchar (4000) , @C_ID nvarchar (9)) UPDATE
CUSTOMER SET C_LOGIN = @SYSDATETIME, C_EXPIRATION = @EXPIRATIONDATETIME WHERE C_ID = @C_ID
4,207
24,576 Compiled
Plan
Prepared
4,000
24,576 Compiled
Plan
Prepared
4,000
3,563
3,409
16,384 Compiled
16,384 Compiled
40,960 Compiled
Plan
Plan
Plan
Prepared
Adhoc
Prepared
(@SCL_I_ID nvarchar (4) , @I_STOCK nvarchar (2)) UPDATE ITEM SET I_STOCK = @I_STOCK WHERE I_ID =
@SCL_I_ID
(@SCL_I_ID nvarchar (4)) SELECT I_STOCK FROM ITEM WHERE I_ID = @SCL_I_ID
SELECT MAX (O_ID) AS MAX_O_ID FROM ORDERS with (NOLOCK)
(@1 varchar (8,000)) SELECT [I_ID][I_COST] FROM [ITEM] WHERE [I_ID] =@1
45
usecounts
本稼働移行後の継続的な性能監視
-継続的なインデックス チューニングの実施テーブル名
インデックス名
IOT_CUSTOMER PK_IOT_CUSTOMER
COUNTRY
PK_COUNTRY
idx_CountryName
AUTHOR
PK_AUTHOR
ADDRESS
PK_ADDRESS
idx_addr_co_id
idx_addr_zip
ITEM
CUSTOMER
ORDERS
CC_XACTS
ORDER_LINE
MM 1
46
属性
PK_Clustered
PK_Clustered
NonClustered
PK_Clustered
PK_Clustered
NonClustered
NonClustered
Inclued 列
インデックス列
C_ID
CO_ID
CO_ID, CO_NAME
A_ID
ADDR_ID
ADDR_CO_ID
ADDR_ZIP
ADDR_STREET 1, ADDR_STREET
idx_getExistingAddr
NonClustered
2, ADDR_CITY, ADDR_STAATE,
ADDR_ZIP
PK_ITEM
PK_Clustered
I_ID
idx_i_subject
NonClustered
I_SUBJECT
NC_IDX_ITEM_A_ID_1
NonClustered
I_A_ID
I_ID, I_TITLE
NC_IDX_ITEM_ID_1
NonClustered
I_ID
I_COST
I_ID, I_TITLE, I_A_ID,
NC_IDX_ITEM_SUBJECT_1 NonClustered
I_SUBJECT
I_PUB_DATE
PK_CUSTOMER
PK_Clustered
C_ID
idx_c_uname
NonClustered
C_UNAME
PK_ORDERS
PK_Clustered
O_ID
PK_CC_XACTS
PK_Clustered
CX_O_ID
PK_ORDER_LINE
PK_Clustered
OL_ID, OL_O_ID
idx_ol_i_id
NonClustered
OL_I_ID
UCL_IDX_MM 1
UNIQUE_Clustered I_ID
NC_IDX_MM 1_I_SUBJECT NonClustered
I_SUBJECT
まとめ
-Mission Critical 分野への取り組み検証ラボでのベンチマークテストの実施
NUMA アーキテクチャの検証
マルチコア化のスケール アップ検証
最新ハードウエア環境で SQL Server の検証
金融系 B 2 C 企業での継続的な性能診断の実施
国内最大級の x 64 NUMA 環境
最新のサーバーとストレージサブシステム導入を積極的に実施
サービス内容拡大時の事前アセスメント
ビーク時の性能診断とチューニングの継続的な実施
ベスト プラクティスの作成
ベンチマークテスト、実際の導入事例を、セミナー形式で
提供中
パートナー様への、SQL Server プロフェショナルの育成
データベース アプリケーション設計
SQL Server データベース物理設計
パフォーマンス測定とチューニング
47
関連セッション
T4-305: スケールしないシステムにおける開発者の過ち
～ SQL Server の場合～
TH-401: SQL Server 動的管理ビューとパフォーマンス カウンター Part I
TH-402: SQL Server 動的管理ビューとパフォーマンス カウンター Part II
TH-403: SQL Server 現役サポート エンジニア直伝
～ SQL Server トラブル シューティング実践編～
48
リファレンス
SQL Server Customer Advisory Team
http://sqlcat.com/default.mspx
マイクロソフトの真・Dr. K’s SQL Server チューニング研修
http://www. atmarkit.co.jp/fdb/rensai/10_drk/drk01.html
SQL Server 2008 R2 自習書シリーズ
http://www.microsoft.com/japan/sqlserver/2008/r2/technology/self-learning.mspx
SQL Server 2008 徹底検証シリーズ
http://www.microsoft.com/japan/sqlserver/2008/r2/technology/cqi.mspx
49
ご清聴ありがとうございました。
T4-401
アンケートにご協力ください。
© 2010 Microsoft Corporation. All rights reserved. Microsoft, Windows and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.
The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should
not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS,
IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.