数据库实战案例—————记一次TempDB暴增的问

排查结论

  综合各项现象指标,可以分析出系统在下午14点57分左后开始执行TempDB高消耗操作,语句本身是一个近千行涉及大量表连接排序等操作的复杂存储过程,对TempDB造成暴增的问题负主要责任,而且雪上加霜的是从会话的标识可以看出,这不是一个语句只一次的执行,而是在特定的时间存在比较大的并发操作导致。

  与相关业务人员沟通,发现这是一个集团的类似报表的大消耗操作,因为对功能进行的调整,而程序人员的一次误操作而错误的指向了集群中的主库,而导致的问题。

 

--------------博客地址-----------------------------------------------------------------------------

原文地址: 

如有转载请保留原文地址! 

 

 ----------------------------------------------------------------------------------------------------

SQL SERVER 2016版本小福利

  2016 已经发布了 在2016中做了如下改动:

  2016 创建数据库时会检测CPU个数来创建tempdb,但是初始大小为8M,64M增长。

  2016 tempdb使用默认为统一区,在以前的SQL Server版本里,临时表的数据页总分配在所谓的混合区(Mixed Extends),它大小是64kb在多个数据库对象(像表和索引)间共享。这个方法是可以减少在SGAM(共享全局分配映射(Shared Global Allocation Map)页,管理混合区)页上的闩锁竞争问题(Latch Contention problem)。

  2016之前,很多人使用1117和1118跟踪标记来定义SQL Server在数据库里如何分配页,新版本中已经不需要啦!

  图片 1

 

 

   高能预警: 2016中默认的TempDB 文件数量也和本文讲述的TempDB配置个数相符合哦~~~~

 

 

 

--------------博客地址---------------------------------------------------------------------------------------

Expert 诊断优化系列 

 


  总结:TempDB经过添加多个文件,基本可以避免成为瓶颈。

     TempDB添加的文件一定要大小一致,增长率一致,否则不会起到效果。

     使用临时表等对语句优化是常用手段,但一定要保持一个平衡,切勿过度使用。

      通过语句优化一样能降低TempDB压力,如检查执行计划,是否有一些计划创建了大量的临时对象、假脱机、排序或者工作表。对此,你需要把一些临时对象清理掉。比如,在列中创建用于order by的索引可以考虑移除排序。

     TempDB的文件分配是优化的常规配置。

 

 ----------------------------------------------------------------------------------------------------

注:此文章为原创,欢迎转载,请在文章页面明显位置给出此文链接!
若您觉得这篇文章还不错请点击下右下角的推荐,非常感谢!

  引用高大侠的一句话 :“拒绝SQL Server背锅,从我做起!”

系列文章导读请关注 :  SQL SERVER全面优化-------Expert for SQL Server 诊断系列

 

  文件看问题

  TempDB暴增必然伴随着文件的增长,首先我们看一下TempDB文件的增长情况。

  图片 2

 

  可见TempDB的分配空间在14点50几分的时候开始暴增,细心的朋友会发现这是1个G到6个G的增长,这是因为客户的TempDB配置了16个数据文件:

  

  图片 3

 

  注:为什么配置这么多TempDB文件请参见:Expert 诊断优化系列------------------给TempDB 降温

文件大小、增长率要相同

   这里需要注意一个小细节,你所分配的文件必须大小一致,如果设置自动增长那么增长率要相同

    图片 4

 

深入指标分析

分成多个文件

    作为一般规则,如果逻辑处理器数小于或等于 8,使用和逻辑处理器相同数量的数据文件。如果逻辑处理器数大于 8 时,使用 8 个数据文件,然后如果仍然存在争用,增加数据文件数4 的倍数(最多的逻辑处理器数)直到争用降低到可接受的程度或对工作负荷/代码进行更改。

    在网上流传的各种TempDB 配置文档中,都描述的是使用逻辑处理器相同数量的数据文件。一般情况下是没什么问题,但是有一点需要注意:如果程序中有内存不足蔓延到tempDB的情况,或频繁的使用数据量大的临时数据Worktables 等,性能反而会下降,因为你的文件被分成多个,但数据写入的时候就需要轮循(round-robin),简单理解这样会有一定的时间损失,且读取的时候随机IO 也会多消耗IO资源和时间。有兴趣的朋友可以详见 :

