谈谈Tempdb对SQL Server性能优化有何影响
作者:changbluesky 发布时间:2024-01-28 04:55:31
先给大家巩固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重要的一部分,以上只是对tempdb的一些了解总结,还需要进一步学习...
猜你喜欢
- argparse 是 python 的一个命令行解析包,可根据需要编写高可读性的程序。网上的许多教程较为冗长和散漫,没有达到精练好掌握的目的
- 1、RuntimeError: invalid argument 0: Sizes of tensors must match except
- 一、问答平台这个「生活常识解答」机器人采用的是:阿里达摩院发布的语言模型PLUG(最近刚发布的,目前是测试阶段),地址链接如下:https:
- 一、了解字符编码的知识储备1. 文本编辑器存取文件的原理(nodepad++,pycharm,word) 打开编辑器就打开了启动了
- 锁有两种分类方法。(1) 从数据库系统的角度来看锁分为以下三种类型: •独占锁(Exclusive Lock)独占锁锁定的资源只允许进行锁定
- 1.过程蜘蛛纸牌大家玩过没有?之前的电脑上自带的游戏,用他来摸鱼过的举个手。但是现在的电脑上已经没有蜘蛛纸牌了。所以…
- 本文实例讲述了Python多线程原理与用法。分享给大家供大家参考,具体如下:先来看个栗子:下面来看一下I/O秘籍型的线程,举个栗子——爬虫,
- 写过一篇"正则表达式30分钟入门教程",有读者问:[^abc]表示不包含a、b、c中任意字符, 我想实现不包含字符串ab
- 本文实例讲述了Golang最大递减数算法问题。分享给大家供大家参考,具体如下:给出一个非负整数,找到这个非负整数中包含的最大递减数。一个数字
- 方法一:mylist = [1,2,2,2,2,3,3,3,4,4,4,4]myset = set(mylist)for item in m
- OpenCV提供了很多简单的语句,实现复杂的功能,根据颜色和鼠标交互的基础语句,我们可以建立一个简单的画板。尽管它简单,但是制作的框架步骤不
- 如何取得刚添加的记录自动增加的ID字段的号码?我们用下面这个程序就行:rs(1)=oldrname &nb
- 学了一天pygame,用python和pygame写一个简单的挡板弹球游戏GitHub:EasyBaffleBallGame# -*- co
- 首先,看看本文所面向的应用场景:我们有一个数据集df,现在想统计数据中某一列每个元素的出现次数。这个在我们前面文章《如何画直方图》中已经介绍
- 我们之前看到了函数和对象。从本质上来说,它们都是为了更好的组织已经有的程序,以方便重复利用。模块(module)也是为了同样的目的。在Pyt
- 字符串Go语言中的字符串以原生数据类型出现。 Go 语言里的字符串的内部实现使用UTF-8编码。 字符串的值为双引号(")中的内容
- 隐藏你的.php文件 隐藏你的.php文件 今天做PHP在线手册镜像的时候看到了这个方法,哈哈,以前都没有注意到,所以说,手册是
- MongoDB 是一个基于分布式文件存储的数据库。由 C++ 语言编写。旨在为 WEB 应用提供可扩展的高性能数据存储解决方案。MongoD
- 本文的asp xmlhttp类,使用asp的MSXML2.ServerXMLHTTP组件来获取远程音乐文件。类定义 Cls_AspHttp.
- 1:下载FCKEditor编辑器下载下来后解压到你网站的目录,最好就放在根目录下,文件夹名字就用FCKEditor;这里可以随便自己喜好,但