SQL Server 优化---为什么索引视图(物化视图)需要with(noexpand)强制查询提示

原文:SQL Server 优化---为什么索引视图(物化视图)需要with(noexpand)强制查询提示

 

本文出处:http://www.cnblogs.com/wy123/p/6694933.html 

 

第一次通过索引视图优化SQL语句,以及遇到的一些问题,记录一下。

 

语句分析

最近开发递交过来一个查询统计的SQL,说是性能有问题,原本执行需要4-5秒钟,这个业务本身对性能要求又比较critical,期望是在1s之内
在用尽各种办法之后(执行计划,统计信息,索引,改写SQL,临时表拆分),依然没有实质性的改观,
在观察SQL本身的特点之后,
有以下几个特点
  1,查询语句整体为多表join,但是每个表自身的数据并不是非常大,百万级
  2,查询结果在主表上一个较大的时间范围的数据进行Count的聚合操作
  3,几张表之间除了连接条件,主要是进行了一些比较复杂的逻辑运算(下面截图可以看到,没多少IO,CPU时间却很高)
    不过表的连接方式都是inner join,主要性能点就在于表关联之间的Hash join之间的逻辑运算,参考下图(是执行计划的一部分)

  通过统计信息发现,该SQL语句的物理IO并不高,说明索引没有什么问题,通过索引改善IO可能改善空间很有限
  时间主要花费在SQL语句的编译和的Hash join 运算
  尝试改写借助SQL之后(纯改写SQL和借助临时表拆分语句),发现依然很难绕过Hash join,主要是表之间的逻辑运算最为耗时
  最后想到可以先将运算好的的数据物理存储起来,然后改写查询的SQL语句完成等价的查询,
  避免每次查询都做复杂的逻辑运算,应该可以有比较大的改善,于是就想到了索引视图

  

创建索引视图改写SQL

  在提取出来原始的查询SQL,创建索引视图,并在索引视图上创建unique cluster index和合理的nocluster index
  通过索引视图改写原始的查询统计SQL语句,
  改写后的SQL语句是一个索引视图替代原始的4张表,与另外一个物理表join,发现效率上没有任何改善,
  观察改写后的SQL语句的执行计划,发现跟原始SQL一样,并没有走索引视图上的索引,或者说是用到索引视图,一时间觉得好心塞,实在没招了
  执行计划依旧是长长的一大段,然后依旧是好几个Hash Join.参考下图,执行计划跟本文第一个截图一模一样

  按道理,索引视图固化结果集,并且根据情况做了过滤,结果集是原始查询的一部分而已,
  用同样的查询条件从索引视图做查询统计,走索引视图代价肯定要比原始SQL低,通过强制不展开with(noexpand)提示,证实了这个推断
  如下是强制不展开索引视图的统计信息,可以看到完全达到了预期的1S之内

  

 

  当然索引视图也不是没有代价,在一定程度上牺牲了数据写入的效率和冗余存储,来换取查询的效率
  之前简单介绍过索引视图的一些特点 http://www.cnblogs.com/wy123/p/6041122.html

 

索引视图被展开(expand)的原因分析

  最关键的问题在于,没有强制不展开索引视图(with(noexpand)提示)的情况下,为什么没有走索引视图呢?

  这个问题确实郁闷了一阵子,整个半天都在想这个问题,因为索引视图本身用的就少,出现此问题更是一头雾水,不过Google一下,还有一大把类似的问题
  如下是在google的时候查到的,原文是英文,大概意思如下,非直译。

  参考链接:http://dba.stackexchange.com/questions/26979/what-factors-go-into-an-indexed-views-clustered-index-being-selected/27039#27039

索引视图的匹配(查询用索引视图替代而不走原始的基础表)是一个相当昂贵的操作,因此优化器试图通过其他方式快速转换(生成执行计划)
如果优化器产生了一个相对优化的执行计划,就可以尽早结束(不必继续生成其他执行计划)
问题就在于:继续生成其他执行计划的代价要大于已生成的执行计划的代价
听起来有点别扭,
之前举过这么一个例子,比如说是花3秒钟找到一个相对优化的执行计划,这个执行计划完成SQL的执行需要2秒钟
与花10秒钟的时候找到一个最优化的执行计划,尽管这个执行计划完成SQL的执行可能只需要0.5秒钟,虽然后者的执行计划更优,但整体代价更大
优化器的主要目标是尽快找到一个足够好的执行计划(而非总是生成最好的执行计划)

使用索引视图本身并不是一个昂贵的操作,
但是与潜在索引视图中匹配逻辑查询树确实一个代价较大的行为,在查询涉及的视图在优化器优化之前已经被展开
因此优化器并不知道你的查询是否未被了索引视图,他看到的只是展开的查询树
这个通俗地讲就是:
让sqlserver知道,一个查询,可以用索引视图中的结果等价替代视图逻辑中原始的基表,是一个代价较大的过程
因为SQL Server根据原始的基础表,生成一种执行计划之后,就不去判断是否可以用索引视图做等价替代。

   当然白皮书里有更详细的介绍,里面索引视图相关的一些逻辑实现和分析http://www.cs.cmu.edu/~natassa/courses/15-823/current/papers/vldb00.pdf

 

最后多逼逼一句:
  上面的白皮书实在看不懂,只是Google出来说详细信息请看这个参考资料,
  这里只是从现象去推断优化器在面对索引视图的一些特性,了解到内在机制的一些特点,可能潜在的问题,以及对应的解决办法
  这里又提开源软件了,别说不开源了,即便是开源了,除了钱的因素,在技术上,有几个人可以动一下这些大型软件的核心代码?
  我并不是在黑开源软件啊,只是听有人说,开源好啊,有源码怎么怎么样,觉得够装13的,
  国内除了bat等少数几家公司做过修改源码,做过开源数据库的定制,试问还有多少家可以修改数据库源码然后上生产使用

  最近在学MySQL,继续SQL Server下去恐怕踏马要失业了,开源,对大多数公司来说,钱才是一个大头因素吧,尼玛跑题了。

   

