A24 SQL Server におけるパフォーマンスチューニング手法 - 注目すべきポイントを簡単に by 多田典史

SQL Serverにおける
パフォーマンスチューニング手法
~注目すべきポイントを簡単に~

Microsoft Corporation
SQL Server Customer Advisory Team
Principal Program Manager
Yorihito Tada
Yorihito.Tada@microsoft.com
1

SQLCAT (Customer Advisory Team)
SQL Server Customer Advisory Team (SQL CAT) は SQL Server の製品開発グループを
代表して顧客プロジェクトを支援するチームです。SQLCAT はワールドワイドで大規模で
複雑なプロジェクトに参加しています。

 お客様プロジェクトの成功
 Bwin–ヨーロッパで最もポピュラーなアミューズメントサイト、
30,000 万トランザクション/秒、100 TB トータルストレージ
 Temenos–銀行勘定系パッケージベンダー; 1 TB DB, 100 k
batch requests/sec
 プロダクトの改善
 顧客プロジェクトへの深いかかわりから、プロダクトへのフィード
バックを SQL Server 開発チームに伝えます
 コミュニティへの貢献
 http://sqlcat.com
 SEAS (SQL Server Enterprise Architecture Summit) の開催、
PASS Summit などへの貢献
2

アジェンダ
 SQL Serverのパフォーマンスとは
 SQL Serverのボトルネック分析と対策
 Wait
 I/O
 CPU
 メモリ

3

パフォーマンス？

 パフォーマンス
 スループット
 単位時間あたりの処理数
 レイテンシ
 処理にかかる時間

 パフォーマンスチューニング
 遅いデータベースを速くする
 速いデータベースを作る

4

パフォーマンス問題

 リソースのボトルネック
 ボトルネックの特定
 解消

 リソース
 ストレージ
 CPU
 メモリ
 リソースのブロッキング (Wait)

5

Wait
ボトルネックの分析と対策

6

ボトルネックの分析

 手順を追って分析する
 どこに時間がかかっているかを理解する
 経過時間 = 実行時間＋待ち時間
 待ち時間がポイント – どういう待ちがあるか
 通常の状態を把握する
 一番待っているのは誰か

 以下のようなことを考えながら分析
 どこに時間がかかっているか
 CPUはなぜ使われていないか
 いくつ並列に動作できるか
 どうやって測るか

7

select * from sys.dm_os_wait_stats
order by wait_time_ms desc
waiting_ta wait_time_ms max_wait_ti signal_wait
sks_count me_ms _time_ms

LOGMGR_QUEUE 4048 562390 144 31

DIRTY_PAGE_POLL 5149 562355 117 43
HADR_FILESTREAM_IOMGR_IOCO
MPLETION 1102 561541 516 12

LAZYWRITER_SLEEP 557 561412 1016 22
SQLTRACE_INCREMENTAL_FLUSH_S
LEEP 141 561221 4015 1

XE_TIMER_EVENT 129 561092 5014 560824

REQUEST_FOR_DEADLOCK_SEARCH 113 560870 5015 560870

CHECKPOINT_QUEUE 2 485470 485470 0

XE_DISPATCHER_WAIT 5 480244 120219 0

BROKER_TO_FLUSH 273 281650 1039 22

8

典型的なWait

 LCK_<X> による長い待ち
 誰が長くロックしているか
 WRITELOG による長い待ち
 ログディスクのパフォーマンスは？
 PAGELATCH_<X> - ホットスポット
 特定のページへのアクセス集中
 SOS_SCHEDULER_YIELD
 CPUリソース不足または使い過ぎ
 LATCH_<X>
 さらにsys.dm_os_latch_statsを調査する必要がある
 合計待ち時間が長い ASYNC_NETWORK_IO or OLEDB
 外部のプログラムが遅い？インタラクションが多すぎる？
 CXPACKET
 並列化と同時に発生。無視。

9

I/O

10

ディスクIOボトルネック分析

 I/Oのボトルネックをモニタして特定
 リソースモニタ
 パフォーマンスモニタ
 DMV

 何がI/O負荷を上げているか
 悪いクエリプラン
 多くの読み込み・書き込み
 ストレージの性能が低い

11

リソースモニター

ファイル毎にI/O
スループットと
ディスクのレスポ
ンス時間

12

パフォーマンスモニタ

現在のパフォーマン
スのモニタリング

どのカウンタを見るか

13

I/O関連カウンタ

 PhysicalDisk Object:
 Avg. Disk Queue Length
 Avg. Disk Sec/Read
 Avg. Disk Sec/Write
 %Disk Time
 Disk Transfers/Sec
 Avg. Disk Bytes/Read
 Avg. Disk Bytes/Write
 Disk Read Bytes/Sec
 Disk Write Bytes/Sec

14

I/O関連カウンタ

 SQL Server Buffer Manager Object:
(メモリ不足がI/O負荷を上げる場合あり)
 Buffer Cache hit ratio
 Page Life Expectancy
 Checkpoint pages/sec
 Lazywrites/sec

 SQL Server:Wait Statistics
 Log buffer waits
 Log write waits
 Page IO latch waits

15

DMVやログも観察

高い I/O ラッチ
sys.dm_os_wait_stats
sys.dm_exec_requests
待ち時間

待ち/停止中の sys.dm_io_pending_io_requests
sys.dm_io_virtual_file_stats
I/O

