Tempdb怎么会成为性能瓶颈？

转自：https://blogs.msdn.microsoft.com/apgcdsd/2011/01/24/tempdb/

我曾经遇到过这样一个性能问题。一个客户反映，他的SQL Server会在某一段时间里，突然变得非常慢。最后他不得不重启SQL Server服务。而重启以后，问题就消失了。客户在出现问题的那段时间里，收集了主要的系统动态管理视图，以及性能监视器里和SQL Server有关的那些计数器。顺便说一句，这台服务器有16颗CPU。

 

Sys.dm_exec_requests是检查SQL Server性能瓶颈的有力工具。在处理SQL Server性能问题的时候，它是作者第二个检查的对象。（第一个当然是SQL Server的日志文件，要确认Server当时没有异常。）

 

从Sys.dm_exec_requests的结果看，问题比较明显，有很多任务在争抢页面2:18:331608上的PAGELATCH_x资源。Tempdb上的瓶颈是当时最大的问题。

 

 

 

Tempdb上的一个页面，能造成客户整个SQL Server响应缓慢。这是为什么？为什么重起又能解决问题呢？

 

Tempdb是SQL Server里的一个重要的系统数据库。许多用户的操作，都有可能使用到它。最常见的当然是用户使用临时表或者表变量。其他可能性有，用户使用trigger，Snapshot Isolation Level，某些复杂的查询，DBCC CHECKDB，以及DBCC Reindex等。

 

当数据库创建一张新表的时候，SQL Server要为这张表分配存储页面，同时SQL Server也要修改SGAM, PFS, 和GAM页面，把已经分配出去的页面标志成已使用。所以每创建一张新表，SGAM, PFS, 和GAM这些系统页面都会有修改动作。

 

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

 

但是2:18:331608这个值让人有点疑惑。第一，文件ID 18意味着这个tempdb上至少有18个文件。除去一个日志文件，这个tempdb至少有17个数据文件。而这台服务器只有16颗CPU，为什么大家别的数据文件都不用，非要抢这个第18号文件呢？这是很奇怪的地方。第二，SGAM, PFS, 和GAM页面都在文件的开头。只有当数据文件变得比较大以后，文件头的那几个页面已经不够用了，SQL Server才会在后面再分配新的系统页面。所以331608意味着这个18号文件当时已经比较大了。

 

带着这些疑惑，作者又让客户收集了一个tempdb上的sp_helpfile结果。这个结果回答了疑惑。

  

   

像前面猜测的一样，这个tempdb上果然有17个数据文件。但是这些文件的配置是不一样的。前16个文件的初始大小是256MB，最大大小是512MB。而最后一个数据文件，也就是出问题的18号，初始大小是2GB，没有上限。用户这样设置，显然是为了防止tempdb在P盘上使用太多的空间。

 

如果tempdb能够同时使用这17个数据文件，那么它会同时在不同的数据文件里为不同的用户分配空间。也就意味着，同时可以有多个人创建临时对象。这样Tempdb就不再会是系统的性能瓶颈。并发度会大大提高。

 

那这位用户那里发生了什么呢？通过性能监视器的计数器SQLServer:Databases – Data File(s) Size (KB)，发现当时Tempdb的总大小在21GB。也就是说，前面的16个小的数据文件已经用满。SQL Server只好集中使用第18号数据文件，因为它没有上限，就让它不断自动增长。所有的压力都集中在了一个文件上，难怪这个文件成为了瓶颈。

 

SQL Server重起以后，Tempdb被清空。用户重新可以同时使用这17个文件。所以，重起解决了问题。

 

为了达到长治久安，在高并发、又大量使用Tempdb的SQL Server里，DBA需要这样配置Tempdb。

 

1．  创建和CPU数目同样多的Tempdb数据文件，每个文件的大小要一样大。
这里客户应该创建16个数据库文件，每个2GB，差不多够用。

2． 严密监视Tempdb空间使用情况，确保这些文件不会被SQL Server写满。

3． 如果使用中发现初始空间不够大，需要手工增长每一个数据文件，确保它们始终一样大。

如果初始空间不够大，SQL Server会自动增长某个文件，获得新的空间。而这个自动增长的文件会成为系统瓶颈。所以不能依赖SQL Server帮你自动增长。

 

当然，监视tempdb的使用情况，搞清楚是谁在tempdb里占用了这么多空间也是很重要的。我们会另有文章，介绍怎么监视tempdb的使用情况。