数据库内核月报

数据库内核月报 - 2016 / 09

SQLServer · 最佳实践 · TEMPDB的设计

Author: 天铭

认识TEMPDB

TEMPDB和其他数据库一样以MODEL库为模板创建,是一个全局资源,可供所有连接到实例的用户使用,不同的是它在每次SQL Server启动的时候都会被重新创建,它主要存储三种对象:

常见场景

了解了TEMPDB存储了哪些东西就可以知道它对于整个实例的重要性,尤其是性能上的影响,一些高并发场景很容易凸显问题。

比如在生产环境中出现这样的场景——应用频繁的创建和销毁临时表

select * from sys.sysprocesses
where spid>50
and lastwaittype<>'MISCELLANEOUS'
and status<>'sleeping'
and spid<>@@SPID

screenshot 500+的session挂起,这时候业务已经无法正常提供服务了,每个session都在等PAGELATCH_UP(lastwaittype)对应资源是 2:*:2(waitresource)。

SQLServer的 PAGELATCH:PAGEPATCH是同步访问数据库PAGE的Latch,SQL server的BP里每个数据页(8K)都有一个对应的LATCH,要访问某个PAGE必须首先获得这个PAGE的LATCH,PAGELATCH有很多种,如共享的PAGELATCH_SH,独占的PAGELATCH_EX,更新的PAGELATCH_UP。

waitresource 2:*:2 分别表示database_id,file_id,page_id 对应资源是tempdb的某个datafile第二个数据页;了解SQLServer的存储结构可以知道datafile的前几个page是固定的系统page,第二个PAGE既是固定的全局分配映射页(GAM),TEMPDB做统一区分配的时候会用到。

这个场景是因为对临时表操作的并发过高,TEMPDB在系统页上出现严重争抢导致整个实例卡慢从而影响业务,是一个非常典型的场景,解决方法最好从TEMPDB的规划设计入手。

优化建议

空间监控

最后,现实场景中即使对TEMPDB做好了规划也不排除应用异常使用导致的各种问题,所以合理的监控是必不可少的。 SQLServer针对TEMPDB提供了一些视图信息方便我们监控排查,主要了解sys.dm_db_file_space_usage、sys.dm_db_session_space_usage、sys.dm_db_task_space_usage这三个就可以帮我们做好监控。

--分类获取TEMPDB总空间使用
Select
	SUM ( user_object_reserved_page_count)*8 as user_objects_kb,
	SUM ( internal_object_reserved_page_count)*8 as internal_objects_kb,
	SUM ( version_store_reserved_page_count)*8  as version_store_kb,
	SUM ( unallocated_extent_page_count)*8 as freespace_kb
From sys .dm_db_file_space_usage

--获取每个会话对TEMPDB用户对象和内部对象的使用空间
--排查用户对象和内部对象使用空间异常的问题
select

t1.session_id ,

(t1. internal_objects_alloc_page_count + task_internal_alloc+t1. user_objects_alloc_page_count + task_user_alloc)*8 as [SPACE Allocated FOR ALL Objects (in KB)] ,

(t1. internal_objects_alloc_page_count + task_internal_alloc )*8 AS [SPACE Allocated FOR Internal Objects (in KB)],

(t1. internal_objects_dealloc_page_count + task_internal_dealloc )*8 as [SPACE Deallocated FOR Internal Objects (in KB)],

(t1. user_objects_alloc_page_count + task_user_alloc )*8 as [SPACE Allocated FOR USER Objects (in KB)],

(t1. user_objects_dealloc_page_count + task_user_dealloc )*8 as [SPACE Deallocated FOR USER Objects (in KB)],

DB_NAME( t1.database_id ) AS [ DATABASE NAME ],

t3.HOST_NAME AS [ System NAME ] ,

t3.program_name AS [ Program NAME ] ,

t3.login_name AS [ USER NAME ],

t3.STATUS ,

t3.cpu_time AS [ CPU TIME(IN milisec) ],

t3.total_scheduled_time AS [ Total Scheduled TIME(IN milisec) ],

t3.total_elapsed_time AS [ Elapsed TIME(IN milisec) ],

(t3. memory_usage * 8 ) AS [ Memory USAGE (IN KB) ]

from sys .dm_db_session_space_usage as t1,

( select session_id,

sum(internal_objects_alloc_page_count ) as task_internal_alloc,

sum(internal_objects_dealloc_page_count ) as task_internal_dealloc,

sum(user_objects_alloc_page_count ) as task_user_alloc,

sum(user_objects_dealloc_page_count ) as task_user_dealloc

from sys .dm_db_task_space_usage group by session_id) as t2,

sys.dm_exec_sessions as t3

where t1. session_id = t2 .session_id

and t2. session_id = t3 .session_id

and t1. session_id > 50

order by [SPACE Allocated FOR ALL Objects (in KB)] desc

--获取行版本的信息
--排查行版本使用异常的问题
SELECT session_id,elapsed_time_seconds as elapsed_time,* FROM sys.dm_tran_active_snapshot_database_transactions ORDER BY elapsed_time_seconds DESC