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

by admin on 2019年9月5日

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

  想了好久索引的重要性应该怎么写?讲原理结构?我估计大部分人不愿意看,也不愿意花那么多时间仔细研究。光写应用?感觉不明白原理一样不会用。举例说明?情况太多也写不全….到底该怎么写呢?

  想了好久索引的重要性应该怎么写?讲原理结构?我估计大部分人不愿意看,也不愿意花那么多时间仔细研究。光写应用?感觉不明白原理一样不会用。举例说明?情况太多也写不全….到底该怎么写呢?

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

  随便写吧,想到哪写到哪!

  随便写吧,想到哪写到哪!

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

 
 
前面很多篇不管CPU、内存、磁盘、语句等等等都提到了索引的重要,我想刚刚开始学数据库的在校学生都知道索引对语句性能的重要性。但他们可能不知道,对语句的重要性就是对系统的重要性!

 
 
前面很多篇不管CPU、内存、磁盘、语句等等等都提到了索引的重要,我想刚刚开始学数据库的在校学生都知道索引对语句性能的重要性。但他们可能不知道,对语句的重要性就是对系统的重要性!

    

 

 

     Expert工具下载链接: 

  

  

 

  抛出一个问题
:你相信一条语句就能让你的大系统挂掉么?

  抛出一个问题
:你相信一条语句就能让你的大系统挂掉么?

 

 

 

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

 

 

 

  带着问题,首先还是贴出我的座驾

  带着问题,首先还是贴出我的座驾

写给运维兄弟

  

 图片 1

 图片 1

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

    

  最近不太喜欢红色换了一辆!

  最近不太喜欢红色换了一辆!

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

    

 

 

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

    

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

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

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

    

Expert 诊断优化系列 

Expert 诊断优化系列 

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

 

 

 

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

 

 

 

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

 

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

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

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

 

 

 

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

 

开篇小测验

  下面这样一个小SQL 你该怎么样添加最优索引

  两个表上现在只有聚集索引

  bigproduct 表上已经有聚集索引 ProductID

 

  bigtransactionhistory 表上已经有聚集索引 TransactionID

  

select p.productnumber,p.reorderpoint,th.Quantity
from bigproduct as p
join bigtransactionhistory as th on th.productid=p.productid and th.TransactionDate > p.SellStartDate
where p.name in ('LL Crankarm1000','ML Crankarm1000') and th.TransactionDate > '2010-01-01'

 

 

  你是否一眼就能看出来呢?

  

  答案将在文章中逐步揭晓~~~

开篇小测验

  下面这样一个小SQL 你该怎么样添加最优索引

  两个表上现在只有聚集索引

  bigproduct 表上已经有聚集索引 ProductID

 

  bigtransactionhistory 表上已经有聚集索引 TransactionID

  

select p.productnumber,p.reorderpoint,th.Quantity
from bigproduct as p
join bigtransactionhistory as th on th.productid=p.productid and th.TransactionDate > p.SellStartDate
where p.name in ('LL Crankarm1000','ML Crankarm1000') and th.TransactionDate > '2010-01-01'

 

 

  你是否一眼就能看出来呢?

  

  答案将在文章中逐步揭晓~~~

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

 

简单粗暴的添加索引

  看过我前面文章的看官们一定会发现我很喜欢用“简单粗暴”这个词,一是因为词汇量小文笔也差,真心用不出高大上的词儿!
再一个,你们不喜欢简单粗暴么~~干货最重要,不是么?

  

  首先我们看一下没有优化前的执行计划

  图片 3

  

  图片 4

  clustered index scan 这其实就是表扫描,不是table scan
只是因为表上有聚集索引

  可以看出这个查询俩表都使用了表扫描!  

  

  where 条件添加索引

  首先大多数人都知道 where
条件中的字段
需要添加索引! 我们添加一下看看效果创建 

  在 bigproduct 表上创建 name 列索引
,在bigtransactionhistory表上创建 TransactionDate 列索引。

  再次执行语句看一下效果!

  图片 5

  图片 6

  

  添加where索引以后可以看到以下几个现象

  1. bigproduct 从原来的clustered index scan 变成 index seek
  2. 另外多出来个KEY Lookup(clustered)
  3. bigproduct 上添加的索引起了作用,逻辑读bigproduct 由 601 变成 10。
  4. bigtransactionhistory 没啥变化啊 还是clustered index scan

  

  解释一下出现的现象 : 首先一点bigproduct 边添加的where
条件索引,起到了作用,执行的时候不是全表扫描了,逻辑读有明显的下降,出现的
KEY Lookup
是因为选择(select)的列,在索引中没有,而需要通过聚集索引再查找一次,再找一次也意味着多一部分开销!

  那么同样添加了where 条件索引的bigtransactionhistory
表为什么没起作用呢?
那是因为SQL优化器在选择计划的时候认为,不使用TransactionDate
列索引查找效率会更好! 

  真的么? 我们来验证一下,通过指定选择索引,来让优化器选择索引查找!

  图片 7

 

  图片 8

 

   强制使用索引以后,可以看出逻辑读由 14W
变成1961W,语句时间也变得很长,这就是优化器为什么不选用你加的索引!优化器还是很智能的吧。

 

  高能预警:优化器可不是什么时候都这么智能的…由于缓存计划或优化器抽风等原因,也会出现优化器用了这种索引,导致你的语句奇慢,读飙升直接影响到你的内存、磁盘、CPU资源!另外如果这样一条语句是系统中一条很频繁运行的语句,你的系统就挂了!没错就挂了!这就是开篇抛出的问题就是因为一条语句!

 

 

  消灭Key Lookup 添加select
字段

  这就是传说中的覆盖索引! 

   看到执行计划中存在Key
Lookup 而且消耗占比很高,如上面强制索引的计划,那么我们就要想到的
在索引中包含那些SELECT 的列!如果消耗低,逻辑读少,如上面bigproduct
表中的Key Lookup
就可以忽略(如果你追求完美,也一样优化就可以了)。

   包含列的图形化创建 : @秋仙
特意给你的说明

   图片 9

   

   语句创建就是 :

   

CREATE NONCLUSTERED INDEX TransactionDate包含ProductID_Quantity
ON [dbo].[bigTransactionHistory] ([TransactionDate])

------INCLUDE 就是包含列
INCLUDE ([ProductID],[Quantity])
GO

 

 

 

   下面我们添加一下看看效果 :

   图片 10

 

   图片 11

 

  添加select
索引字段后可以看出的现象:

  1. 优化器自己选择了index seek
  2. bigtransactionhistory占比最高的Key
    Lookup消失了
  3. 逻辑读由原来无索引的14W变成1W
  4. bigtransactionhistory表还提示缺少索引?

   

   通过优化索引添加select
字段,我们看出语句又一次得到了提升 bigtransactionhistory
从表扫描变成索引查找,逻辑读由14W变成
1W!这是一个质的飞跃啊!

CREATE NONCLUSTERED INDEX TransactionDate包含ProductID_Quantity
ON [dbo].[bigTransactionHistory] ([TransactionDate])

------INCLUDE 就是包含列
INCLUDE ([ProductID],[Quantity])
GO

   那为什么还提示缺少索引呢? 创建一下试试吧!

  索引再优化加入表关联列

  按照提示我们创建索引 : 和上一个索引的不同 ProductID
列由包含列变成了索引列!

USE [AdventureWorks2012]
GO
CREATE NONCLUSTERED INDEX ProductID_TransactionDate包含Quantity
ON [dbo].[bigTransactionHistory] ([ProductID],[TransactionDate])
INCLUDE ([Quantity])

 

  我们看一下效果:

  图片 12

 

  图片 13

 

  再次优化索引以后可以看到以下几个现象

  1. bigtransactionhistory表还是索引查找index
    seek

  2. bigtransactionhistory依然没有了Key
    Lookup

  3. 两表关联的hash join 变成了nested
    loops
  4. 并行计划变成了串行
  5. 逻辑读又从1W 变成18

 

  又一次质的飞跃!读从原来的14W 变成1W
又变成18,这样大大减少了内存和IO的消耗,另外并行计划也变成了串行,无疑又减少了大量CPU的消耗!语句时间,我想这里就不用多说了吧?

  

  高能预警:这里所说的hash
join,并行变串行,不懂的朋友可以在百度自行学习,这里只是针对当前语句的情况,不能一概而论!

 

 

 

  精简你的索引

  大家都知道,索引会导致update、insert、delete操作变慢!那么尽量精简你的索引就是一个很重要的话题了!

   上面的优化过程中我们创建了几个索引,以bigTransactionHistory为例来看一下:

  图片 14

   脚本这里就不贴了,其实我们最后创建的索引 ProductID_TransactionDate包含Quantity
已经包含了前两个索引,而且可以说无论任何类似语句都使用ProductID_TransactionDate包含Quantity
就可以了!

   那么我们就可以清除前两个索引!

    

   

 

 

 

 

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

Expert 诊断优化系列 

 

 


 

  至此语句的优化算是结束了,留下的就是bigproduct 依然有一个Key
Lookup可以优化,可以仿照上面的继续优化,这里就不细说了。语句只是经过了简单的索引优化就从一辆2手QQ变成了法拉利,是不是很神奇?

  这就是索引的重要性!

 

  开篇小测试你做对了么?如果没做对那么这么请你自行模拟一个场景再现本篇的话题吧!

 


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

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

     

     一个问题随之而来语句一条一条漫无目的的优化么?我怎么找出系统的问题语句?怎么样的一个优先级? 

     请参见前文 : Expert
诊断优化系列——————语句调优三板斧

     后一篇我将使用 Expert for
sqlserver  工具讲述怎么样针对重点语句调索引,喜欢的看官请mark了! 

简单粗暴的添加索引

  看过我前面文章的看官们一定会发现我很喜欢用“简单粗暴”这个词,一是因为词汇量小文笔也差,真心用不出高大上的词儿!
再一个,你们不喜欢简单粗暴么~~干货最重要,不是么?

  

  首先我们看一下没有优化前的执行计划

  图片 3

  

  图片 4

  clustered index scan 这其实就是表扫描,不是table scan
只是因为表上有聚集索引

  可以看出这个查询俩表都使用了表扫描!  

  

  where 条件添加索引

  首先大多数人都知道 where
条件中的字段
需要添加索引! 我们添加一下看看效果创建 

  在 bigproduct 表上创建 name 列索引
,在bigtransactionhistory表上创建 TransactionDate 列索引。

  再次执行语句看一下效果!

  图片 5

  图片 6

  

  添加where索引以后可以看到以下几个现象

  1. bigproduct 从原来的clustered index scan 变成 index seek
  2. 另外多出来个KEY Lookup(clustered)
  3. bigproduct 上添加的索引起了作用,逻辑读bigproduct 由 601 变成 10。
  4. bigtransactionhistory 没啥变化啊 还是clustered index scan

  

  解释一下出现的现象 : 首先一点bigproduct 边添加的where
条件索引,起到了作用,执行的时候不是全表扫描了,逻辑读有明显的下降,出现的
KEY Lookup
是因为选择(select)的列,在索引中没有,而需要通过聚集索引再查找一次,再找一次也意味着多一部分开销!

  那么同样添加了where 条件索引的bigtransactionhistory
表为什么没起作用呢?
那是因为SQL优化器在选择计划的时候认为,不使用TransactionDate
列索引查找效率会更好! 

  真的么? 我们来验证一下,通过指定选择索引,来让优化器选择索引查找!

  图片 7

 

  图片 8

 

   强制使用索引以后,可以看出逻辑读由 14W
变成1961W,语句时间也变得很长,这就是优化器为什么不选用你加的索引!优化器还是很智能的吧。

 

  高能预警:优化器可不是什么时候都这么智能的…由于缓存计划或优化器抽风等原因,也会出现优化器用了这种索引,导致你的语句奇慢,读飙升直接影响到你的内存、磁盘、CPU资源!另外如果这样一条语句是系统中一条很频繁运行的语句,你的系统就挂了!没错就挂了!这就是开篇抛出的问题就是因为一条语句!

 

 

  消灭Key Lookup 添加select
字段

  这就是传说中的覆盖索引! 

   看到执行计划中存在Key
Lookup 而且消耗占比很高,如上面强制索引的计划,那么我们就要想到的
在索引中包含那些SELECT 的列!如果消耗低,逻辑读少,如上面bigproduct
表中的Key Lookup
就可以忽略(如果你追求完美,也一样优化就可以了)。

   包含列的图形化创建 : @秋仙
特意给你的说明

   图片 9

   

   语句创建就是 :

   

CREATE NONCLUSTERED INDEX TransactionDate包含ProductID_Quantity
ON [dbo].[bigTransactionHistory] ([TransactionDate])

------INCLUDE 就是包含列
INCLUDE ([ProductID],[Quantity])
GO

 

 

 

   下面我们添加一下看看效果 :

   图片 10

 

   图片 11

 

  添加select