什么造成了增长

  造成TempDB暴增原因很多语句使用临时表、语句排序、CheckDB等,但这些都是可以在语句中反应出来。所以下面我们分析一下语句。

  注:很多使用过SQL专家云平台或工具的朋友可能不会注意里面一些细节,其实很多细节(如上面的TempDB文件增长趋势和下面的语句分配空间)的设计都是解决一些疑难的问题。

  

  首先语句中的其中两个指标用户分配空间(MB)和内部对象分配空间(MB)指的就是对TempDB的使用消耗。

  注:用户分配空间可能是临时表使用的比较多,内部对象分配空间可能是排序或者hash join等操作(其他使用的消耗可以参见前面给出的链接文章)

  图片 5

  分配空间越大,也就说明语句越消耗TempDB资源。

  我们有两种方式找到到底是什么操作导致的TempDB暴增,可以直接找时间点的语句,也可以在TempDB资源消耗的高到低顺序中找!

  为了全面了解一下TempDB的问题,这里我们采用第二种方式。

  那么我们就分析一下语句对TempDB资源被消耗的情况:

  步骤1:首先我们按照用户对象分配空间排序:图片 6

 

 经过排查,这里面用户空间分配比较高的都是CDC的作业,服务器上确实运行这几个库的CDC作业。其他的一些操作的用户分配空间都比较小,所以这不是造成问题的原因!

 

 步骤2:接着我们按照内部对象分配空间排序:

 图片 7

 

 这里发现最消耗空间的是CheckDB的操作,但时间点是对应不上的,所以这也不是问题的原因。

 继续排查:

 在消耗排位在第三的这个语句中我们发现了等待资源FGCB_ADD_REMOVE(这个可以简单理解为有大量的文件自动生长发生,这里是16个TempDB文件),并且使用的内部对象空间也很高,并且我们还发现有多个会话同时执行这样的高消耗操作。图片 8

 

 继续深入:

 图片 9

  性能计数器的表象也与之前的种种迹象相吻合。

  图片 10

 

A SQL Server DBA myth a day: (12/30) tempdb should always have one data file per processor core

 

    这里说的看官们好像也不知道我该使用几个了...对于系统最佳实践,非常精细化的优化时可能才需要考虑上面的问题,对于一般系统TempDB一般可以配置成8 或16 个Temp文件就足够了,如果还有大量争取就继续增加(一般情况不要超过你的逻辑CPU数量)。

    

场景描述

  客户系统比较稳定,用了5台机器做了AlwaysOn高可用组,完全实现了读写分离。磁盘也做了规划,主库日常操作TempDB需求在20G以下,所以TempDB所在的磁盘只配置了100个G的空间。

  本案例是客户突然接到监控报警,显示TempDB磁盘空间不足,可用空间不断减小直到耗尽。

  比较戏剧的是,这个客户早上刚刚做了巡检数据库情况稳定,没有什么异常。

  那么我初步判定,这必然是一次特殊操作或应用配置出错导致的问题。

等待类型诊断

  TempDB的争用压力在等待篇中已经简单介绍,等待的表现为 pagelatch_类等待,等待的资源是 “2: X :X ”

图片 11 图片 12

 

前言

  很多时候数据库的TempDB、日志等文件的暴增可能导致磁盘空间被占满,如果日常配置不到位,往往会导致数据库故障,业务被迫中断。

  这种文件暴增很难排查,经验不足的一些运维人员可能更是无法排查具体原因,导致问题不能彻底解决。

计数器诊断

  计数器中我们主要看以下几个计数器:

  1. Workfiles Created/sec 
  2. Worktables Created/sec 
  3. Active Temp Tables  
  4. Temp Tables Creation Rate
  5. Temp Tables For Destruction   

  这里的标准各不相同就不细说了。

 

 

 

 总结

  问题的结果往往比较简单,也相对容易解决,但综合各项指标深入分析问题原因是值得和每一个技术人员探讨的,这也是为什么用一篇长案例来分析一个小点的原因。

  

  再思考:本案例中服务器的架构设计是比较完善的,已经做了读写分离可以轻松的把这样的大操作指向辅助服务器,并且可以做到负载均衡,那么如果你的单机服务器也有类似这样一个报表操作,你会怎么办呢?

 ----------------------------------------------------------------------------------------------------

