Sql server エンジニアに知ってもらいたい!! sql server チューニングアプローチ

本セッションの資料について
db tech showcase 20141
資料のダウンロード先
http://1drv.ms/1iyYQm2

SQL Server エンジニアに
知ってもらいたい!!
SQL Server チューニングアプローチ
Masayuki Ozawa (Microsoft MVP for SQL Server)

自己紹介
 フリーランスエンジニアとして SQL Server の案件を中心
に従事しています
 案件等で協力できることがありましたらお声掛けいただけると幸いです
 Microsoft MVP for SQL Server (July 2011 - June 2014)
 コミュニティやブログで SQL Server の情報を発信
 コミュニティ活動
 SQL Server : SQLTO (http://sqlto.net)
 Azure : JAZUG (http://r.jazug.jp/)
 ブログ : SE の雑記 (http://engineermemo.wordpress.com)

本セッションについて
 本セッションは OS / SQL Server の標準ツールを使
用して、SQL Server のチューニングに必要な情報の
取得 / 解析の一般的な流れについて解説させていた
だきます。
 Sqldiag / SQL Nexus ツール / SQL Live Monitor /
PAL ツールといった情報取得 / 解析ツールは利
用しません。

セッションの流れ
全体最適
休憩 (15 分)
部分最適

今回対象とする環境

みなさんどのようにアプローチしますか??
データベースを格納しているドライブに
大量のディスクキューが発生している

DEMO
前提となる環境の説明

負荷実行のツール
 RML Utilities for SQL Server (x64)
http://www.microsoft.com/en-us/download/details.aspx?id=4511
 Ostress を使用
 SQLQueryStress
http://www.datamanipulation.net/SQLQueryStress/
 Enzo SQL Baseline
 http://enzosqlbaseline.codeplex.com/

2 つのアプローチ
• (機器) 構成の見直し
• パラメーター変更
全体最適 (前半）
• クエリチューニング
• インデックスチューニング
部分最適 (後半)

全体最適 (1)
- ボトルネックの把握 -

どこがネックになっているかを見極める
Application
Server
Storage
CPU
Memory
Storage HBA
Network Card

負荷を正しく把握する
Application
Server
Storage
CPU
Memory
Storage HBA
Network Card
ディスクの負荷が高いので
今回はここが原因??

Resource Balanced Approach
(Balanced System Architecture)
Application
Server
Storage
CPU
Memory
Storage HBA
Network Card
CPU の処理できる I/O を基準に
他のリソースのバランスをとる

情報を組み合わせてみる
 ディスクの負荷が高い = ディスクがボトルネックとは限らない
 ディスク
 書き込み / 読み込みのどちらの頻度が多いか
→ ディスク or メモリがボトルネックの可能性
 CPU
 ユーザー増加によるクエリ実行回数の増加
 頻繁なクエリコンパイルの発生
→ CPU or メモリがボトルネックの可能性
 メモリ
 どの領域のメモリが多いのか
 キャッシュのヒット状況
→ メモリ or ディスクがボトルネックの可能性
 本セッションでは上記 3 つのリソースについての情報を取得してみます。

全体最適 (2)
- 情報の取得 -

情報の取得
OS の情報
サーバーとしての稼働状況
SQL Server の情報
SQL Server の稼働状況

OS の情報 (パフォーマンスモニター)
リソースカウンター
CPU Processor : % Processor Time
System : Processor Queue Length
Process : % Processor Time (sqlservr)
メモリ Memory : Available Mbytes
ディスク Physical Disk : Current Disk Queue Length
Physical Disk : Disk Bytes/sec
Physical Disk : Disk Read Bytes/sec
Physical Disk : Disk Write Bytes/sec

DEMO
パフォーマンスモニターに情報を追加
(OS)

SQL Server の情報
OS SQL Server
CPU クエリ実行時間
クエリコンパイル状況
メモリキャッシュの確保状況
キャッシュヒット率
ディスクキャッシュヒット率
データ (インデックス)アクセス状況

SQL Server の情報 (パフォーマンスモニター)
情報カウンター
クエリ実行時間の統計 SQLServer:Batch Resp Statistics
クエリコンパイル SQLServer:SQL Statistics
SQL Server のメモリの内訳 SQLServer:Memory Manager
キャッシュ利用状況 SQLServer:Buffer Manager
SQLServer:Plan Cache
データのアクセス状況 SQLServer:Access Methods

DEMO
パフォーマンスモニターに情報を追加
(SQL Server)

全体最適 (3)
- 情報のブレークダウン -

SQL Server のメモリ構造
24
Memory Manager : Database Cache Memory (KB)
Plan Cache : Cache Pages
Memory Manager : Connection Memory (KB)
Memory Manager : Granted Workspace Memory (KB)
Memory Manager : Lock Memory (KB)
一時利用
キャッシュ

情報のブレークダウン
 例)データキャッシュのブレークダウン
 パフォーマンスモニターでは、
 SQLServer:Memory Manager¥Database Cache Memory (KB)
 SQLServer:Buffer Manager¥Database pages
 でデータキャッシュとして使用しているメモリを取得できるが、
どのデータベースのキャッシュとして使用しているかまではわか
らない
 パフォーマンスモニターで取得した内容の詳細化は
動的管理ビュー (DMV)

データベースページの比率
 SQLServer:Memory Manager¥Database Cache Memory (KB)/
SQLServer:Buffer Manager¥Database pages はインスタンス単位の
情報のため、データベース単位でのキャッシュ状況を確認するこ
とはできない
 sys.dm_os_buffer_descriptors を使用することで、
「どのデータベース」の「どのオブジェクト」が「どの程度」
メモリ上にキャッシュされているかを確認することができる

クエリの比率
 SQLServer:Plan Cache¥Cache Page から情報が取得できるが、アドホッ
ククエリとパラメータ化クエリの区別がつかない
 アドホッククエリ = 実行のたびにコンパイルされる可能性が高い
→ CPU 負荷とプランキャッシュのメモリ圧迫につながる
 sys.dm_exec_cached_plans を使用することで、クエリの比率の詳細が確
認できる

DEMO
キャッシュの内訳を詳細化

全体最適 (4)
- データベースのアクセス状況 -

データベースのアクセス状況
 メモリ / ディスクのどちらからアクセスしている頻度が高いかを把握
する
 以下のパフォーマンスモニターのカウンターからアクセス傾向を取得
カウンター備考
SQLServer:Buffer Manager¥Page life expectancy キャッシュにページが保持されている秒数
SQLServer:Buffer Manager¥Page reads/sec 物理読み込み
SQLServer:Buffer Manager¥Page Readahead pages/sec 先行読み取りによる物理読み込み
SQLServer:Buffer Manager¥Page lookups/sec 論理読み込み

キャッシュヒット率
 パフォーマンスモニターから取得可能
 SQLServer:Buffer Manager¥Buffer cache hit ratio
 SQLServer:Plan Cache¥Cache Hit Ratio
 Bound Trees : ビュー / 制約
 Extended Stored Procedures : 拡張ストアドプロシージャ
 Object Plans : ストアドプロシージャ
 SQL Plans : アドホッククエリ / パラメータ化クエリ
 Temporary Tables & Table Variables : 一時テーブル / テーブル変数
 キャッシュヒット率は SQL Server のサービスが最後に起動してから平
均値となるため、起動時間が長いとヒット率が平準化することがある

データベースファイルのアクセス状況
 sys.fn_virtualfilestats or sys.dm_io_virtual_file_stats を
使用することでデータベースのファイル単位のアクセス
状況がわかる
 特定のデータベースにアクセスが集中している
 特定のファイルにアクセスが集中している

DEMO
データベースのアクセス状況の取得

全体最適 (4)
- 情報の可視化 -

情報の使い分け
 パフォーマンスモニター
 時系列のサマリーデータを取得
 折れ線 / 積み重ねグラフ
 動的管理ビュー
 特定のタイミングの詳細なデータを取得
 表 / 円グラフ

作成するグラフの例 (1)

作成するグラフの例 (2)

DEMO
全体最適による改善

休憩
前半部分が終了しましたので
15 分程度休憩を挟みます
休憩後に後半の部分最適を
お話させていただきます

部分最適 (1)
- 最適化対象とするクエリ -

部分最適のアプローチ
 50 ms 時間がかかるクエリを 5 ms にする??
 10,000 ms 時間がかかるクエリを 5ms にする??
部分最適のターゲットとしてみる
 50ms のクエリが 200 回程度実行されている場合は、
50ms → 5ms の効果が出てくることもあるので実行頻度も考慮

クエリの実行時間の全体的な統計
 SQLServer:Batch Resp Statistics から、クエリの実行時間の全体的な統計
が確認できる
 SQL Server 2012 で追加されたパフォーマンスモニターのカウンター

部分最適 (2)
- クエリ情報の取得 -

クエリ情報の取得
SQL Server Profiler (SQL Trace)
データコレクション
動的管理ビュー (DMV)
拡張イベント今回はこれらを利用

動的管理ビュー
 DMV : SQL Server が最後に起動してからの情報を確認
 ログとして取得したい場合は定期的に状態をダンプする必要がある
 クエリ関連の情報の場合、キャッシュのクリア / キャッシュアウトされるとキャッ
シュから抜けた情報は取得できない
 クエリ系の DMV
 sys.dm_exec_xxxx 系の DMV を使用 (以下は使用頻度が高い DMV)
 sys.dm_exec_query_stats
 sys.dm_exec_cached_plans
 取得できる情報の例
 実行頻度の高いクエリ
 CPU 使用率の高いクエリ
 I/O の高いクエリ
 プランが生成/実行された時間/リコンパイルされた回数

DEMO
DMV を使用したクエリ情報の取得

拡張イベントを使用した Slow Query Log 取得
 SQL Server 2008 で追加された機能
 SQL Server Profiler より軽量で ETW との連携可能
 SQL Server 2014 では、1,400以上のオブジェクトを利用可能
 SQL Server 2012 から SSMS から容易に作成可能
 拡張イベントでスロークエリログを取得
 http://wp.me/p15G3m-5uf

Slow Query の確認ポイント
 注目するポイント
 リソースの使用率が高いクエリ
 Duration
 CPU Time
 IO
 実行プラン
 コストの高い操作
 インデックスの利用状況
 データ件数の多いテーブルに対してのテーブルスキャン
 開発段階での取得を視野に入れる
 実行速度の遅いクエリを分析するためのチェックリスト
 http://technet.microsoft.com/ja-jp/library/ms177500(v=sql.105).aspx

DEMO
拡張イベントを使用した
Slow Query の取得

部分最適 (3)
- 待ち情報の取得 -

処理のライフサイクル
実行状態
(RUNNING)
実行可能状態
(RUNNABLE)
待機状態
(SUSPENDED)
処理を実行している状態
CPU
メモリ
ディスク
処理を待機している状態
ロック
並列処理同期
ディスク I/O
待機がなくなり処理の開始待ち
スケジューラーの利用可能待ち
signal wait

クエリによる待ちの発生状況
 DMV から取得することが可能
 インスタンスが最後に起動してからの累計値となっているため、必要に応じ
て初期化する
 DBCC SQLPERF(‘<DMV 名>’, CLEAR)
 情報の利用方法の例
 特定の時間帯の上位の待ち事象の発生状況
 待ち事象の平均待機時間
情報 DMV 備考
待ち事象 sys.dm_os_wait_stats インスタンス全体の待ち事象
ラッチ待ち sys.dm_os_latch_stats 待ち事象の LATCH_xx の詳細
スピンロック sys.dm_os_spinlock_stats スピンロックによる待ち事象

拡張イベントの利用
 DMV から取得できる待ち事象は、インスタンスの累計値
 特定のクエリで発生する待ち事象は拡張イベントから取得可能

DEMO
待ち事象の取得

部分最適 (4)
- SQL Server を使用した処理の流れ -

ディスク
SQL Server を使用した処理の流れ
データファイル
ログファイル
CPU
SQL Server
メモリ
アプリケーション

ディスク
ディスクベーステーブルのデータ書き込み
ログファイル
CPU
SQL Server
メモリ
②データの変更
③チェックポイント
①先行ログ書き込み (WAL)
Or
遅延持続性 (非同期)
→ WRITELOG Wait
④ページヘッダの
書き換え
→ PAGELATCH_xxx

ディスク
ディスクベーステーブルのデータ読み込み
ログファイル
CPU
SQL Server
メモリ
②8KB ページ単位での
データ読み込み
→ PAGEIOLATCH Wait
①メモリのデータを
確認

ディスクベーステーブルの I/O
 ディスクベーステーブルの I/O はレコード単位ではなく 8KB ページ単
位で実行される
 ページ内のデータ密度が高ければ、一度の I/O で読み取れるレコード
が増える
 列ストアインデックスを使用することで、列単位での I/O が可能
メモリディスク
DB
Col1 Col2 Col3 Col4
10 100 xxxxxxxx NULL
～省略～
ページ
ページ
ページページ
データをキャッシュする
=
ページをキャッシュする

ディスク
リソースに影響を与える設定
ログファイル
CPU
SQL Server
メモリ
パラメータ化クエリ
ストアドプロシージャ
データ圧縮
インデックス
遅延持続性
データ圧縮
(列ストア)インデックス
In-Memory OLTP
間接チェックポイント
バッファープール拡張

DEMO
データストア設定の変更によるI/Oの変化

部分最適 (5)
- 実行プランの把握 -

実行プランの確認
 実行プランから以下の情報を確認
 インデックスが使用されているか
 クエリによりどの程度のデータが取得されているか
 実行プランの確認方法の例
 SSMS からグラフィカルな実行プランを取得する設定しクエリを実行
 SET STATISTICS PROFILE ON を実行してからクエリを実行
コストの高い操作を把握

インデックスの使用状況
 アクセスコストは一般的には Index Scan > Index Seek
 Table Scan / Clustered Index Scan はテーブルのデータを全件取得
 Clustered Index Scan > Index Scan > Clustered Index Seek > Index Seek
 アクセスコストの高い操作は CPU への負荷も高くなる
 キャッシュされているデータの取得にも CPU コストはかかる
 sys.dm_db_index_usage_stats からインデックスの使用状況を取得できる

ディスクネックから CPU ネックへ
 ディスクの高速化により、CPU ネックになる傾向が出てくる
 処理内容の大小にかかわらず、処理をするには CPU を使用
SET NOCOUNT ON
GO
DECLARE @i bigint
WHILE(0=0)
BEGIN
SET @i += 1
END
 コンパイル済みクエリでも実行されれば CPU は使用される
 キャッシュされているクエリの再実行でも CPU は使用される
 取得データが多ければキャッシュされていても CPU 負荷は上昇する

Clone DB の作成
 データベースのバックアップ / リストアによりデータベースのコピー
ができればベスト
 本番環境のデータベースを開発環境にリストア
 データベースのバックアップを使用すると以下の状態が再現可能
 断片化の発生状況
 統計情報
 データベースのコピーができない場合は、Clone DB を作成することで、
再現環境を作成する
 Clone DB を作成することで、テーブルのスキーマと統計情報をコピーできる
 推定実行プランを使用した実行プランの確認
 How to generate a script of the necessary database metadata to create a statistics-
only database in SQL Server 2005 and in SQL Server 2008
http://support.microsoft.com/kb/914288

DEMO
インデックスの設定によるクエリ最適化
CloneDB の作成

まとめ
 情報を確認して、問題個所を特定する
 CPU / ディスク / メモリ / ネットワークを比較し、
相互の影響を把握
 全体最適と部分最適のどちらでアプローチするか
 全体構成の最適化 / クエリチューニング
 クエリチューニングする際には実行時間が
大きいものから最適化することで効果が大きい
 アクセスコストを意識してデータを取得

Sql server エンジニアに知ってもらいたい!! sql server チューニングアプローチ

More Related Content

What's hot

Similar to Sql server エンジニアに知ってもらいたい!! sql server チューニングアプローチ

More from Masayuki Ozawa

Sql server エンジニアに知ってもらいたい!! sql server チューニングアプローチ