Tempdb怎么会成为性能瓶颈

一个客户反映，他的SQL Server会在某一段时间里，突然变得非常慢，最后他不得不重启SQL Server服务，而重启以后，问题就消失了。查看 sys.dm_exec_requests 发现问题比较明显，有很多任务在争抢页面 2:18:331608 上的 PAGELATCH_UP 和 PAGELATCH_SH 资源（表示会话正在等待获取对内存中页面的Latch访问权），可见 Tempdb（database_id为2）的瓶颈是当时最大的问题。

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

Tempdb是SQL Server里的一个重要的系统数据库，许多用户的操作，都有可能使用到它，最常见的当然是用户使用临时表或者表变量。当数据库创建一张新表的时候，SQL Server要为这张表分配存储页面，同时SQL Server也要修改GAM、SGAM、PFS等系统页面，把已经分配出去的页面标志成已使用。所以每创建一张新表，GAM、SGAM、PFS这些系统页面都会有修改动作。

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

但是 2:18:331608 这个值让人有点疑惑。第一，文件ID 18意味着这个tempdb上至少有18个文件。除去一个日志文件，这个tempdb至少有17个数据文件。而这台服务器只有16颗CPU，为什么大家别的数据文件都不用，非要抢这个第18号文件呢？这是很奇怪的地方。第二，331608意味着这个18号文件当时已经比较大了。带着这些疑惑，又收集了tempdb上的sp_helpfile结果，这个结果回答了疑惑。

像前面猜测的一样，这个tempdb上果然有17个数据文件。但是这些文件的配置是不一样的。前16个文件的初始大小是256MB，最大大小是512MB。而最后一个数据文件，也就是出问题的18号，初始大小是2GB，没有上限。用户这样设置，显然是为了防止tempdb在P盘上使用太多的空间。如果tempdb能够同时使用这17个数据文件，那么它会同时在不同的数据文件里为不同的用户分配空间。也就意味着，同时可以有多个人创建临时对象。这样Tempdb就不再会是系统的性能瓶颈。并发度会大大提高。

假设前16个数据文件都达到最大值，而且最后一个文件没有增长，那么tempdb数据文件的大小应该是（0.5x16+2）=10GB。那出问题的时候，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，差不多够用。如果P盘空间不够，可以将部分文件挪到G盘上。

• 2．严密监视Tempdb空间使用情况，确保这些文件不会被SQL Server写满，尽量保证每个文件剩余的空间一样大。

  注：如果某个数据文件写满了，SQL Server就不会再往这个文件上写了。若所有数据文件全都写满了，SQL Server会选取其中一个文件（不是每一个）做自动增长，新数据全都写入这个文件，此时这个自动增长的文件会成为系统瓶颈。