了解tempdb对SQL Server数据库性能的影响

数据库 SQL Server 数据库运维
SQL Server中有四个重要系统级数据库:master, model, msdb, tempdb,本文将探讨的是tempdb对SQL Server数据库性能的影响。

本文是提升SQL Server数据库性能方面的讨论,51CTO数据库频道向您推荐《SQL Server入门到精通》以便于您更深刻的了解SQL Server数据库。

1.SQL Server系统数据库介绍

SQL Server有四个重要的系统级数据库:master,model,msdb,tempdb.

master:记录SQL Server系统的所有系统级信息,包括实例范围的元数据,端点,链接服务器和系统配置设置,还记录其他数据库是否存在以及这些数据问文件的位置等等.如果master不可用,数据库将不能启动.

model:用在SQL Server 实例上创建的所有数据库的模板。因为每次启动 SQL Server 时都会创建 tempdb,所以 model 数据库必须始终存在于 SQL Server 系统中。

msdb:由SQL Server 代理用来计划警报和作业。

tempdb:是连接到 SQL Server 实例的所有用户都可用的全局资源,它保存所有临时表,临时工作表,临时存储过程,临时存储大的类型,中间结果集,表变量和游标等。另外,它还用来满足所有其他临时存储要求.

2.tempdb内在运行原理

与其他SQL Server数据库不同的是,tempdb在SQL Server停掉,重启时会自动的drop,re-create. 根据model数据库会默认建立一个新的8MB(mdf file:8MB;ldf file:1MB, autogtouth设置为10%)大小recovery model为simple的tempdb数据库.

tempdb数据库建立之后,DBA可以在其他的数据库中建立数据对象,临时表,临时存储过程,表变量等会加到tempdb中.在tempdb活动很频繁时,能够自动的增长,因为是simple的recovery model,会最小化日志记录,日志也会不断的截断.

3.如何合理的优化tempdb以提高SQL Server的性能

如果SQL Server对tempdb访问不频繁,tempdb对数据库不会产生影响;相反如果访问很频繁,loading就会加重,tempdb的性能就会对整个DB产生重要的影响.优化tempdb的性能变的很重要的,尤其对于大型数据库.

注:在优化tempdb之前,请先考虑tempdb对SQL Server性能产生多大的影响,评估遇到的问题以及可行性.

3.1最小化的使用tempdb

SQL Server中很多的活动都活发生在tempdb中,所以在某种情况可以减少多对tempdb的过度使用,以提高SQL Server的整体性能.

如下有几处用到tempdb的地方:

(1)用户建立的临时表.如果能够避免不用,就尽量避免. 如果使用临时表储存大量的数据且频繁访问,考虑添加index以增加查询效率.

(2)Schedule jobs.如DBCC CHECKDB会占用系统较多的资源,较多的使用tempdb.最好在SQL Server loading比较轻的时候做.

(3)Cursors.游标会严重影响性能应当尽量避免使用.

(4)CTE(Common Table Expression).也会在tempdb中执行.

(5)SORT_INT_TEMPDB.建立index时会有此选项.

(6)Index online rebuild.

(7)临时工作表及中间结果集.如JOIN时产生的.

(8)排序的结果.

(9)AFTER and INSTEAD OF triggers.

不可能避免使用tempdb,如果有tempdb的瓶颈或issue,就该返回来考虑这些问题了.

3.2重新分配tempdb的空间大小

在SQL Server重启时会自动建立8MB大小的tempdb,自动增长默认为10%. 对于小型的数据库来说,8MB大小已经足够了.但是对于较大型的数据库来说,8MB远远不能满足SQL Server频繁活动的需要,因此会按照10%的比例增加,比如说需要1GB,则会需要较长的时间,此段时间会严重影响SQL Server的性能. 建议在SQL Server启动时设置tempdb的初始化的大小(如下图片设置为MDF:300MB,LDF:50MB),也可以通过ALTER DATABASE来实现. 这样在SQL Server在重启时tempdb就会有足够多的空间可利用,从而提高效率.

SQL Server重启

 难点在于找到合理的初始化大小,在SQL Server活动频繁且tempdb不在增长时会是一个合适的值,可以设置此时的值为Initial Size;当然还会有更多的考量,此为一例.

3.3不要收缩tempdb(如没有必要)

有时候我们会注意到tempdb占用很大的空间,但是可用的空间会比较低时,会想到shrink数据库来释放磁盘空间, 此时要小心了,可能会影响到性能.

不要收缩tempdb

 如上图所示:tempdb分配的空间为879.44MB,有45%的空间是空闲的,如果shrink掉,可以释放掉一部分磁盘空闲,但是之后SQL Server如有大量的操作时,tempdb空间不够用,又会按照10%的比例自动增长. 这样子的话,所做的shrink操作是无效的,还会增加系统的loading.

3.4 分派tempdb的文件和其他数据文件到不用的IO上

tempdb对IO的要求比较高,最好分配到高IO的磁盘上且与其他的数据文件分到不用的磁盘上,以提高读写效率.

tempdb也分成多个文件,一般会根据CPU来分,几个CPU就分几个tempdb的数据文件. 多个tempdb文件可以提高读写效率并且减少IO活动的冲突.

tempdb是SQL Server重要的一部分,以上只是对tempdb的一些了解总结,还需要进一步学习...

原文标题:tempdb对SQL Server性能的影响 

链接:http://www.cnblogs.com/changbluesky/archive/2010/04/15/1711733.html

【编辑推荐】

  1. SQL Server使用索引实现数据访问优化
  2. SQL Server数据库优化经验总结
  3. 如何使用SQLServer数据库查询累计值
  4. 浅析Oracle和SqlServer存储过程的调试、出错处理
  5. 几段SQLServer语句和存储过程
  6. 50种方法优化SQL Server数据库查询
责任编辑:彭凡 来源: 博客园
相关推荐

2010-06-17 13:34:47

SQL Server数

2011-08-09 12:27:16

SQL Server tempdb

2009-06-30 15:02:41

磁盘排序Oracle数据库性能

2010-07-15 17:28:50

SQL Server

2011-04-01 13:56:12

SQL Server数

2010-08-17 09:22:34

2010-07-08 11:05:14

SQL Server数

2021-02-05 16:30:18

ZabbixSQL工具

2010-06-30 14:24:08

SQL Server数

2010-07-02 14:46:20

SQL Server数

2010-11-08 10:16:41

SQL Server数

2010-07-14 09:17:17

SQL Server数

2010-07-22 15:13:08

SQL Server

2010-11-26 14:52:10

MySQL系统变量

2021-07-01 10:45:08

硬盘数据库性能

2011-03-24 09:07:11

SQL Server数备份

2011-03-24 09:24:08

SQL Server数还原

2011-03-24 09:45:34

SQL Server数恢复

2010-07-13 09:12:56

SQL Server

2011-03-30 10:52:07

SQL Server数服务器
点赞
收藏

51CTO技术栈公众号