注:此文章为原创,欢迎转载,请在文章页面明显位置给出此文链接!
若您觉得这篇文章还不错请点击下右下角的推荐,非常感谢!

  

SQL SERVER全面优化-------Expert for SQL Server 诊断系列

    这篇我们来说说TempDB,这个系统数据库如何进行优化,怎么样平衡他的使用。

    首先简单介绍一下TempDB:Tempdb是SQL Server里的一个重要的系统数据库。并且每个实例中只有一个TempDB,也就是当你在一个实例下创建了100个数据库,这100个数据库也只能用这一个TempDB。是不是感觉到了他的压力会很大?还没完呢!许多用户的操作,都有可能使用到它。最常见的当然是用户使用临时表或者表变量。其他可能性有,用户使用trigger,Snapshot Isolation Level,某些复杂的查询,以及DBCC CHECKDB等。听起来这是要爆炸的节奏呀!他不会爆炸,这么说只是想你提高对他的关注性,很多系统性能问题就出在他身上!

 

    一如既往还是用一个例子说明: 语句相当于“车”,硬件相当于 “路” ,等待相当于 “红绿灯”,那么TempDB 相当于什么呢? “服务区停车场

    图片 13

    

--------------博客地址---------------------------------------------------------------------------------------

Expert 诊断优化系列 

 

 

废话不多说,直接开整-----------------------------------------------------------------------------------------

 

TempDB问题简单处理

    上面描述的看起来好像需要对SQL SERVER掌握的很深,才能处理这个问题。其实很简单 ,只需要你做一件事情就可以搞定TempDB的大部分问题!那就是把TempDB设置成多个来分摊这个压力。

 

    “服务区停车场” 可以设置多个收费口来避免拥堵和排队!

 

  

数据库实战案例—————记一次TempDB暴增的问题排查。TempDB压力诊断

我创建个临时表跟系统页还有关系?

    下面也用一个例子说明 : 

    创建临时表的时候会对系统表中进行插入和更新,而删除临时表逆向过程会删除或更新系统表!

use [AdventureWorks2012]
GO
checkpoint
go
create table #t
(
id int
)
drop table #t


use tempdb
go
select Operation,CONTEXT,[Transaction ID],AllocUnitId,AllocUnitName,[Page ID],[Transaction Name],Description from fn_dblog(null,null)

    图片 14

    图片 15

 

 

    所以**当你并发过高且频繁创建删除临时表的时候就会造成大量的争用。**

 

 

 

TempDB压力从哪来?

    当数据库创建一张新表的时候,SQL Server要为这张表分配存储页面,同时SQL Server也要修改SGAM, PFS, 和GAM页面,把已经分配出去的页面标志成已使用。所以每创建一张新表,SGAM, PFS, 和GAM这些系统页面都会有修改动作。这种行为对一般的用户数据库不会有问题,因为正常的应用不会折腾着不停地建表、删表。但是tempdb就不同了。如果一个存储过程使用了临时表,而这个存储过程被并发用户广泛使用,那很自然地就会有很多并发用户在tempdb里同时创建表,做完了以后又删除表。这样,在一个时间点,会有很多任务要修改SGAM, PFS, 或GAM页面。但是为了维护物理的一致性,对于同一个页面,SQL Server在一个时间点同时只允许一个用户修改它。所以对于tempdb,如果同时有很多很多人要在同一个数据文件里分配空间,那这个数据文件的SGAM, PFS, 或GAM页面,就有可能成为系统瓶颈。大家只能一个一个做,并发度上不去。

    这就好像你进停车场要登记交费一样!一个一个来不要急~

    直接上例子: 

    图片 16

 

    等待资源为 : “2:1:3” 这是什么意思? ID 为 2 的数据库(TempDB)的 1号文件 的 页码为3的页(SGAM页面)!

 

    图片 17图片 18

 

 

    这里关于系统页不过多的介绍,想详细了解的朋友请参见 :  SQL Server中的GAM页和SGAM页

 

