步骤1.TempDB压力诊断

等待类型诊断

TempDB的争用压力在等待篇中已经简单介绍，等待的表现为 pagelatch_类等待，等待的资源是 “2： X ：X ”

技术分享

tempDB所在磁盘的响应时间

技术分享

一个实例下只有一个tempdb，也就是当你在一个实例下创建了100个数据库，这100个数据库也只能用这一个TempDB。

你创建的临时表，或SQL执行语句所需要的排序等操作都需要用到Tempdb。所以TempDB对磁盘的响应时间要求比较高。

步骤2.解决问题

把TempDB设置成多个来分摊这个压力。

分成多个文件

　　　　作为一般规则，如果逻辑处理器数小于或等于 8，使用和逻辑处理器相同数量的数据文件。如果逻辑处理器数大于 8 时，使用 8 个数据文件，然后如果仍然存在争用，增加数据文件数4 的倍数（最多的逻辑处理器数）直到争用降低到可接受的程度或对工作负荷/代码进行更改。

文件大小、增长率要相同

　　　这里需要注意一个小细节，你所分配的文件必须大小一致，如果设置自动增长那么增长率要相同。

　　　　技术分享

TempDB磁盘划分

　　大多数情况下，TempDB的文件不需要拆分磁盘，在同一个磁盘即可，如果压力大可以选择放置在一个单独的磁盘中，这样不会与其他文件（如数据读写）发生磁盘资源竞争。

　　　　技术分享

　　　　如果出现TempDB 读取响应时间高的情况，请考虑，TempDB的磁盘相关优化，如将TempDB文件单独放入比较快的磁盘。

步骤3.语句调优

　　语句调优篇提到语句中使用临时表或表变等会减少语句的复杂度，提升语句的效率，是常用的三板斧之一，但这里的需要一个平衡。如果对语句过度使用会造成文中提到的TempDB压力。那么怎么样平衡呢？下面给出几点建议：

切记不要过度使用临时表！临时表的使用主要有两个场景，拆分语句降低复杂性。另一个是缓存中间结果避免重复操作。
减少使用临时表锁系统表的时间！”select 字段 into #临时表 from“ 如果语句执行时间过长这将是灾难，尽量选用先创建，后插入的做法。

原理：TempDB压力从哪来？

　　　　当数据库创建一张新表的时候，SQL Server要为这张表分配存储页面，同时SQL Server也要修改SGAM, PFS, 和GAM页面，把已经分配出去的页面标志成已使用。所以每创建一张新表，SGAM, PFS, 和GAM这些系统页面都会有修改动作。这种行为对一般的用户数据库不会有问题，因为正常的应用不会折腾着不停地建表、删表。但是tempdb就不同了。如果一个存储过程使用了临时表，而这个存储过程被并发用户广泛使用，那很自然地就会有很多并发用户在tempdb里同时创建表，做完了以后又删除表。这样，在一个时间点，会有很多任务要修改SGAM, PFS, 或GAM页面。但是为了维护物理的一致性，对于同一个页面，SQL Server在一个时间点同时只允许一个用户修改它。所以对于tempdb，如果同时有很多很多人要在同一个数据文件里分配空间，那这个数据文件的SGAM, PFS, 或GAM页面，就有可能成为系统瓶颈。大家只能一个一个做，并发度上不去。

　　　　这就好像你进停车场要登记交费一样！一个一个来不要急~

　　　　技术分享

　　　　等待资源为： “2：1：3” 这是什么意思？ ID 为 2 的数据库（TempDB）的 1号文件的页码为3的页（SGAM页面）！

　　　　技术分享

　　　　这里关于系统页不过多的介绍，想详细了解的朋友请参见： SQL Server中的GAM页和SGAM页

我创建个临时表跟系统页还有关系？

　　　　下面也用一个例子说明：

　　　　创建临时表的时候会对系统表中进行插入和更新，而删除临时表逆向过程会删除或更新系统表！

use [AdventureWorks2012]
GO
checkpoint
go
create table #t
(
id int
)
drop table #t


use tempdb
go
select Operation,CONTEXT,[Transaction ID],AllocUnitId,AllocUnitName,[Page ID],[Transaction Name],Description from fn_dblog(null,null)

　　　　技术分享