ERRORLOG へ 15 秒以上I/O完了が遅れ
ると警告
の警告

16

次になにをするか
 どのクエリが負荷を上げているか特定してチュー
ニング
 インデックスが効かない？
 不足？
 あるのに効かない？
 統計情報が古い？型変換？
 クエリプラン等を分析

 メモリが不足していないか確認
 圧縮でI/O負荷が下げられるか検討
 ストレージデバイスのアップグレード
 ファイルの再配置
 ディスクの追加
17

I/O TOP 10

SELECT TOP 10
(qs.total_logical_reads + qs.total_logical_writes)
/qs.execution_count as [Avg IO],
substring (qt.text,qs.statement_start_offset/2,
(case when qs.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else qs.statement_end_offset end -
qs.statement_start_offset)/2)
as query_text,
qt.dbid,
qt.objectid
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt
ORDER BY [Avg IO] DESC

18

CPU

19

CPU のボトルネック分析

 継続的に%Processor Timeが80%以上？
 タスクマネージャ
 DMV

 なにがCPUを使っているか
 リコンパイル
 カーソル
 悪いクエリプラン
 その他

20

CPU 関連カウンタ

 Processor object:
 Processor Information object:
 %Privileged Time
 %Processor Time
 %User Time

 Process object (SQL Server):
 %Privileged Time
 %Processor Time
 %User Time

21

CPU 負荷の原因に関連するカウンタ

 SQL Server:SQL Statistics
 Batch requests/sec
 SQL Compilations/sec
 SQL Re-Compilations/sec

 SQL Server:Cursor Manager by Type
 SQL Server:Cursor Manager Total
複数同時のカーソル処理
はCPU負荷の原因となる

22

CPU関連DMV

sys.dm_exec_query_stats

sys.dm_exec_sessions sys.dm_exec_requests sys.dm_exec_procedure_stats sys.sysprocesses
sys.dm_exec_trigger_stats

cpu_time cpu_time total_worker_time
cpu

total_scheduled_t total_elapsed_tim
total_elapsed_time
ime e

Backward
total_elapsed_tim Sql_handle/query compatibility view
query_hash
e _hash

23


 CPU使用率が高いこと自体は問題ではないかもし
れない
 CPU使用率の高いクエリを特定してチューニング
 コンパイル・リコンパイルが多数
 SETオプション、TEMPテーブル、統計情報、クエリヒント
 効率の悪いプラン
 インデックス、統計情報、クエリヒント

 ハードウエアアップグレード

24

CPU TOP 10
SELECT TOP 10
SUM(query_stats.total_worker_time) AS "Total CPU Time",
SUM(query_stats.total_worker_time) /
SUM(query_stats.execution_count) AS "Avg CPU Time",
MIN(query_stats.statement_text) AS "Statement Text"
FROM
(SELECT QS.*,
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as
query_stats
GROUP BY query_stats.query_hash
ORDER BY 1 DESC;
25

メモリ

26

メモリのボトルネック分析

 メモリ負荷をモニタして特定
 タスクマネージャ
 DMV

 何がメモリを圧迫しているか？
 外部？内部？
 悪いクエリプラン？

27

メモリ関連カウンタ

 Memory object:
 Available K/M bytes
 Commit Limit
 Paging File %Usage (Peak)
 Page reads/sec

 Process object:
 Private bytes
 Virtual bytes
 Working set

28

メモリ関連カウンタ
 SQL Server Memory Manager object:
 Connection Memory
 Lock Memory
メモリ待ちのタスクあり
 Optimizer Memory
 Memory Grants Pending
 …
 SQL Server Buffer Manager object:
 Buffer cache hit ratio
 Checkpoint pages/sec
 Free list stalls/sec
 Free pages
 Lazy writes/sec
 Page life expectancy
 Stolen pages
キャッシュの寿命は？
 Target pages
 Total pages
 SQL Server Plan Cache object
 SQL Server Resource Pool Stats object

29

DBCC と DMVs

 DBCC MEMORYSTATUS
 sys.dm_os_sys_memory
 system_memory_state_desc

 sys.dm_os_process_memory
 process_physical_memory_low = 1 は物理メモリ不足
 memory_utilization_percentage が100%を下回っている場
合には外部からのメモリプレッシャーあり。
 sys.dm_os_memory_clerks

30


 メモリ構成をチェック – sp_configure
 max server memory (MB)

 外部からのメモリ圧迫
 他のどのプロセスがメモリを圧迫しているか

 内部のメモリ圧迫
 どのコンポーネントが一番メモリを消費しているか

 クエリチューニング
 メモリ追加

31

おわりに

 手順どおりに分析
 複数のツールからの情報の関連に注目する
 １つのボトルネックを潰すと次が顕在化
 リソース競合はお互いに影響
 メモリ圧迫＝＞CPUやI/Oの高負荷
 I/Oが遅い＝＞Wait多発
 とか

32
Page 32

A24 SQL Server におけるパフォーマンスチューニング手法 - 注目すべきポイントを簡単に by 多田典史

More Related Content

What's hot

Viewers also liked

Similar to A24 SQL Server におけるパフォーマンスチューニング手法 - 注目すべきポイントを簡単に by 多田典史

More from Insight Technology, Inc.

Recently uploaded

A24 SQL Server におけるパフォーマンスチューニング手法 - 注目すべきポイントを簡単に by 多田典史

Editor's Notes