通过对象分布诊断

  

    TempDB中对象可分为三种:

  • 显式创建的用户对象

  这些对象由用户显式创建。存在于用户会话的作用域中,也可位于创建对象所用的例程(存储过程、触发器或用户定义函数)的作用域中。

  包括:表和索引(系统的,或用户定义的)、临时表和索引(全局的,或局部的)、表变量、表值函数中返回的表。

  • 数据库引擎创建的内部对象

  这些内部对象由数据库引擎根据需要而创建,用于处理SQL Server语句。可以在语句的作用域中创建和删除。每个内部对象至少使用9个页面:1个IAM页,1个连续8页的区。

  包括:用于游标或假脱机操作以及临时大型对象(LOB)存储的工作表;用于HASH连接或HASH聚合操作的工作表;用于创建或重新生成索引等操作(如果指定了SORT_IN_TEMPDB)的中间排序结果,或者某些GROUP BY、ORDER BY或UNION查询的中间排序结果。

  • 版本存储区

  版本存储区是数据页的集合,它包含支持使用行版本控制的功能所需的数据行,主要用来支持快照事务隔离级别,以及一些其它提高数据库并发性能的新功能。主要分为2类:公用版本存储区、联机索引生成版本存储区。

  包括:由使用快照隔离级别或已提交隔离级别(基于行版本控制)的数据库中的数据修改事务生成的行版本;由数据修改事务为实现联机索引操作、多个活动的结果集(MARS)以及AFTER触发器等功能而生成的行版本。

 

  图片 19

 

  脚本奉上 :

SELECT 'tempdb' AS DB,GETDATE() AS TIME,
SUM (user_object_reserved_page_count)*8 as [用户对象(kb)], ----如临时表的使用
SUM (internal_object_reserved_page_count)*8 as [内部对象(kb)], -----如连接hash 使用的空间
SUM (version_store_reserved_page_count)*8  as [纪录版本空间(kb)],
SUM (unallocated_extent_page_count)*8 as [可用空间(kb)],
SUM (mixed_extent_page_count)*8 as [mixedextent(kb)]
FROM sys.dm_db_file_space_usage

 

 

   高能预警:如果用户对象分配空间持续使用很大,基本可以说明你的程序代码中过度依赖TempDb 过并发高的存储过程中有大量的临时表使用。如果内部对象持续很高,说明你的程序中有很多语句写法可以优化(如排序、hash join溢出,游标等等)

       

TempDB磁盘划分

    大多数情况下,TempDB的文件不需要拆分磁盘,在同一个磁盘即可,如果压力大可以选择放置在一个单独的磁盘中,这样不会与其他文件(如数据读写)发生磁盘资源竞争。

    图片 20

 

    如果出现TempDB 读取响应时间高的情况,请考虑,TempDB的磁盘相关优化。

 

TempDB和语句调优

    语句调优篇提到语句中使用临时表或表变等会减少语句的复杂度,提升语句的效率,是常用的三板斧之一,但这里的需要一个平衡。如果对语句过度使用会造成文中提到的TempDB压力。那么怎么样平衡呢?下面给出几点建议:

  1. 切记不要过度使用!临时表的使用主要有两个场景,拆分语句降低复杂性。另一个是缓存中间结果避免重复操作。
  2. 减少使用临时表锁系统表的时间!”select 字段 into #临时表 from“ 如果语句执行时间过长这将是灾难,尽量选用先创建,后插入的做法。
  3. 临时表也是有缓存的,查找哪些对象没有被缓存,为什么发生这样的情况!参见 :Sql Server tempdb原理-缓存机制解析实践

 

 

 

    前面文章针对CPU、内存、磁盘、语句、等待讲述了SQL SERVER的一些基本的问题诊断与调优方式。为了方便阅读给出导读文章链接方便阅读:

本文由上海时时乐走势图发布于上海时时乐走势图官网,转载请注明出处:数据库实战案例—————记一次TempDB暴增的问

您可能还会对下面的文章感兴趣: