标签归档:tempdb

mssq1 sqlserver 查询tempdb数据库所涉及的sql信息


摘要:
tempdb数据库过大,通常是由于创建大量的临时对象,导致tempdb缓存大量数据,
下文将讲述通过系统对象获取tempdb数据库过大所涉及的sql脚本信息,如下所示:

--获取前50条占用 
use tempdb  
go  

SELECT top 10 t1.session_id,                                                      
t1.internal_objects_alloc_page_count,  t1.user_objects_alloc_page_count,  
t1.internal_objects_dealloc_page_count , t1.user_objects_dealloc_page_count,  
t3.login_name,t3.status,t3.total_elapsed_time  
from sys.dm_db_session_space_usage  t1   
inner join sys.dm_exec_sessions as t3   
on t1.session_id = t3.session_id   
where (t1.internal_objects_alloc_page_count>0   
or t1.user_objects_alloc_page_count >0  
or t1.internal_objects_dealloc_page_count>0   
or t1.user_objects_dealloc_page_count>0)  
order by t1.internal_objects_alloc_page_count desc  

--根据sessionId获取相应的sql脚本信息
select s.text,p.*
from master.dbo.sysprocesses p   
cross apply sys.dm_exec_sql_text(p.sql_handle) s  
where spid = 63  

mssql sqlserver tempdb优化误区


摘要:
下文讲述tempdb创建文件个数的建议。



需要创建多个数据文件的检测方法

当tempdb出现大量的分配页竞争时,此时我们需创建多个数据文件,
如果我们使用系统视图 sys.dm_os_wait_stats ,查看系统等待类型,出现大量的PAGELATCH时,代表出现分配页竞争,此时我们需关注tempdb数据库的各项性能

tempdb数据文件增加个数

在sqlserver 2000中,我们可以调整tempdb数据文件的个数同CPU内核数的个数一致,来达到最优的IO效果
在sqlserver 2005及以后的版本中,sql引擎进行了相关优化,此时数据文件和CPU内核数为1:4 或 1:2

tempdb数据文件一定可以解决pageLAtch等待吗?

由于增加的数据文件都存放在磁盘上,所以我们每次临时对象的申请操作,都会涉及磁盘操作,当我们创建一个超大对象时,此时并不能通过数据文件的个数来解决此竞争,
我们还需加大sql引擎的内存,使大量的临时对象操作基于内存上,可一定程度上减少pageLATCH的等待时间

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:
作为开发人员,需减少临时对象的创建和使用,减少系统的开销,使系统并发达到最大