转自:http://blogs.msdn.com/b/apgcdsd/archive/2011/01/25/tempdb.aspx
我曾经遇到过这样一个性能问题。一个客户反映,他的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的使用情况。
分享到:
相关推荐
1.tempdb扩容脚本.sql 2.查看没有实用的索引.sql 3.查看数据库IO性能.sql 4.查询数据库消耗IO排行.sql(非常实用) 5.查询查询阻塞.sql
tempdb太大引起磁盘容量不足的解决方案
恢复tempdb数据库的方法要分两个部分。首先将tempdb重设到默认大小;其次确认和改变tempdb数据库到新的设备上
如何恢复tempdb数据库.pdf
数据库tempdb的日志已满数据库tempdb的日志已满
关于SqlServer2000数据库中tempdb.mdf的迁移
当多个应用程序的数据库部署在同一台服务器上的时候,应用程序共享tempdb,如果开发人员不注意对Tempdb的使用就会造成这些数据库相互影响从而影响应用程序。 特性: 1、 tempdb中的任何数据在系统重新启动之后都不会...
数据库 'tempdb' 的日志已满。请备份该数据库的事务日志以释放一些日志空间。 网上找了下解决方案,大体是扩大临时库的日志文件的大小解决的 解决过程: 查看了下数据库的属性,是自动增长,不指定文件大小上限。 ...
每个实例仅仅允许有一个TempDb,但需要用到TempDB的地方却有很多,所以TempDB很容易成为性能瓶颈,我想大家数人都了解这一点,而大多数人所不了解的应该是在什么情况下才需要额外的TempDB文件。 当你看到PA
tempdb系统资料库是用来放置资料处理时的中继资料,它的使用关系到整体系统的运作,本资料就是针对TEMPDB系统资料库的用途和观察做介绍。
TempDB 2.0 下载,它是中山大学研究的,国内顶尖的实时数据库开发系统
我们有时候为了操作方便,常常会更改一下tempdb数据库的位置,那么该如何操作呢?本文我们就来介绍这一更改的过程。 获得tempdb的原始位置: select [name],[physical_name] from sys.master_files where ...
在SQL Server 2016安装期间,第一个你会碰到的改变是在安装过程中,现在你能配置TempDb的物理配置。我们可以详细看下面的截屏。 微软现在检测几个可用的CPU内核,基于这个数字安装程序自动配置TempDb文件个数。这个
当数据页经常从缓冲池中移进移出的时候,I/O子系统会成为SQLServer性能问题的关键因素之一。事务日志和tempdb同样也会产生重大的I/O压力。因此,你必须确保你的I/O子系统能按照预期运行。否则你将会成为响应时间...
如果Tempdb过小当查询数据量较大的时候Tempdb会自动扩展,如果遇到频繁的查询会导致Tempdb不断扩展,从而影响系统性能。这种情况我尽可能地使查询的返回结果比较小 3、大量插入数据,导致数据文件增长过快。不要设置...
tempdb是SQLServer的系统数据库一直都是SQLServer的重要组成部分,用来存储临时对象。tempdb中的任何数据在系统重新启动之后都不会持久存在。因为实际上每次SQLServer启动的时候都会重新创建tempdb。这个特性就说明...
近帮助客户调优的过程中,发现客户的TempDB存在非常大的压力,经过排查是发现某些语句对TempDB的巨量使用所导致。 在SQL Server中,TempDB主要负责供下述三类情况使用: · 内部使用(排序、hash join、work ...
在SQL Server中,TempDB主要负责供下述三类情况使用: 内部使用(排序、hash join、work table等) 外部使用(临时表,表变量等) 行版本控制(乐观并发控制) 而对于内部使用,一些比较复杂的查询中由于涉及到...