【T-SQL性能优化】01.TempDB的使用和性能问题

以前总是追求新东西,发现基础才是最重要的,今年主要的目标是精通SQL查询和SQL性能优化。

一、TempDB是什么?

1.TempDB是一个系统数据库。从SQL Server2000开始就一直存在。

2.只有Simple恢复模式。自动截断模式。

3.存放局部变量/全局临时表/表变量/临时用法(如hash表等)。

4.机器重启或SQL Server服务重启后,都会按照Model库的配置重新创建。

5.如果临时对象是在会话或存储过程范围内产生的,在会话结束后就会自动回收,不能再查询或使用。

6.默认情况下都具有访问权限。

二、TempDB用来存放什么?

2.1.用户临时对象

(1)由用户再会话中显示创建的实体表和上面的索引。重启后清空。

(2)全局临时表+索引。##开头的表。

(3)局部临时表及上面的索引。#开头的表。

(4)表变量。@开头。

注意:

(1)全局临时表对所有会话都可见。当创建临时表的会话断开数据库的联接,而且也没有活动再引用全局临时表时,SQL Server会自动删除相应的全局临时表。

(2)局部临时表只对创建它的会话再创建级和调用堆栈内部级(内部的过程、函数、触发器、以及动态批处理)是可见的。当创建例程弹出调用堆栈,SQL Server就会自动删除相应的临时表

(3)表变量在tempdb数据库中也有对应的表作为其物理表示。只对当前会话的批处理可见。对调用堆栈中当前批处理的内部批处理是不可见的,对会话中随后的批处理也是不可见的。

(4)根据国外专家的经验,对于大数据,偏向使用临时表,小数据量(一般来说小于100行)则可以使用表变量。

是否具有统计信息

是否可以创建索引

是否是物理存储

临时表

Y

Y

Y

表变量

N

N

N

2.2.内部临时对象

在查询过程中存储临时数据的对象,如Sorts、假脱机、Hash关联和游标等。

可以使用下面的SQL语句进行查看:

SELECT * FROM sys.dm_db_session_space_usage

查看internal_object_alloc_page_count列

2.3.版本存储

开启乐观并发模式后,会使用Temp DB存放修改前的版本数据。

注意:

版本存储将会造成Temp DB的非预期增长,需要对Temp DB的文件大小及使用空间进行监控。

三、TempDB上的存在的性能问题

3.1 空间使用情况

TempDB是系统数据库,被很多地方用到,如果配置和使用不当,空间会被迅速消耗,可能出现报错,影响服务器的正常运行。

查看TempDB的空间使用情况。

3.1.1 可以用性能监视器看下SQL server的空间使用情况。

3.1.2 用SQL语句查询空间使用情况。

(1)查看tempdb的使用情况

Exec sp_spaceused

(2)查看tempdb.mdf文件的大小

SELECT * FROM dbo.sysfiles

(3)查看tempdb的使用空间

SELECT * FROM sys.dm_db_file_space_usage

(4)查看会话的空间分配情况,不包含当前活动的任务。

SELECT * FROM sys.dm_db_session_space_usage WHERE session_id > 50

(5)查看TempDB中当前运行任务的信息。

SELECT * FROM sys.dm_db_task_space_usage WHERE session_id > 50

3.1.3 诊断TempDB磁盘问题

错误

引发错误的情况

1101 或 1105

任何会话都必须分配 tempdb 中的空间。

3959

版本存储区已满。此错误在日志中通常出现在错误 1105 或 1101 之后。

3967

由于 tempdb 已满,版本存储区被强制收缩。

3958 或 3966

事务在 tempdb 中找不到所需的版本记录。

3.2 I/O问题

(1)用函数sys.dm_io_virtual_file_stats查看当前实例上的TempDB上的磁盘读写情况。

SELECT  DB_NAME(database_id) AS 'Database Name' ,
        file_id ,
        io_stall_read_ms / num_of_reads AS 'AVG Read Transfer/ms' ,
        io_stall_write_ms / num_of_writes AS 'AVG Write Transfer/ms' ,
        *
FROM    sys.dm_io_virtual_file_stats(-1, -1)
WHERE   num_of_reads > 0
        AND num_of_writes > 0

参考时间:10~20ms 可接受的范围。

(2)大量、频繁地创建和删除临时表及表变量

四、优化TempDB

1.配置文件的大小

默认配置:

初始大小8M

自动增长10%,不限制增长。

这个配置可以修改,要视生产环境的情况而修改。

建议如下配置

tempdb 文件大小

FILEGROWTH 增量

0 至 100 MB

10 MB

100 至 200 MB

20 MB

200 MB 或更多

10%*

2.存放文件的地方

一般要将TempDB的文件单独放到一个磁盘中。如果追求性能,考虑放到RAID0,但是不具有容灾性。

RAID:磁盘阵列

