与锁的区别
锁用来保证事务隔离及一致性,而latch则保证在内存里的对象的一致性
锁会在事务运行期间持续存在,而Latch则只是短暂存在.
锁可以通过隔离级别或hint控制,而Latch由数据库引擎自动控制.
Latch模式
- SH
Shared Latches:读取页时被使用
- UP
Update Latches:修改页时被使用,但此时页可以被读取
- EX
Exclusive Latches:修改页发生时被使用,但此时页不可被读或修改
- KP
Keep Latches:保护内存中的页不被Destroy Latch清理,与除了DT之外的所有latch兼容
- DT
Destroy Latches:当SQL Server实例想释放内存中的数据页时,或从内存中移除数据页内容.
Latch等待类型
- Buffer Latches
用来保护Buffer cache中的数据页,包括用户的和系统的.
sys.dm_os_wait_stats PAGELATCH_XX XX为模式
- Non-Buffer Latches;
用来保护buffer cache外的数据结构
sys.dm_os_wait_stats LATCH_XX
- IO Latches:
用来从存储子系统读取数据页到buffer cache中
sys.dm_os_wait_stats PAGEIOLATCH_XX
PAGELATCH_XX
- page-latch(PFS page)争用
加数据文件优化
- last page insert争用
分区,或改善造成hot-spot的表设计
Latch_XX
PAGEIOLATCH_XX
#可以通过perfmon或下面的语句监测,它只记录自上一次engine启动到现在的记录,同时确保未使用dbcc dropcleanbuffers清除buffer cache中的数据
#同时要保证碎片和统计信息的及时更新
SELECT
[ReadLatency] =
CASE WHEN [num_of_reads] = 0
THEN 0 ELSE ([io_stall_read_ms] / [num_of_reads]) END,
[WriteLatency] =
CASE WHEN [num_of_writes] = 0
THEN 0 ELSE ([io_stall_write_ms] / [num_of_writes]) END,
[Latency] =
CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
THEN 0 ELSE ([io_stall] / ([num_of_reads] + [num_of_writes])) END,
[AvgBPerRead] =
CASE WHEN [num_of_reads] = 0
THEN 0 ELSE ([num_of_bytes_read] / [num_of_reads]) END,
[AvgBPerWrite] =
CASE WHEN [num_of_writes] = 0
THEN 0 ELSE ([num_of_bytes_written] / [num_of_writes]) END,
[AvgBPerTransfer] =
CASE WHEN ([num_of_reads] = 0 AND [num_of_writes] = 0)
THEN 0 ELSE
(([num_of_bytes_read] + [num_of_bytes_written]) /
([num_of_reads] + [num_of_writes])) END,
LEFT ([mf].[physical_name], 2) AS [Drive],
DB_NAME ([vfs].[database_id]) AS [DB],
[mf].[physical_name]
FROM
sys.dm_io_virtual_file_stats (NULL,NULL) AS [vfs]
JOIN sys.master_files AS [mf]
ON [vfs].[database_id] = [mf].[database_id]
AND [vfs].[file_id] = [mf].[file_id]
-- WHERE [vfs].[file_id] = 2 -- log files
ORDER BY [Latency] DESC
-- ORDER BY [ReadLatency] DESC
-- ORDER BY [WriteLatency] DESC;
GO
清楚记录
- 重启服务
- dbcc sqlperf('sys.dm_os_latch_stats',clear)