tempdb空间暴涨问题

最新推荐文章于 2024-07-22 09:04:26 发布

三思呐三思

最新推荐文章于 2024-07-22 09:04:26 发布

阅读量3.1k

点赞数 2

分类专栏： SQL Server 文章标签： sqlserver tempdb暴涨

本文链接：https://blog.csdn.net/weixin_37692493/article/details/118370836

版权

SQL Server 专栏收录该内容

11 篇文章 1 订阅

订阅专栏

文章目录

一、tempdb数据库介绍
- 1.1 基本概念
- 1.2 限制
二、如何定位tempdb空间暴涨
- 2.1 tempdb库基本信息查询
- 2.2 tempdb空间使用监控
三、如何处理tempdb暴涨

一、tempdb数据库介绍

1.1 基本概念

tempdb数据库是一个全局资源，可供连接到SQL Server数据库的所有用户使用，主要用于以下几方面：

显式创建的临时用户对象

比如全局或者本地的临时表/索引、临时存储过程、表变量、表值函数中返回的表或游标。

数据库引擎创建的内部对象

spool、游标、临时的大对象存储

hash join或者hash 聚合操作的临时文件

创建或者重建索引操作、group by、order by操作产生的内部排序结果，

版本存储

RC隔离级级别、snapshot隔离级别下，由于数据变更产生的多版本数据记录

在线创建索引操作、 Multiple Active Result Sets (MARS)、 AFTER triggers操作产生的多版本数据记录

对于tempdb的所有操作也会被记录到日志中，以便事务回滚。每次实例重启会重建tempdb。对于每个会话线程，当会话链接断开时，会自动删除其持有的临时表、临时存储过程等。也正是由于这样的特性，tempdb数据库是不需要进行备份恢复操作的。

任何用户都可以在tempDB库中创建临时对象，且用户只能访问自己的对象，除非他们获得更多的权限。

model数据库的物理存储信息：

文件	逻辑名称	物理名称	初始大小	文件增长
主数据	tempdev	tempdb.mdf	8 MB	以 64 MB 的速度自动增长直到磁盘已满
次要数据文件*	temp#	tempdb_mssql_#.ndf	8 MB	以 64 MB 的速度自动增长直到磁盘已满
日志	templog	templog.ldf	8 MB	以 64 MB 的速度自动增长直到达到上限 2 TB

1.2 限制

不能对 TempDB 数据库执行以下操作：

添加文件组
备份或还原数据库
更改排序规则。默认排序规则为服务器排序规则
更改数据库所有者。 TempDB 的所有者是 sa
创建数据库快照
删除数据库
从数据库中删除 guest 用户
启用变更数据捕获
参与数据库镜像
删除主文件组、主数据文件或日志文件
重命名数据库或主文件组
运行 DBCC CHECKALLOC
运行 DBCC CHECKCATALOG
将数据库设置为 OFFLINE
将数据库或主文件组设置为 READ_ONLY

二、如何定位tempdb空间暴涨

2.1 tempdb库基本信息查询

1、检查当前tempdb库大小以及增长参数

1）tempdb数据库不可设置过小，若参数设置过小，每次实例启动时都会消耗额外的计算资源对tempdb进行扩展，直到满足当前所需大小

2）tempdb库的数据文件应该设置为自动增长，避免出现意外导致tempdb空间不足

3）tempdb的每个文件组的数据文件大小应保持一致，SQL Server使用比例填充算法，有效提高并行效率

4）将文件增量设置为合理的大小以避免 TempDB 数据库文件的增量过小。如果文件的增量与写入tempDB的数据量相比过小，则tempDB可能需要不断扩大，并且会影响性能。

-- 查看指定数据库下初始化大小、当前大小，自增长设置
SELECT DB_NAME(database_id) AS name, mf.name AS db_filename, mf.physical_name, CAST(mf.size / 128.0 AS DECIMAL(20, 2)) AS initial_size_MB
	, CAST(df.size / 128.0 AS DECIMAL(20, 2)) AS actual_size_MB
	, CASE mf.is_percent_growth
		WHEN 0 THEN STR(CAST(mf.growth / 128.0 AS DECIMAL(10, 2))) + ' MB'
		WHEN 1 THEN STR(mf.growth) + '%'
	END AS auto_grow_setting
FROM sys.master_files mf
	JOIN sys.database_files df ON mf.name = df.name
WHERE mf.database_id = DB_ID()


SELECT name AS FileName,
    size*1.0/128 AS FileSizeInMB,
    CASE max_size
        WHEN 0 THEN 'Autogrowth is off.'
        WHEN -1 THEN 'Autogrowth is on.'
        ELSE 'Log file grows to a maximum size of 2 TB.'
    END,
    growth AS 'GrowthValue',
    'GrowthIncrement' =
        CASE
            WHEN growth = 0 THEN 'Size is fixed.'
            WHEN growth > 0 AND is_percent_growth = 0
                THEN 'Growth value is in 8-KB pages.'
            ELSE 'Growth value is a percentage.'
        END
FROM tempdb.sys.database_files;
GO

2.2 tempdb空间使用监控

1）tempdb空间使用情况监控

-- Determining the Amount of Free Space in TempDB
SELECT SUM(unallocated_extent_page_count) AS [free pages],
  (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

-- Determining the Amount Space Used by the Version Store
SELECT SUM(version_store_reserved_page_count) AS [version store pages used],
  (SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]
FROM sys.dm_db_file_space_usage;

-- Determining the Amount of Space Used by Internal Objects
SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],
  (SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]
FROM sys.dm_db_file_space_usage;

-- Determining the Amount of Space Used by User Objects
SELECT SUM(user_object_reserved_page_count) AS [user object pages used],
  (SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]
FROM sys.dm_db_file_space_usage;

2）监控当前会话或任务tempdb空间占用情况

若要在会话级或任务级监视 tempdb 中的页分配或页释放活动，可以使用 sys.dm_db_session_space_usage 和 sys.dm_db_task_space_usage 动态管理视图。这些视图有助于标识使用 tempdb 中大量磁盘空间的大型查询、临时表或表变量。还可使用若干个计数器来监视 tempdb 中的可用空间以及正在使用 tempdb 的资源。

-- Obtaining the space consumed by internal objects in all currently running tasks in each session
SELECT session_id,
  SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,
  SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count
FROM sys.dm_db_task_space_usage
GROUP BY session_id;

-- Obtaining the space consumed by internal objects in the current session for both running and completed tasks
SELECT R2.session_id,
  R1.internal_objects_alloc_page_count
  + SUM(R2.internal_objects_alloc_page_count) AS session_internal_objects_alloc_page_count,
  R1.internal_objects_dealloc_page_count
  + SUM(R2.internal_objects_dealloc_page_count) AS session_internal_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage AS R1
INNER JOIN sys.dm_db_task_space_usage AS R2 ON R1.session_id = R2.session_id
GROUP BY R2.session_id, R1.internal_objects_alloc_page_count,
  R1.internal_objects_dealloc_page_count;

1）确认当前会话中占用tempdb空间比较大的pid

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 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

session_id ：稍等可以查询该session的相关信息
internal_objects_alloc_page_count  ：分配给session内部对象的数据页
internal_objects_dealloc_page_count ：已经释放的数据页
login_name : 该session的登录名

2）根据pid查看具体SQL语句

SELECT p.*, s.text
FROM master.dbo.sysprocesses p
	CROSS APPLY sys.dm_exec_sql_text(p.sql_handle) s
WHERE spid = ${session_id}

三、如何处理tempdb暴涨

1、重启

重启数据库实例，重启数据库实例后，tempdb会按照初始化设置大小重建tempdb。但是需要我们注意的是，我们需要合理的根据业务需要设置tempdb大小，避免因为tempdb初始化值设置过小，导致实例重启后需要消耗较多的资源不断对该空间进行扩容。

2、DBCC SHRINKFILE

使用 DBCC SHRINKFILE 收缩tempdb数据库的数据文件。

1）DBCC SHRINKFILE的作用

收缩当前数据库的指定数据或日志文件大小。可以使用它将一个文件中的数据移到同一文件组中的其他文件，这会清空文件，从而允许删除数据库。可以将文件收缩到小于创建大小，同时将最小文件大小重置为新值。

2）语法

DBCC SHRINKFILE   
(  
    { file_name | file_id }   
    { [ , EMPTYFILE ]   
    | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]  
    }  
)  
[ WITH NO_INFOMSGS ]

3）在计划收缩文件时，请考虑以下几点：

在执行会产生大量未用空间的操作（如截断表或删除表操作）后，执行收缩操作最有效。
大多数数据库都需要一些可用空间，以供常规日常操作使用。如果反复收缩数据库，并且它的大小再次增长，那么常规操作可能需要收缩空间。在这种情况下，反复收缩数据库是一种无谓的操作。
收缩操作不保留数据库中索引的碎片状态，通常还会在一定程度上增加碎片。此类碎片是不要反复收缩数据库的另一个原因。
按顺序而非同时缩小同一数据库中的多个文件。对系统表的争用可能会导致阻塞，进而导致延迟。
收缩空间时需要源实例仍空余一些空间
在基于行版本控制的隔离级别下运行的事务可能会阻止收缩操作，需要等到删除操作完成后才会继续

参考链接：

https://docs.microsoft.com/zh-cn/sql/relational-databases/databases/tempdb-database?view=sql-server-2017
https://docs.microsoft.com/zh-cn/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-2017