【永利酒店赌场】Expert 诊断优化系列——————给TempDB 降温

    现在很多用户被数据库的慢的问题所困扰,又苦于花钱请一个专业的DBA成本太高。软件维护人员对数据库的了解又不是那么深入,所以导致问题迟迟不能解决,或只能暂时解决不能得到根治。开发人员解决数据问题基本又是搜遍百度各种方法尝试个遍,可能错过诊断问题的最佳时机又可能尝试一堆方法最后无奈放弃。

  上一篇我们说了索引的重要性,一个索引不仅能让一条语句起飞,也能大量减少系统对CPU、内存、磁盘的依赖。我想上一篇中的例子可以说明了。给出上一篇和目录文链接:

 

    本系列文章主要和企业IT运维人员或数据库从业者分享,如何用最快的方式解决数据库出现的问题?当问题出现时应该有的解决思路和本能的判断。让数据库问题出现时,我们不再那么慌乱,不再毫无头绪。

SQL SERVER全面优化——-索引有多重要?

  

    另外针对目前企业对数据库的应用,阐述一些最佳实践,90%的系统问题,由10%的问题导致,这里没有高大上的技术,有的只是解决那10%问题的经验。

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

 

  

  书接前文,我们知道了索引的重要,也知道了索引怎么加,那么我们应该往那些语句加?语句一条一条漫无目的的优化么?我怎么找出系统的问题语句?怎么样的一个优先级? 

  很多对数据库了解不是很多的人,也许一片茫然!还真不知道,那么多存储过程,那么多程序语句,我总不能都看一遍吧?

  对数据库有些了解的人可能会知道用profiler,系统视图等,这是个不错的方式!

  但是个人觉得这些不够直观,还是不能抓住重点,如果业务多变也会消耗大量时间。

 

  

  所谓工欲善其事,必先利其器!那么本篇我利用 Expert for
sqlserver 讲述怎样抓住重点语句来优化你的系统!**

  

  首先还是上座驾:

  

  永利酒店赌场 1

 

  

————–博客地址—————————————————————————————

Expert 诊断优化系列 

 

 

废话不多说,直接开整—————————————————————————————–

  本文选用的例子为一个服务器高配,跑了一个小业务,硬件资源充足,但是语句执行很慢!(32CPU,32G内存跑了个只有10G
数据文件的库)

  下面简单的一个展示:

  永利酒店赌场 2

  永利酒店赌场 3

  永利酒店赌场 4

 

 

   性能计数器指标请参见前文,本例中磁盘队列全天小于2,内存充足,CPU使用60%略有压力(主要是缺失索引导致)

  下面看一下总体的语句执行情况:

  永利酒店赌场 5

  

  语句可以看出超过1-3秒的语句有近8W次,3-5秒
5-10秒均接近2W,10秒以上的也有1W+,可见充足的资源配置下系统语句仍然很慢!

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

    

– 语句优先级 

  前面很多文章中都已经介绍过了,优化一定要针对重点语句,优化10条执行频率低的语句效果不及半条高频语句。那么找到系统中的高频语句就是优化的重中之重!

   直接上图!

  永利酒店赌场 6

 

    

   图中按照语句的执行次数排序,这也强烈符合我的优化套路,可以看出系统中执行频率最高的语句,平均执行时间都在3秒左右甚至更长,逻辑读都很高,但是影响的行数很少。这就是典型的缺少索引的情况!

 

   高能提示:
看到这样的一个统计界面,你是否知道如何下手了?怎么样的一个优先级?
没错
次数从高往低,来吧!开整!

  根据个人习惯也可以按照逻辑读/写,cpu消耗等排出优先级。

 

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

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

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

 

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

    永利酒店赌场 7

    

————–博客地址—————————————————————————————

Expert 诊断优化系列 

 

 

废话不多说,直接开整—————————————————————————————–

 

     Expert工具下载链接: 

针对语句调索引

  拿到了重点语句,那么我们就从重点语句下手详细分析一下。上一篇已经介绍了简单粗暴的添加索引,简单粗暴大概能应对80%的场景了,但是也要有一些注意!下面新手看官们要认真体会了!

  永利酒店赌场 8

 

   永利酒店赌场 9

 

  我们看到了缺失索引的提示,这就和前文介绍执行计划的大绿字是一个个东西。这里不再详细介绍。那么拿到这个索引缺失我们就直接创建么?前文中告诉你们的答案是直接创建!新的文章中当然要学点新东西!创建前请先核实一下索引!何为核实一下呢?
首先我们看一下执行计划!由于执行计划比较大只贴出消主要耗部分~

  永利酒店赌场 10

 

  永利酒店赌场 11

 

 

  执行计划看出,缺失语句主要消耗在两部分,都是这个customer表,index
scan
说明有相关字段的索引,但是不是最优的!那么提示的索引算是正确(字段验证这里就忽略了),那么现在可以创建了?
还需要再核查几个地方!

 

要创建索引的表有多少数据?

 

  永利酒店赌场 12

  

  表上有150W+数据 确实适合创建索引!

是否有这个类似索引?

  那么表上现在有什么索引呢?是新创建还是修改原有索引呢?

   永利酒店赌场 13

 

  一堆索引…一屏没截下….但是你会发现一个覆盖索引都没有?也没有针对这条语句的最优索引!
也许这个系统的维护人员知道索引的重要性,但是不知道怎么创建一个最优的索引,HOHO
让他看看上篇文章就好了!

  那么这回可以直接创建提示索引就OK了吧? 答案是大写的“NO”! 还需要你的细心!

  

创建的索引是否能使用? 

  前面 SQL
