tempdb占用大 shrink tempdb的作用

转载

IT剑客行 2024-06-15 20:46:22

文章标签 tempdb占用大 shrink 数据库 azure sql Database 文章分类 机器学习人工智能

本文适用：AZURE SQL数据库，AZURE SQL托管实例

Tempdb简介

首先来了解下Tempdb的概念，tempdb是SQLServer的系统数据库一直都是SQLServer的重要组成部分，用来存储临时对象。可以简单理解tempdb是SQLServer的速写板。应用程序与数据库都可以使用tempdb作为临时的数据存储区。一个实例的所有用户都共享一个Tempdb。很明显，这样的设计不是很好。当多个应用程序的数据库部署在同一台服务器上的时候，应用程序共享tempdb，如果开发人员不注意对Tempdb的使用就会造成这些数据库相互影响从而影响应用程序。

Tempdb的特性

1、 tempdb中的任何数据在系统重新启动之后都不会持久存在。因为实际上每次SQLServer启动的时候都会重新创建tempdb。这个特性就说明tempdb不需要恢复。

2、 tempdb始终设置为“simple”的恢复模式，当你尝试修改时都会报错。也就是说已提交事务的事务日志记录在每个检查点后都标记为重用。

3、 tempdb也只能有一个filegroup，不能增加更多文件组。

4、 tempdb被用来存储三种类型的对象：用户对象，内部对象、版本存储区

关于TEMPDB的介绍，未来会在其他文章中详细展开，这里有个大致概念即可，接下来我们继续谈关于tempdb的性能问题。

识别 IO 性能问题时，与 tempdb 问题最相关的等待类型是 PAGELATCH_*（而不是 PAGEIOLATCH_*）。但是，出现 PAGELATCH_* 等待并不总是意味着发生了 tempdb 争用。这种等待可能还意味着，由于并发请求面向相同的数据页面，发生了用户对象数据页面争用。若要进一步确认 tempdb 争用，请使用 sys.dm_exec_requests 确认 wait_resource 值是否以 2:x:y 开头，其中，tempdb 是数据库 ID，x 是文件 ID，y 是页 ID。

对于 tempdb 争用，常用的方法是减少或重写依赖于 tempdb 的应用程序代码。常见的 tempdb 使用区域包括：

临时表
表变量
表值参数
版本存储使用（与长时间运行的事务关联的用法）
包含使用排序、哈希联接和 spool 的查询计划的查询

使用表变量和临时表的最相关查询

使用以下查询来识别使用表变量和临时表的最相关查询：

SELECT plan_handle, execution_count, query_plan
INTO #tmpPlan
FROM sys.dm_exec_query_stats
     CROSS APPLY sys.dm_exec_query_plan(plan_handle);
GO

WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/2004/07/showplan' AS sp)
SELECT plan_handle, stmt.stmt_details.value('@Database', 'varchar(max)') 'Database', stmt.stmt_details.value('@Schema', 'varchar(max)') 'Schema', stmt.stmt_details.value('@Table', 'varchar(max)') 'table'
INTO #tmp2
FROM(SELECT CAST(query_plan AS XML) sqlplan, plan_handle FROM #tmpPlan) AS p
    CROSS APPLY sqlplan.nodes('//sp:Object') AS stmt(stmt_details);
GO

SELECT t.plan_handle, [Database], [Schema], [table], execution_count
FROM(SELECT DISTINCT plan_handle, [Database], [Schema], [table]
     FROM #tmp2
     WHERE [table] LIKE '%@%' OR [table] LIKE '%#%') AS t
    JOIN #tmpPlan AS t2 ON t.plan_handle=t2.plan_handle;

tempdb占用大 shrink tempdb的作用_azure