【SQL server】ラッチ状況の確認

SQLserver ラッチ待ちの解説と確認

SQLserverには3種類のラッチが存在します。

1.LACTH_[xx] (非バッファーラッチ)
2.PAGELATCH_[xx] (バッファーラッチ)
3.PAGEIOLATCH_[xx] (バッファーラッチ)
※[xx]にはSH(参照),UP(更新),EX(排他)などが入ります。

これらについて主にどのような時に発生するのか解説します。
ラッチ全体としては以下のようなイメージになるかと思います。

f:id:utiowa:20161219011438p:plain

ラッチ全体の状況を確認するSQLは以下でざっくり確認できます。

SELECT 
		CASE 
			WHEN [wait_type] LIKE N'LATCH%'		THEN N'Non_Buffer_Latch'
			WHEN [wait_type] LIKE N'PAGELATCH%'	THEN N'Buffer_Latch'
			WHEN [wait_type] LIKE N'PAGEIOLATCH%'	THEN N'Buffer_Latch'
		END AS [type]
		,* 
	FROM sys.dm_os_wait_stats
	WHERE 　[wait_type] LIKE N'LATCH%' 
	　　OR	[wait_type] LIKE N'PAGELATCH%' 
	　　OR	[wait_type] LIKE N'PAGEIOLATCH%'

1.LACTH_[xx]
メモリ上の非バッファーキャッシュの読み取り時に発生します。様々な種類がありますが、例えばインデックスのB-treeの拡張等で発生します。

上記SQLのtypeが「Non_Buffer_Latch」であるレコードを確認すればどのくらい待ちが発生しているかは判断できますが、さらにラッチの詳細を確認する場合は以下のようなSQLで確認するとよいでしょう。

SELECT  [latch_class],
　　　　[wait_time_ms] AS [wait_time_ms],
	[waiting_requests_count] AS [wait_counts],
	100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [wait_time_Percentage]
　FROM sys.dm_os_latch_stats 
	WHERE [latch_class] NOT IN ( N'BUFFER') AND [wait_time_ms] > 0 
	ORDER BY [wait_time_Percentage] DESC

2.PAGELATCH_[xx]
バッファーキャッシュ上に存在するページの読み取り時に発生します。
tempdbのPFS(Page Free Space)に対する一時オブジェクトのCREATE,DROPや、インデックスの末尾ページへのデータの追加等で見受けられます。

対処法としては以下のようなものが考えられます。
tempdbのPFSに対しては
①tempdbのデータファイル数をCPU数に一致させる。
②tempdbのデータファイル容量をすべて同じサイズにする。
詳しくはMSのData Platform Support Team Blogにtempdbの考え方についての記載がありますのでこちらを見ていただければよいかと。
https://blogs.msdn.microsoft.com/jpsql/2013/01/16/dosdonts-17-tempdb-259/

インデックスページの末尾で発生するPAGELATCH_[xx]については
①同じファイルサイズのデータファイル(.ndf)を追加して処理対象となるデータファイルを分散させる。
②インデックスのキー項目がシーケンシャルな値の場合には追加処理が末尾ページに集中するので、GUIDなどの処理対象が分散するような値をインデックスのキーとして検討する。（業務要件として可能な場合に限る）
③テーブルやインデックスをパーティション分割する。

PAGEIOLATCH_[xx]
これはよく見かけるLATCHかと思います。
読み取り操作時にディスクからバッファーキャッシュへ移動する際に発生します。ゆえにインスタンス起動直後やキャッシュをクリアした後には発生してしまいます。

尚ディスクからキャッシュへのデータページの移動については以下のような順序で実施されます。
①データページの要求
②バッファーキャッシュにデータが存在しない
③バッファーにページ用領域を割り当て
④ディスクからバッファーキャッシュへ移動

対処法としては、以下の観点で確認をするとよいかと思います。

(1)ログからSQLserverが再起動していないか確認
再起動後はキャッシュが空のため、クエリ実行時にPAGEIOLATCHが多く発生します。
これに起因するPAGEIOLATCHの発生はやむを得ないかと思います。

(2)IOが多いクエリがないか確認
不要なIOを伴うクエリはディスクから大量ページをバッファーキャッシュに読み込むため、IOを減らすようチューニングしましょう。

(3)インデックスの断片化が発生していないか確認
断片化により1ページ当たりのデータ格納量が減っている場合、断片化を解消することで読み取る必要があるページ数を削減できます。
こちらに関してはメンテナンスプランで自動化する等して定期的に断片化を解消しましょう。

(4)SQLserverへの割り当てメモリが不足していないか確認
バッファーキャッシュの上限設定(MaxServerMemory)が必要としているメモリと比べ不足している場合、データページがキャッシュアウトされやすいため、ディスクからの読み取りが増加してしまいます。メモリ割り当てを増やすことを検討しましょう。
メモリが不足しているかどうかは別途分析が必要かと思いますが、簡単に以下だけでも閾値として確認しておくとよいでしょう。