SERVER全面优化——-写出好语句是习惯 已经提到过,where条件的字段中不能使用函数,不能有隐式转换,也不能用
like “%XXXX%” 这样就不能用索引查找seek了!
我们要看一下是否是提示的索引不能使用!

   

  如果你仔细的看了前文,你会反问:不能用不是就不提示了么?
哈哈,真是认真,确实是这样!这里只是个需要细心的温馨提示!

  但是每一篇文章重要更深入一下么,对吧!
前面看到原计划中customer表使用了index scan ,细心的看官们会发现还有个key
lookup,index scan + key lookup 你不觉得奇怪么?

  永利酒店赌场 14

 

  我们看一下具体的语句:语句太长,只贴where 部分了  

 永利酒店赌场 15

 

  我们可以看到customername 确实使用了 like ”%%“
无法使用seek,但是companyid 和createdate 可以使用索引呀~所以我们再看一下
提示出的索引: 

CREATE NONCLUSTERED INDEX [EFS_IX_Customer_b87864c46d0f4d3ca4ad4e4db8232063]
ON [dbo].[Customer] ([CompanyId],[CreateDate])
INCLUDE ([Id],[CustomerId],[CustomerName],[Project],[IndustryOneId],[IndustryTwoId],[SourceId],[StateId],[TypeId],[ProtectId],[Audit],[delFlag])
GO

  还是比较智能吧~这回你可以创建这个索引了!

  

  

  还得啰嗦一句:覆盖索引虽好,但创建要注意,不要把过多的列放在索引里。个人建议索引的筛选列+包含列不要超过表字段的1/3
,纯属个人建议不是那么绝对。

   

  文章至此已经在上一篇的基础上又做了一些细节的说明。看官们可以按照优先级动手了。

 

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页

 

 

大面积创建缺失索引

  如果系统完全没有过保养,表上基本没有创建过什么索引,那么上面的创建方式一样很伤体力,这里还有一种简单粗暴的方式for
you!

  永利酒店赌场 19

 

 

  大批量创建索引切记不要看到就创建,一定是影响、开销、次数都很高的,并且要优化合并生成的脚本,也就是上一篇提到的精简索引!

   

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

    下面也用一个例子说明 : 

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

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)

    永利酒店赌场 20

    永利酒店赌场 21

 

 

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

 

 

 

– 根据执行计划创建

  这种方式和根据语句创建有异曲同工之妙,但不同的是一般的收集工具只收集1秒以上的语句。默认超过1秒才算慢,但是系统中有些语句执行不到一秒,但非常高频,这也是需要关注的一大类!
限于篇幅这里就不展开说了!

  永利酒店赌场 22

 

————–博客地址—————————————————————————————

Expert 诊断优化系列 

 

 


 

  总结 :
往往一个系统的整体缓慢都是因为索引问题导致的,优化索引是对你系统最简单的保养!

     
不要小看一条语句的威力,一条语句足可以让你的系统彻底无法工作!

     相反优化一条重要的高频语句就可以让你的系统变的流畅!

     

     优化索引要有自己的方法,不能逮到一条做一条,效率又差又可能抓不住重点。

     每个人优化都有自己的一套方法,只有是够系统,够全面就可以。本文只是简单介绍自己的优化方式,不喜勿喷~

 

 Expert工具下载链接: 

相关文章链接 : 

TempDB问题简单处理

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

 

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

 

    本系列主要通过 Expert for
sqlserver 
 工具讲解,分为以下几个大块:

SQL SERVER全面优化——-索引有多重要?

分成多个文件

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

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

 

SQL SERVER全面优化——-写出好语句是习惯

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

 

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

    

写给运维兄弟

  

Expert 诊断优化系列——————语句调优三板斧

 —————————————————————————————————-

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

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

为了方便阅读给出系列文章的导读链接:

文件大小、增长率要相同

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

    永利酒店赌场 23

 

Expert 诊断优化系列——————你的CPU高么?

    

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

 

TempDB磁盘划分

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

    永利酒店赌场 24

 

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

 

Expert 诊断优化系列——————内存不够用么?

    

TempDB压力诊断

Expert 诊断优化系列——————冤枉磁盘了

    

等待类型诊断

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

永利酒店赌场 25 永利酒店赌场 26

 

Expert 诊断优化系列——————语句调优三板斧

    

计数器诊断

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

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

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

 

 

 

Expert 诊断优化系列——————透过等待看系统

 

通过对象分布诊断

  

    TempDB中对象可分为三种:

  • 显式创建的用户对象

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

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

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

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

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

  • 版本存储区

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

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

 

  永利酒店赌场 27

 

  脚本奉上 :

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溢出,游标等等)

       

Expert 诊断优化系列——————给TempDB 降温

 

TempDB和语句调优

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

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

 

 

 

Expert 诊断优化系列——————锁是个大角色

 

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在数据库里如何分配页,新版本中已经不需要啦!

  永利酒店赌场 28

 

 

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

 

 

 

————–博客地址—————————————————————————————

Expert 诊断优化系列 

 


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

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

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

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

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

 

 —————————————————————————————————-

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

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

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

 

SQL SERVER全面优化——-写出好语句是习惯

 

SQL SERVER全面优化——-索引有多重要?

 

Expert 诊断优化系列————-针对重点语句调索引

 

数据库的运维策略脚本篇(内附脚本,无私分享)

 

数据库优化案例——————某市中心医院HIS系统

 

性能优化实战案例——助力某移动OA系统

 

数据库高可用实战案例——-架构优化之清爽一夏

 

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

 

 

数据库优化案例——————某知名零售企业ERP系统

 

 

网站地图xml地图