Tempdb对SQL Server性能优化有何影响

共计 2808 个字符，预计需要花费 8 分钟才能阅读完成。

Tempdb 对 SQL Server 性能优化有何影响，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。

先给大家巩固 tempdb 的基础知识

简介：

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

特性：

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

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

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

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

接下来围绕主题展示问题分析：

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

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

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

如上图所示: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 性能优化有何影响问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注丸趣 TV 行业资讯频道了解更多相关知识。