谈谈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重要的一部分,以上只是对tempdb的一些了解总结,还需要进一步学习...

时间: 2015-11-10

SQL Server 2016 查询存储性能优化小结

作为一个DBA,排除SQL Server问题是我们的职责之一,每个月都有很多人给我们带来各种不能解释却要解决的性能问题. 我就多次听到,以前的SQL Server的性能问题都还好且在正常范围内,但现在一切已经改变,SQL Server开始糟糕, 疯狂的事情不能解释.在这个情况下我介入,分析下整个SQL Server的安装,最后用一些神奇的调查方法找出性能问题的根源. 但很多时候问题的根源是一样的:所谓的计划回归(Plan Regression),即特定查询的执行计划已经改变.昨天SQL Serv

SQLServer性能优化--间接实现函数索引或者Hash索引

SQLServer中没有函数索引,在某些场景下查询的时候要根据字段的某一部分做查询或者经过某种计算之后做查询,如果使用函数或者其他方式作用在字段上之后,就会限制到索引的使用,不过我们可以间接地实现类似于函数索引的功能. 另外一个就是如果查询字段较大或者字段较多的时候,所建立的索引就显得有点笨重,效率也不高,就需要考虑使用一个较小的"替代性"字段做等价替换,类似于Hash索引, 本文粗浅地介绍两种上述两种问题的解决方式,仅供参考. 1,在计算列上建索引,实现"函数索引"

SQL SERVER 的SQL语句优化方式小结

1.SQL SERVER 2005的性能工具中有SQL Server Profiler和数据库引擎优化顾问,极好的东东,必须熟练使用. 2.查询SQL语句时打开"显示估计的执行计划",分析每个步骤的情况 3.初级做法,在CPU占用率高的时候,打开SQL Server Profiler运行,将跑下来的数据存到文件中,然后打开数据库引擎优化顾问调用那个文件进行分析,由SQL SERVER提供索引优化建议.采纳它的INDEX索引优化部分. 4.但上面的做法经常不会跑出你所需要的,在最近的优化

SQLServer 优化SQL语句 in 和not in的替代方案

但是用IN的SQL性能总是比较低的,从SQL执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别: SQL试图将其转换成多个表的连接,如果转换不成功则先执行IN里面的子查询,再查询外层的表记录,如果转换成功则直接采用多个表的连接方式查询.由此可见用IN的SQL至少多了一个转换的过程.一般的SQL都可以转换成功,但对于含有分组统计等方面的SQL就不能转换了. 推荐在业务密集的SQL当中尽量不采用IN操作符 NOT IN 此操作是强列推荐不使用的,因为它不能应用表的索引.推荐用NOT EXIS

人工智能自动sql优化工具--SQLTuning for SQL Server

针对这种情况,人工智能自动SQL优化工具应运而生.现在我就向大家介绍这样一款工具:SQLTuning for SQL Server. 1. SQL Tuning 简介 SQL Turning是Quest公司出品的Quest Central软件中的一个工具. QuestCentral(图1)是一款集成化.图形化.跨平台的数据库管理解决方案,可以同时管理Oracle.DB2 和 SQL server 数据库.它包含了如下的多个工具: 数据库管理(DBA)  数据库监控(Monitoring Pack

SQL Server中的SQL语句优化与效率问题

很多人不知道SQL语句在SQL SERVER中是如何执行的,他们担心自己所写的SQL语句会被SQL SERVER误解.比如: select * from table1 where name='zhangsan' and tID > 10000 和执行: select * from table1 where tID > 10000 and name='zhangsan' 一些人不知道以上两条语句的执行效率是否一样,因为如果简单的从语句先后上看,这两个语句的确是不一样,如果tID是一个聚合索引,那

sql语句优化之SQL Server(详细整理)

MS SQL Server查询优化方法 查询速度慢的原因很多,常见如下几种 1.没有索引或者没有用到索引(这是查询慢最常见的问题,是程序设计的缺陷) 2.I/O吞吐量小,形成了瓶颈效应. 3.没有创建计算列导致查询不优化. 4.内存不足 5.网络速度慢 6.查询出的数据量过大(可以采用多次查询,其他的方法降低数据量) 7.锁或者死锁(这也是查询慢最常见的问题,是程序设计的缺陷) 8.sp_lock,sp_who,活动的用户查看,原因是读写竞争资源. 9.返回了不必要的行和列 10.查询语句不好,

win2008 r2 服务器php+mysql+sqlserver2008运行环境配置(从安装、优化、安全等)

win2008 r2 安装 http://www.jb51.net/article/38048.htm iis的安装 http://www.jb51.net/article/86390.htm php的安装注意事项: 下载非安全线程 版本 nts php 5.2.17 http://www.jb51.net/softs/268745.html 其它版本可以到 http://museum.php.net/php5/里面很多经典的老版本. PHP 5.5 (5.5.36) VC11 x64 Non

SQL Server游标的使用/关闭/释放/优化小结

游标是邪恶的! 在关系数据库中,我们对于查询的思考是面向集合的.而游标打破了这一规则,游标使得我们思考方式变为逐行进行.对于类C的开发人员来着,这样的思考方式会更加舒服. 正常面向集合的思维方式是: 而对于游标来说: 这也是为什么游标是邪恶的,它会使开发人员变懒,懒得去想用面向集合的查询方式实现某些功能. 同样的,在性能上,游标会吃更多的内存,减少可用的并发,占用宽带,锁定资源,当然还有更多的代码量-- 从游标对数据库的读取方式来说,不难看出游标为什么占用更多的资源,打个比方: 当你从ATM取钱

SQL SERVER性能优化综述(很好的总结,不要错过哦)第1/3页

一.分析阶段 一般来说,在系统分析阶段往往有太多需要关注的地方,系统各种功能性.可用性.可靠性.安全性需求往往吸引了我们大部分的注意力,但是,我们必须注意,性能是很重要的非功能性需求,必须根据系统的特点确定其实时性需求.响应时间的需求.硬件的配置等.最好能有各种需求的量化的指标. 另一方面,在分析阶段应该根据各种需求区分出系统的类型,大的方面,区分是OLTP(联机事务处理系统)和OLAP(联机分析处理系统). 二.设计阶段 设计阶段可以说是以后系统性能的关键阶段,在这个阶段,有一个关系到以后几乎

sql server 性能优化之nolock

伴随着时间的增长,公司的数据库会越来越多,查询速度也会越来越慢.打开数据库看到几十万条的数据,查询起来难免不废时间. 要提升SQL的查询效能,一般来说大家会以建立索引(index)为第一考虑.其实除了index的建立之外,当我们在下SQL Command时,在语法中加一段WITH (NOLOCK)可以改善在线大量查询的环境中数据集被LOCK的现象藉此改善查询的效能. 不过有一点千万要注意的就是,WITH (NOLOCK)的SQL SELECT有可能会造成Dirty Read,就是读到无效的数据.

SQL Server并行操作优化避免并行操作被抑制而影响SQL的执行效率

为什么我也要说SQL Server的并行: 这几天园子里写关于SQL Server并行的文章很多,不管怎么样,都让人对并行操作有了更深刻的认识. 我想说的是:尽管并行操作可能(并不是一定)存在这样或者那样的问题,但是我们不能否认并行,仍然要利用好并行. 但是,实际开发中,某些SQL语句的写法会导致用不到并行,从而影响到SQL的执行效率 所以,本文要表达的是:我们要利用好并行,不要让一些SQL的写法问题"抑制"了并行,让我们享受不了并行带来的快感 关于SQL Server的并行: 所谓的

SQL Server 性能调优之查询从20秒至2秒的处理方法

一.需求 需求很简单,就是需要查询一个报表,只有1个表,数据量大约60万左右,但是中间有些逻辑. 先说明一下服务器配置情况:1核CPU.2GB内存.机械硬盘.Sqlserver 2008 R2.Windows Server2008 R2 SP1和阿里云的服务器,简单说就是阿里云最差的服务器. 1.原始表结构 非常简单的一张表,这次不讨论数据冗余和表字段的设计,如是否可以把Project和Baojian提出成一个表等等,这个是原始表结构,这个目前是没有办法改变的. 2.查询的sql语句为 sele

SQL Server 数据库优化

在开发工具.数据库设计.应用程序的结构.查询设计.接口选择等方面有多种选择,这取决于特定的应用需求以及开发队伍的技能.本文以SQL Server为例,从后台数据库的角度讨论应用程序性能优化技巧,并且给出了一些有益的建议.1 数据库设计 要在良好的SQL Server方案中实现最优的性能,最关键的是要有1个很好的数据库设计方案.在实际工作中,许多SQL Server方案往往是由于数据库设计得不好导致性能很差.所以,要实现良好的数据库设计就必须考虑这些问题. 1.1 逻辑库规范化问题 一般来说,逻辑

SQL语句性能优化(续)

上篇介绍了一下自己在项目中遇到的一种使用sql语句的优化方式(性能优化--SQL语句),但是说的不够完整.在对比的过程中,没有将max函数考虑在内,经人提醒之后赶紧做了一个测试,测试过程中又学到了不少的东西. 上次用的是select count(*) 和select * 的执行效率问题,因为我的需求是获取数据的一个总数来自动给出新的id,然后网友给出可以使用max的方式给出新id.其实这也是一种不错的思路(当时我们也用过该函数,只不过因为系统数据本身问题,不适合用该函数),然后我就对max函数的

SQL Server性能调优之缓存

在执行任何查询时,SQL Server都会将数据读取到内存,数据使用之后,不会立即释放,而是会缓存在内存Buffer中,当再次执行相同的查询时,如果所需数据全部缓存在内存中,那么SQL Server不会产生Disk IO操作,立即返回查询结果,这是SQL Server的性能优化机制. 一,主要的内存消费者(Memory Consumer) 1,数据缓存(Data Cache) Data Cache是存储数据页(Data Page)的缓冲区,当SQL Server需要读取数据文件(File)中的数

MySQL批量SQL插入性能优化详解

对于一些数据量较大的系统,数据库面临的问题除了查询效率低下,还有就是数据入库时间长.特别像报表系统,每天花费在数据导入上的时间可能会长达几个小时或十几个小时之久.因此,优化数据库插入性能是很有意义的. 经过对MySQL innodb的一些性能测试,发现一些可以提高insert效率的方法,供大家参考参考. 1. 一条SQL语句插入多条数据. 常用的插入语句如: INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) VAL

SQL SERVER的优化建议与方法

在实际的工作中,尤其是在生产环境里边,SQL语句的优化问题十分的重要,它对数据库的性能的提升也起着显著的作用.我们总是在抱怨机器的性能问题,总是在抱怨并发访问所带来的琐问题,但是如果我们对没一条SQL语句进行优化,尽管不能说可以解决全部问题,但是至少可以解决大部分问题. 1.Top排序问题. 我们经常要对表某个字段进行排序,然后取前N名.所以我们会写如下的SQL语句: select top 100 * from 表 order by Score desc 如果表非常大的话,那么这样的操作是非常消