RAID 0 无奇偶校验的条带磁盘。数据横跨所有的物理磁盘,无任何容灾特性。

RAID 1 磁盘镜像。最少需要两个物理磁盘。可同时从两个磁盘读取数据,写数据需要备份到另外一个盘。具有容灾特性。浪费50%的磁盘空间。

RAID 5 具有奇偶校验的条带磁盘。最少需要3个物理磁盘,一个用来存放奇偶校验信息,另外两个用来存放数据,。具有容灾特性。浪费50%的磁盘空间。

RAID 10 或RIAD 0+1 组合。读写性能最好且具有容灾性。

3.文件的个数

TempDB只有一个primary文件组,所有的数据文件都会存放到这个文件组中。常规建议是4个书文件开始,并且需要进行监控,如果发现不够,可以再增加4个。依次类推。建议将文件个数控制再两位数以内。

五、其他

1.不能对TempDB执行什么操作

  • 添加文件组。
  • 备份或还原数据库。
  • 更改排序规则。默认排序规则为服务器排序规则。
  • 更改数据库所有者。tempdb 的所有者是 dbo
  • 创建数据库快照。
  • 删除数据库。
  • 从数据库中删除 guest 用户。
  • 启用变更数据捕获。
  • 参与数据库镜像。
  • 删除主文件组、主数据文件或日志文件。
  • 重命名数据库或主文件组。
  • 运行 DBCC CHECKALLOC。
  • 运行 DBCC CHECKCATALOG。
  • 将数据库设置为 OFFLINE。
  • 将数据库或主文件组设置为 READ_ONLY。

2、查看TempDB的配置项

 SELECT * FROM sys.databases WHERE name = 'tempdb'

 参考资料:

https://msdn.microsoft.com/zh-cn/library/ms176029(v=sql.105).aspx

https://msdn.microsoft.com/zh-cn/library/ms345368(v=sql.105).aspx

https://msdn.microsoft.com/zh-cn/library/ms190768(v=sql.105).aspx

《SQL Server 性能优化与管理的艺术》

作  者: Jackson0714 出  处:http://www.cnblogs.com/jackson0714/ 关于作者:专注于微软平台的项目开发。如有问题或建议,请多多赐教! 版权声明:本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文链接。 特此声明:所有评论和私信都会在第一时间回复。也欢迎园子的大大们指正错误,共同进步。或者直接私信我 声援博主:如果您觉得文章对您有帮助,可以点击文章右下角推荐一下。您的鼓励是作者坚持原创和持续写作的最大动力!

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏L宝宝聊IT

MySQL架构组成、逻辑模块组成

Mysql逻辑结构可以看成是二层架构,第一层通常叫做SQL Layer,在mysql数据库系统处理底层数据之前的所有工作都在这一层完成的,包括权...

10730
来自专栏Linyb极客之路

MySQL 之 SQL 优化实战记录

N个机台将业务数据发送至服务器,服务器程序将数据入库至MySQL数据库。服务器中的javaweb程序将数据展示到网页上供用户查看。

14510
来自专栏Laoqi's Linux运维专列

MySQL性能调优 – 你必须了解的15个重要变量

2.6K20
来自专栏芋道源码1024

数据库中间件 MyCAT源码分析 —— XA分布式事务

---- 1. 概述 2. XA 概念 3. MyCAT 代码实现 3.1 JDBC Demo 代码 3.2 MyCAT 开启 XA 事务 3.3 MyCAT...

45790
来自专栏杨建荣的学习笔记

MySQL中批量初始化数据的对比测试(r12笔记第71天)

一直以来对于MySQL的存储过程性能还是颇有微词的,说实话够慢的。有时候想做一些对比测试,存储过程初始化几万条数据都得好一会儿,这功夫Oracle类似的测试...

34170
来自专栏L宝宝聊IT

存储过程和触发器的应用

16440
来自专栏张戈的专栏

实现WordPress提交评论的时删除该页面的WP-Super-Cache缓存的方法

这篇文章依然是围绕缓存问题展开的,lnmp 环境下的 WordPress 使用 WP-Super-Cache 插件,若要开启 mod_rewrite 缓存模式,...

365120
来自专栏专注于主流技术和业务

Selenium在Windows7上的系统搭建

Selenium自动化浏览器。就是这样!你用那种方式做什么完全取决于你自己。首先,它是为了测试目的自动化web应用程序,但肯定不限于此。无聊的基于web的管理任...

14720
来自专栏idba

MySQL常见slave延迟原因以及解决方法

一 序言 在运维线上M-M 架构的MySQL数据库时,接收的比较多关于主备延时的报警:

15830
来自专栏DHUtoBUAA

Python读取SQLite文件数据

  近日在做项目时,意外听说有一种SQLite的数据库,相比自己之前使用的SQL Service甚是轻便,在对数据完整性、并发性要求不高的场景下可以尝试!   ...

69290

扫码关注云+社区

领取腾讯云代金券