欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

浅谈tempdb在SqlServer系统中的重要作用

程序员文章站 2023-11-27 23:32:46
简介:        tempdb是sqlserver的系统数据库一直都是sqlserver的重要组成部分,用...

简介:

       tempdb是sqlserver的系统数据库一直都是sqlserver的重要组成部分,用来存储临时对象。可以简单理解tempdb是sqlserver的速写板。应用程序与数据库都可以使用tempdb作为临时的数据存储区。一个实例的所有用户都共享一个tempdb。很明显,这样的设计不是很好。当多个应用程序的数据库部署在同一台服务器上的时候,应用程序共享tempdb,如果开发人员不注意对tempdb的使用就会造成这些数据库相互影响从而影响应用程序。

特性:

1、 tempdb中的任何数据在系统重新启动之后都不会持久存在。因为实际上每次sqlserver启动的时候都会重新创建tempdb。这个特性就说明tempdb不需要恢复。

2、 tempdb始终设置为“simple”的恢复模式,当你尝试修改时都会报错。也就是说已提交事务的事务日志记录在每个检查点后都标记为重用。

3、 tempdb也只能有一个filegroup,不能增加更多文件组。

4、 tempdb被用来存储三种类型的对象:用户对象,内部对象、版本存储区

以上是基础知识。

如果sql server对tempdb访问不频繁,

tempdb对数据库不会产生影响;相反如果访问很频繁,loading就会加重,

tempdb的性能就会对整个db产生重要的影响

.优化tempdb的性能变的很重要的,尤其对于大型数据库。

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

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,就该返回来考虑这些问题了。

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活动频繁且tempdb不在增长时会是一个合适的值,可以设置此时的值为initial size;当然还会有更多的考量,此为一例。

3、不要收缩tempdb(如没有必要)

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

4 、分派tempdb的文件和其他数据文件到不用的io上

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

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

常用的脚本如下:

--查tempdb库的大小

select a.name, sum(size)*8.0/1024/1024 as [gb]

, sum(size)*8.0/1024 as [mb]

from master.dbo.sysdatabases a inner join master.dbo.sysaltfiles b on a.dbid=b.dbid

where a.name='tempdb'

group by a.name

order by 1

后记:

tempdb是sql server重要的一部分,今天发现数据的tempdb特别大,所以对tempdb的一些了解和总结,以后还需要进一步学习。