时间: 2024-06-19 10:42:35

SQL Server 优化---为什么索引视图(物化视图)需要with(noexpand)强制查询提示的相关文章

Sql Server优化之索引提示----我们为什么需要查询提示,Sql Server默认情况下优化策略选择的不足

原文:Sql Server优化之索引提示----我们为什么需要查询提示,Sql Server默认情况下优化策略选择的不足 环境: Sql Server2012 SP3企业版,Windows Server2008 标准版   问题由来: 最近在做DB优化的时候,发现一个存储过程有非常严重的性能问题, 由于整个SP整体逻辑是一个多表关联的复杂的查询,整体结构比较复杂的,通过的分析和尝试, 最后发现问题出在其中一个大表的查询上实现方式上, 因为这个大表上的意外的执行方式,导致其他表无法被驱动,其他表也

Sql server优化其索引的小技巧

关于索引的常识:影响到数据库性能的最大因素就是索引.由于该问题的复杂性,我只可能简单的谈谈这个问题,不过关于这方面的问题,目前有好几本不错的书籍可供你参阅.我在这里只讨论两种SQL Server索引,即clustered索引和nonclustered索引.当考察建立什么类型的索引时,你应当考虑数据类型和保存这些数据的column.同样,你也必须考虑数据库可能用到的查询类型以及使用的最为频繁的查询类型. 索引的类型 如果column保存了高度相关的数据,并且常常被顺序访问时,最好使用cluster

八大案例,带你参透SQL Server优化

在本文中,石沫针对用户遇到的各种实际问题,从实例层次到架构,通过8个SQL Server优化案例,分享了如何用最简单快捷的方式解决用户使用SQL Server数据库过程中的典型问题,使SQL Server能够稳定地提供持续服务. 以下是整理内容. 瓶颈诊断 工欲善其事,必先利其器.常见的分析性能问题的工具有三种:诊断硬件资源,等待类型,性能语句. 硬件资源通常有四个方面判断: CPU监控,性能计数器主要包括%Processor Time.Processor Queue Length.Batch

SQL Server基础之<索引>

原文:SQL Server基础之<索引>  索引用于快速找出在某个列中有某一特定值的行,不使用索引,数据库必须从第一条记录开始读完整个表,直到找出相关的行.表越大,查询数据所花费的时间越多,如果表中查询的列有一个索引,数据库能快速到达一个位置去搜索数据,本篇主要介绍与索引相关的内容,包括索引的含义和特点,索引的分类,索引的设计原则以及如何创建和删除索引. 一:索引的含义和特点  索引是一个单独的,存储在磁盘上的数据结构,它们包含则对数据表里所有记录的引用指针,使用索引用于快速找出在某个或多个列

[SQL Server优化]善用系统监视器,确定系统瓶颈

原文:[SQL Server优化]善用系统监视器,确定系统瓶颈 来自: http://hi.baidu.com/solorez/blog/item/f82038fa0e71b78d9e51468c.html 如果您运行的是 Microsoft Windows 服务器操作系统,则可以使用系统监视器图形工具来测量系统当前的性能参数.可以查看包括处理器.内存.缓存.线程和进程等数据.每个对象都有一个相关的的计数器集,用于测量设备使用情况.队列长 度.延时情况,另外还有吞吐量及内部拥塞指示器. 监视磁盘

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

原文:SQL Server 深入解析索引存储(中) 标签:SQL SERVER/MSSQL SERVER/数据库/DBA/索引体系结构/堆 概述      本篇文章是关于堆的存储结构.堆是不含聚集索引的表(所以只有非聚集索引的表也是堆).堆的 sys.partitions 中具有一行,对于堆使用的每个分区,都有 index_id = 0.默认情况下,一个堆有一个分区.当堆有多个分区时,每个分区有一个堆结构,其中包含该特定分区的数据.例如,如果一个堆有四个分区,则有四个堆结构:每个分区有一个堆结构

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

原文:SQL Server 深入解析索引存储(下)   标签:SQL SERVER/MSSQL SERVER/数据库/DBA/索引体系结构/非聚集索引 概述   非聚集索引与聚集索引具有相同的 B 树结构,它们之间的显著差别在于以下两点: 基础表的数据行不按非聚集键的顺序排序和存储. 非聚集索引的叶层是由索引页而不是由数据页组成. 既可以使用聚集索引来为表或视图定义非聚集索引,也可以根据堆来定义非聚集索引.非聚集索引中的每个索引行都包含非聚集键值和行定位符.此定位符指向聚集索引或堆中包含该键值的

SQL Server 优化器特性导致的内存授予相关BUG

原文:SQL Server 优化器特性导致的内存授予相关BUG 我们有时会遇到一些坑,要不填平,要不绕过.这里为大家介绍一个相关SQL Server优化器方面的特性导致内存授予的相关BUG,及相关解决方式,也顺便回答下邹建同学的相关疑问.   问题描述 一个简单的查询消耗了匪夷所思的内存.(邹建同学发现的) 原文链接 Code create table test_mem ( id int identity(1,1) primary key, itemid int not null, date d

SQLServer · 最佳实践 · SQL Server优化案例分享

title: SQLServer · 最佳实践 · SQL Server优化案例分享 author: 石沫 前端时间的视频分享,这里有同学文档总结下来: 请参考:https://yq.aliyun.com/articles/60119