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

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

本系列【T-SQL基础】主要是针对T-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 条评论
登录 后参与评论

相关文章

来自专栏Jackson0714

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

1992
来自专栏芋道源码1024

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

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

4469
来自专栏DHUtoBUAA

Python读取SQLite文件数据

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

6039
来自专栏大内老A

一个完整的用于追踪数据改变的解决方案

在之前一篇介绍CDC的文章中,我说Audit Trail(或者Audit Log)是大部分企业级应用不可以或缺的功能。本篇给你一个完整的Audit Trail解...

2256
来自专栏L宝宝聊IT

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

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

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

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

2.5K2
来自专栏Java帮帮-微信公众号-技术文章全总结

Sharding-JDBC—分库分表实例【面试+工作】

Sharding-JDBC是一个开源的适用于微服务的分布式数据访问基础类库,它始终以云原生的基础开发套件为目标。

4832
来自专栏Java技术栈

关系型数据的分布式处理系统:Cobar

Cobar简介 Cobar是关系型数据的分布式处理系统,它可以在分布式的环境下像传统数据库一样为您提供海量数据服务。 Github:https://github...

3898
来自专栏cs

知识点找回2.0

Servlet(server Applet),全称Java Servlet, 是用java编写的服务器端程序。而这些Servlet都要实现Servlet的这个借...

851
来自专栏Linyb极客之路

MySQL 之 SQL 优化实战记录

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

1281

扫码关注云+社区

领取腾讯云代金券