mssql sqlserver tempdb 性能监控及说明


摘要:
tempdb作为数据库引擎运行时,共用资源,当此数据库出现性能问题将直接影响数据库中多种操作的性能,如下所示:



查看tempdb IO是否存在负载

pageLatch竞争:
与普通数据库相比,使用tempdb作为临时存储区域使得工作负载模式可能包含许多不成比例编号的小对象的创建和销毁。
此类工作负载可能导致数据库中需要分配对象的页面上的Latch争用。 Latch是SQL Server的短期同步锁,用于保护物理页面。
您不能通过更改隔离级别或使用正常锁定等提示来影响Latch。 Latch在幕后自动使用。
当从磁盘更改或读取内容或结构时,它会保护内存中的页面免受其他任务的影响。修改。

分配页:

当我们在tempdb中创建一个临时表对象,此时会涉及空间分配,此过程和创建物理表所采用的模式一模一样,所以会涉及以下页信息:
(PFS)Page Free Space、(GAM)Global Allocation Map、(SGAM) Shared Global Allocation Map

PFS (Page Free Space)

pfs为tempdb中的每一页存储一个字节的信息(记载页上的可用空间),一个pfs页面上可以存储64MB的页面信息,
tempdb数据文件中,每64MB将有一个pfs页面
例:2:1:1 表示database_id为2 pfs在第一个数据库文件的第一页上
5:3:1 表示database_id为5 pfs在第三个数据库文件的第一页上

GAM(Global Allocation Map)

GAM页每个范围(一个范围是8个页)存储1比特,表明哪些范围在使用,哪些是空的。
SQL Server通过读取GAM页找到可用空间,并把一整个范围分配给一个对象。一个范围仅存储一个比特(而不是像PFS页,PFS是一个页一个字节),意味着单个GAM页能够追踪更多空间,
在一个数据文件中,你可以在大约4GB间隔的空间找到一个新GAM页。然而,数据文件中的第一个GAM页的页码总是2,因此“2:1:2”就表示tempdb中的第一个GAM页。

SGAM (Shared Global Allocation Map)

SGAM页也是一个范围存储一个比特,但是该值表示该范围是不是与可用空间或一个完整范围混合的范围。
SQL Server通过读取SGAM页来找到与可用空间混合的范围来把空间分配给小对象。
单个SGAM页能够追踪4GB的空间,因此,你可以在4GB的间隔中找到它们,就像GAM页一样。
在数据文件中,第一个SGAM页是页3,所以“2:1:3”就表示tempdb的第一个SGAM页。

分配页竞争(Allocation Page Contention)

当有进程申请创建临时表时,sql引擎会自动判断出tempdb应该在指定的位置创建表,
sqlserver需读取SGAM页找到可用的空间范围分配给表,
再更新sgam中的ex LATCH,再读取pfs,查找可用页分配对象,对pfs加上EX LATCH标志,
确保没有其它对象对同一数据页占用,当LATCH更新完成后,LATCH会被释放
——————————————————————————-
当有多个用户对临时对象进行操作,则需频繁的进行LATCH操作会出现PAGE LATCH等待操作,此时就出现了分配页竞争

分配页竞争及时查看
WITH SGAM_E
AS (SELECT session_id,
wait_type,
wait_duration_ms,
blocking_session_id,
resource_description,
[页面ID] = Cast(Right(resource_description, Len(resource_description)-
Charindex(‘:’, resource_description, 3)) As Int)
From sys.dm_os_waiting_tasks
Where wait_type Like ‘PAGE%LATCH_%’
And resource_description Like ‘2:%’)
——————————————————–
SELECT session_id,
wait_type,
wait_duration_ms,
blocking_session_id,
resource_description,
ResourceType = Case
When PageID = 1 Or PageID % 8088 = 0 Then ‘Is PFS Page’
When PageID = 2 Or PageID % 511232 = 0 Then ‘Is GAM Page’
When PageID = 3 Or (PageID – 1) % 511232 = 0 Then ‘Is SGAM Page’
Else ‘Is Not PFS, GAM, or SGAM page’
End
From SGAM_E ;



降低分配页竞争的方法

方法1:
作为DBA,可以增加多一点的tempdb数据文件,增大SGAM PFS 同时读写
方法2:
作为开发人员,需减少临时对象的创建和使用,减少系统的开销,使系统并发达到最大