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

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
简介: 原文:Sql Server优化之索引提示----我们为什么需要查询提示,Sql Server默认情况下优化策略选择的不足环境:   Sql Server2012 SP3企业版,Windows Server2008 标准版   问题由来: 最近在做DB优化的时候,发现一个存储过程有非常严重的...
原文: Sql Server优化之索引提示----我们为什么需要查询提示,Sql Server默认情况下优化策略选择的不足

环境:

  Sql Server2012 SP3企业版,Windows Server2008 标准版

 

问题由来:

最近在做DB优化的时候,发现一个存储过程有非常严重的性能问题,

由于整个SP整体逻辑是一个多表关联的复杂的查询,整体结构比较复杂的,通过的分析和尝试,

最后发现问题出在其中一个大表的查询上实现方式上,

因为这个大表上的意外的执行方式,导致其他表无法被驱动,其他表也是表扫描的方式参与join

导致后面整个表之间join以及查询编译出来一系列极其低效的执行效果(不合理的执行计划)

 

这里单独将这个大表的查询提取出来作分析

下面就是我说的大表的这一步在默认情况下的执行情况,

这个表当时数据量是3000W条,符合查询条件的数据在50W左右(一个月的数据量),

 

不加索引提示下的执行情况(测试之前都DBCC dropcleanbuffer 清理缓存):

 

因为考虑到结果集比较大,SSMS结果窗口加载数据耗时比较长

我将结果Insert到临时表的目的是减少SSMS加载数据的时间,更注重查询内部计算

 

如下是sql查询执行信息

 

执行计划情况

 

可以看到它走了一个跟查询字段无关的普通索引的index scan的执行计划,

 

 

 

加上索引提示的执行情况:

 

如果加一个查询列上的索引的查询提示,会发现性能上有一个比较明显的提升

当加上查询索引提示之后,2s中就完成了,比上面默认没有索引提示的方式提高了4倍

 

执行计划信息

 

 

这里就有一个问题,默认情况下为什么没有采用索引查找的方式,而采用了全表扫描的方式?

 如果没猜错的话,很可能有人跟我一样,是认为统计信息没有更新,索引碎片之类导致的

 这个查询非常简单,到底是不是索引统计信息,或者索引碎片之类的引起的呢?

 这个倒是好办,对这个表上的所有索引全部重建(rebuild),同时也会促使统计信息的更新。

 

然后继续测试,问题依旧!!!

 

基本上可以认为,外界条件没有额外干扰的情况下,Sql Server 始终没有选择一个执行速度较快的执行计划

之所以说Sql Server没有选择执行速度较快的执行计划,而不是说最优的,是因为这个执行计划是没办法直接去评判的,

如果你注意的话,会发现:

不加索引提示的时候:逻辑读是:589689,物理读是589723

加了索引提示的时候,逻辑读是:1992328,但是真正的物理读并不多,只有11229+2042

                逻辑读的增加是在内存中作计算的时候产生的

但是这并不影响后者执行时间更短,

因为后者还有一个内存授予(Memory Grant)一个211M的物理内存用来暂存中间结果集来以更高效的方式来执行

所以我前面说Sqlserver没有选择“执行速度较快”的执行计划,而不是说最优化的执行计划,

因为后者执行更快,但是耗费了更多的服务器资源,前者执行较慢,但是没有耗费很多系统资源。

 

 最优化的是一个很难用一棒子打死的方式去界定的,到底是以执行时间为评判,还是以资源消耗为评判?

我想sqlserver是以综合考量去评价的吧,出现这种情况,我只能推断:默认情况下,评估执行计划的时候,IO的系数在计算中的权重更大

 

如果服务器资源充足的情况下:

  肯定宁愿为其提供充足资源去让其更快地执行以相应应用程序的请求

如果服务器资源不够充足的情况下:

  这个SQl的运行需要这么多内存,

  可能就需要为了申请到(比第一种执行方式)足够多的内存而造成更长时间的等待(这里不细说,比如memory grants pending)

  此时,还不如让他慢慢执行,好歹还能跑出来结果。

 

这种究竟哪种方式好,哪种方式不好,很难有一个定论,一切都要根据具体情况来定,

数据库自身不太可能在任何情况下都作出最最明智的选择,这大概也就是各种关系数据库预留给用户一些查询提示的原因吧。

 

总结: 

  以上粗浅地根据一个遇到的实例案例,通过认为改变默认情况下的执行计划来观察对比sql的执行效率,在我们对数据库进行性能调优时提供一种可参考的方法

  也能够帮助我们认识Sql Server在选择执行计划时候的一些特点,以帮助我们更加有效地使用Sql Server数据库。

 

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
5天前
|
SQL 人工智能 算法
【SQL server】玩转SQL server数据库:第二章 关系数据库
【SQL server】玩转SQL server数据库:第二章 关系数据库
41 10
|
17天前
|
SQL 存储 关系型数据库
一文搞懂SQL优化——如何高效添加数据
**SQL优化关键点:** 1. **批量插入**提高效率,一次性建议不超过500条。 2. **手动事务**减少开销,多条插入语句用一个事务。 3. **主键顺序插入**避免页分裂,提升性能。 4. **使用`LOAD DATA INFILE`**大批量导入快速。 5. **避免主键乱序**,减少不必要的磁盘操作。 6. **选择合适主键类型**,避免UUID或长主键导致的性能问题。 7. **避免主键修改**,保持索引稳定。 这些技巧能优化数据库操作,提升系统性能。
213 4
一文搞懂SQL优化——如何高效添加数据
|
25天前
|
SQL
sql server链接查询
sql server链接查询
17 1
|
25天前
|
SQL
sql server简单查询
sql server简单查询
12 1
|
15天前
|
SQL
启动mysq异常The server quit without updating PID file [FAILED]sql/data/***.pi根本解决方案
启动mysq异常The server quit without updating PID file [FAILED]sql/data/***.pi根本解决方案
15 0
|
5天前
|
SQL 算法 数据库
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(二)数据查询
【SQL server】玩转SQL server数据库:第三章 关系数据库标准语言SQL(二)数据查询
56 6
|
5天前
|
SQL 存储 数据挖掘
数据库数据恢复—RAID5上层Sql Server数据库数据恢复案例
服务器数据恢复环境: 一台安装windows server操作系统的服务器。一组由8块硬盘组建的RAID5,划分LUN供这台服务器使用。 在windows服务器内装有SqlServer数据库。存储空间LUN划分了两个逻辑分区。 服务器故障&初检: 由于未知原因,Sql Server数据库文件丢失,丢失数据涉及到3个库,表的数量有3000左右。数据库文件丢失原因还没有查清楚,也不能确定数据存储位置。 数据库文件丢失后服务器仍处于开机状态,所幸没有大量数据写入。 将raid5中所有磁盘编号后取出,经过硬件工程师检测,没有发现明显的硬件故障。以只读方式将所有磁盘进行扇区级的全盘镜像,镜像完成后将所
数据库数据恢复—RAID5上层Sql Server数据库数据恢复案例
|
9天前
|
SQL 安全 Java
SQL server 2017安装教程
SQL server 2017安装教程
13 1
|
18天前
|
SQL 关系型数据库 MySQL
【MySQL技术之旅】(7)总结和盘点优化方案系列之常用SQL的优化
【MySQL技术之旅】(7)总结和盘点优化方案系列之常用SQL的优化
36 1
|
20天前
|
SQL 索引
SQL怎么优化
SQL怎么优化
26 2