索引字段后可以看出的现象:

  1. 优化器自己选择了index seek
  2. bigtransactionhistory占比最高的Key
    Lookup消失了
  3. 逻辑读由原来无索引的14W变成1W
  4. bigtransactionhistory表还提示缺少索引?

   

   通过优化索引添加select
字段,我们看出语句又一次得到了提升 bigtransactionhistory
从表扫描变成索引查找,逻辑读由14W变成
1W!这是一个质的飞跃啊!

CREATE NONCLUSTERED INDEX TransactionDate包含ProductID_Quantity
ON [dbo].[bigTransactionHistory] ([TransactionDate])

------INCLUDE 就是包含列
INCLUDE ([ProductID],[Quantity])
GO

   那为什么还提示缺少索引呢? 创建一下试试吧!

  索引再优化加入表关联列

  按照提示我们创建索引 : 和上一个索引的不同 ProductID
列由包含列变成了索引列!

USE [AdventureWorks2012]
GO
CREATE NONCLUSTERED INDEX ProductID_TransactionDate包含Quantity
ON [dbo].[bigTransactionHistory] ([ProductID],[TransactionDate])
INCLUDE ([Quantity])

 

  我们看一下效果:

  图片 12

 

  图片 13

 

  再次优化索引以后可以看到以下几个现象

  1. bigtransactionhistory表还是索引查找index
    seek

  2. bigtransactionhistory依然没有了Key
    Lookup

  3. 两表关联的hash join 变成了nested
    loops
  4. 并行计划变成了串行
  5. 逻辑读又从1W 变成18

 

  又一次质的飞跃!读从原来的14W 变成1W
又变成18,这样大大减少了内存和IO的消耗,另外并行计划也变成了串行,无疑又减少了大量CPU的消耗!语句时间,我想这里就不用多说了吧?

  

  高能预警:这里所说的hash
join,并行变串行,不懂的朋友可以在百度自行学习,这里只是针对当前语句的情况,不能一概而论!

 

 

 

  精简你的索引

  大家都知道,索引会导致update、insert、delete操作变慢!那么尽量精简你的索引就是一个很重要的话题了!

   上面的优化过程中我们创建了几个索引,以bigTransactionHistory为例来看一下:

  图片 14

   脚本这里就不贴了,其实我们最后创建的索引 ProductID_TransactionDate包含Quantity
已经包含了前两个索引,而且可以说无论任何类似语句都使用ProductID_TransactionDate包含Quantity
就可以了!

   那么我们就可以清除前两个索引!

    

   

 

 

 

 

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

Expert 诊断优化系列 

 

 


 

  至此语句的优化算是结束了,留下的就是bigproduct 依然有一个Key
Lookup可以优化,可以仿照上面的继续优化,这里就不细说了。语句只是经过了简单的索引优化就从一辆2手QQ变成了法拉利,是不是很神奇?

  这就是索引的重要性!

 

  开篇小测试你做对了么?如果没做对那么这么请你自行模拟一个场景再现本篇的话题吧!

 


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

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

     

     一个问题随之而来语句一条一条漫无目的的优化么?我怎么找出系统的问题语句?怎么样的一个优先级? 

     请参见前文 : Expert
诊断优化系列——————语句调优三板斧

     后一篇我将使用 Expert for
sqlserver  工具讲述怎么样针对重点语句调索引,喜欢的看官请mark了! 

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

 

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

 

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

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

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

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

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

 

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

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

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

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

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

 

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

 

 

 

 

 

 

———————-深入索引原理推荐博客————————–

目测这几篇文章每篇的编写时间都要超过10小时,非常值得阅读!

pursuer.chen 的博客

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

 

 

 

 

 

 

———————-深入索引原理推荐博客————————–

目测这几篇文章每篇的编写时间都要超过10小时,非常值得阅读!

pursuer.chen 的博客

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

 

SQL Server 深入解析索引存储(上)

SQL Server 深入解析索引存储(上)

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

 

SQL Server 深入解析索引存储(中)

SQL Server 深入解析索引存储(中)

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

 

 

SQL Server 深入解析索引存储(下)

 

桦仔的博客

SQL Server 深入解析索引存储(下)

 

桦仔的博客

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

 

 

SQLSERVER聚集索引与非聚集索引的再次研究(上)

SQLSERVER聚集索引与非聚集索引的再次研究(上)

SQLSERVER聚集索引与非聚集索引的再次研究(下)

     

SQLSERVER聚集索引与非聚集索引的再次研究(下)

     

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图