Ask Learn
Preview
Ask Learn is an AI assistant that can answer questions, clarify concepts, and define terms using trusted Microsoft documentation.
Please sign in to use Ask Learn.
Sign inThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
我曾经遇到过这样一个性能问题。一个客户反映,他的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的使用情况。
Ask Learn is an AI assistant that can answer questions, clarify concepts, and define terms using trusted Microsoft documentation.
Please sign in to use Ask Learn.
Sign in