认识TEMPDB

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

  • 用户对象：包括用户显示创建的本地/全局临时表、表变量、表值函数、游标、临时存储过程等

  • 内部对象：tempdb中具体的内部对象一般是不可见的，因为它们的meta信息只存于内存中并没有harden，所以系统视图也查询不到，大体分为work table、work file、sort units三类

  • 版本存储：包括联机索引、多活动结果集、after触发器以及使用快照的隔离级别都会在TEMPDB存储旧的行版本

常见场景

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

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

  1. select * from sys.sysprocesses
  2. where spid>50
  3. and lastwaittype<>'MISCELLANEOUS'
  4. and status<>'sleeping'
  5. 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的数据文件和正常业务数据库分开存储，配置在不同的物理设备上；日志文件建议和数据文件分开存储，业务数据库的日志文件也建议这样

  • 当实例分配的CPU个数小于8时，数据文件的个数调整到和实例绑定CPU个数一致；当分配的CPU个数大于8且存在系统页的闩锁争抢时按照4的倍数增加文件直到瓶颈不在闩锁上

    1.   --获取实例分配的CPU个数
    2.   USE [master]
    3.   GO
    4.   SELECT COUNT(*) AS CPU_NUM FROM SYS.DM_OS_SCHEDULERS WHERE IS_ONLINE=1 AND CPU_ID<255
    5.   GO

  • 数据和日志文件的最大空间根据物理设备大小配置到最大值或者不限

    1.   --不限文件大小
    2.   USE [master]
    3.   GO
    4.   ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', MAXSIZE = UNLIMITED)
    5.   GO

  • 尽量避免SHRINKING TEMPDB哪怕只是某个FILE

    1.   USE [tempdb]
    2.   GO
    3.   DBCC SHRINKFILE (TEMPDEV, 'TARGET SIZE IN MB')

  • 启用数据和日志文件的自动增长，配置所有数据文件的增长速度一致、初始大小一致；这里还有一种说法是评估设定好应用所需的TEMPDB稳定空间不做自动增长或者靠监控在业务低峰主动调整，当然如果能做到这一点那是很好的，但这需要应用非常稳定并做过长时间测试能够找准这个稳定空间并做好监控和主动扩容，在实际环境中这基本是很难达到的，所以配置自增长是在实际生产环境中更推荐的做法

  • 如果不是物理设备的空间或性能异常请保证只有一个日志文件

  • 开启Trace Flag 1118缓解SGAM页的争抢 DBCC TRACEON (1118, -1)

  • 如果有这样的场景：PAGELATCH_UP对应资源都是同一个FILE（FILE_ID相同），那么可能是由于数据文件大小非常不均衡使proportional fill algorithm一直在一个文件上分配空间，这种情况可以考虑使用黑科技开启Trace Flag 1117解决，但同时会造成空间消耗，因为这是实例级别的参数不止作用于TEMPDB，所有DB的数据文件都会统一增长 DBCC TRACEON (1117, -1)

空间监控

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

  1. --分类获取TEMPDB总空间使用
  2. Select
  3.     SUM ( user_object_reserved_page_count)*8 as user_objects_kb,
  4.     SUM ( internal_object_reserved_page_count)*8 as internal_objects_kb,
  5.     SUM ( version_store_reserved_page_count)*8  as version_store_kb,
  6.     SUM ( unallocated_extent_page_count)*8 as freespace_kb
  7. From sys .dm_db_file_space_usage
  9. --获取每个会话对TEMPDB用户对象和内部对象的使用空间
  10. --排查用户对象和内部对象使用空间异常的问题
  11. select
  13. t1.session_id ,
  15. (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)] ,
  17. (t1. internal_objects_alloc_page_count + task_internal_alloc )*8 AS [SPACE Allocated FOR Internal Objects (in KB)],
  19. (t1. internal_objects_dealloc_page_count + task_internal_dealloc )*8 as [SPACE Deallocated FOR Internal Objects (in KB)],
  21. (t1. user_objects_alloc_page_count + task_user_alloc )*8 as [SPACE Allocated FOR USER Objects (in KB)],
  23. (t1. user_objects_dealloc_page_count + task_user_dealloc )*8 as [SPACE Deallocated FOR USER Objects (in KB)],
  25. DB_NAME( t1.database_id ) AS [ DATABASE NAME ],
  27. t3.HOST_NAME AS [ System NAME ] ,
  29. t3.program_name AS [ Program NAME ] ,
  31. t3.login_name AS [ USER NAME ],
  33. t3.STATUS ,
  35. t3.cpu_time AS [ CPU TIME(IN milisec) ],
  37. t3.total_scheduled_time AS [ Total Scheduled TIME(IN milisec) ],
  39. t3.total_elapsed_time AS [ Elapsed TIME(IN milisec) ],
  41. (t3. memory_usage * 8 ) AS [ Memory USAGE (IN KB) ]
  43. from sys .dm_db_session_space_usage as t1,
  45. ( select session_id,
  47. sum(internal_objects_alloc_page_count ) as task_internal_alloc,
  49. sum(internal_objects_dealloc_page_count ) as task_internal_dealloc,
  51. sum(user_objects_alloc_page_count ) as task_user_alloc,
  53. sum(user_objects_dealloc_page_count ) as task_user_dealloc
  55. from sys .dm_db_task_space_usage group by session_id) as t2,
  57. sys.dm_exec_sessions as t3
  59. where t1. session_id = t2 .session_id
  61. and t2. session_id = t3 .session_id
  63. and t1. session_id > 50
  65. order by [SPACE Allocated FOR ALL Objects (in KB)] desc
  67. --获取行版本的信息
  68. --排查行版本使用异常的问题
  69. SELECT session_id,elapsed_time_seconds as elapsed_time,* FROM sys.dm_tran_active_snapshot_database_transactions ORDER BY elapsed_time_seconds DESC