文档章节

sql优化 性能快速定位

o
 osc_z1hvg4cu
发布于 2018/04/24 17:29
字数 2110
阅读 16
收藏 0

精选30+云产品,助力企业轻松上云!>>>

sql server sql性能快速定位

简介

    对于写出实现功能的SQL语句和既能实现功能又能保证性能的SQL语句的差别是巨大的。很多时候开发人员仅仅是把精力放在实现所需的功能上,而忽略了其所写代码的性能和对SQL Server实例所产生的影响(也就是IO,CPU,内存方面的消耗).这甚至有可能使整个SQL Server实例跪了。本文旨在提供一些简单的步骤来帮助你优化SQL语句。

    市面上已经有很多关于如何优化SQL Server性能的书籍和白皮书。所以本文并不打算达到那种深度和广度,而仅仅是为开发人员提供一个快速检测的列表来找到SQL语句中导致瓶颈产生的部分。

    在开始解决性能问题之前,合适的诊断工具是必须的。除去众所周知的SSMS和SQL Profiler,SQL Server 2008还带有众多DMV来提供关键信息。本篇文章中,我将使用SSMS和一些DMV来找到SQL的瓶颈

 

那么,我们从哪开始

    我的第一步是查看执行计划。这一步既可以通过SMSS也可以通过SQL Profiler实现,为了简便起见,我将在SMSS中获取执行计划。

    1) 检查你是否忽略掉了某些表的连接的条件,从而导致了笛卡尔积(Cross)连接(Join)。比如,在生产系统中有两个表,每个表中有1000行数据。这其中绝大多数数据并不需要返回,如果你在这两个表上应用了Cross Join,返回的结果将会是100万行的结果集!返回如此数量的数据包括将所有数据从物理存储介质中读取出来,因而占用了IO。然后这些数据将会被导入内存,也就是SQL Server的缓冲区。这会将缓冲区内的其它页Flush出去。

 

    2)查看你是否忽略了某些Where子句,缺少Where子句会导致返回额外不需要的行。这产生的影响和步骤一所产生的影响是一样的。

 

    3)查看统计信息是否是自动创建和自动更新的,你可以在数据库的属性里看到这些选项

    1

    在默认条件下创建一个新数据库,Auto Create Statistics和Auto Update Statistics选项是开启的,统计信息是用于帮助查询优化器生成最佳执行计划的。这份白皮书对于解释统计信息的重要性以及对于执行计划的作用解释的非常到位。上面那些设置可以通过右键数据库,选择属性,在“选项”中找到。

 

    4)检查统计信息是否已经过期,虽然统计信息是自动创建的,但是更新统计信息从而反映出数据的变化也同样重要。在一个大表中,有时候虽然Auto Update Statistics 选项已经开始,但统计信息依然无法反映出数据的分布情况。默认情况下,统计信息的更新是基于抽取表中的随机信息作为样本产生的。如果数据是按顺序存储的,那么很有可能数据样本并没有反映出表中的数据情况。因此,推荐在频繁更新的表中,统计信息使用Full Scan选项来定期更新。这种更新可以放到数据库闲时来做。

     DBCC SHOW_STATISTICS命令可以用于查看上次统计信息的更新时间,行数以及样本行数.在这个例子中,我们可以看到Person.Address表上的AK_Address_rowguid索引的有关信息:

USE AdventureWorks;
GO
DBCC SHOW_STATISTICS ("Person.Address", AK_Address_rowguid);
GO

 

    下面是输出结果,请注意Updated,Rows,Rows Sampled这三个列

2

 

   

如果你认为统计信息已经过期,则可以使用sp_updatestats这个存储过程来更新当前数据库中的所有统计信息:

 

    或者使用FULLSCAN选项,则关于表Person.Address上的所有统计信息将会被更新:

UPDATE STATISTICS Person.Address WITH FULLSCAN

 

    5)查看执行计划是否出现任何表或者索引的扫描(译者注:不是查找),在大多数情况下(这里假设统计信息是最新的),这意味着索引的缺失。下面几个DMV对于查找缺失索引很有帮助:

i) sys.dm_db_missing_index_details

ii) sys.dm_db_missing_index_group_stats

iii) sys.dm_db_missing_index_groups

    接下来的几个语句使用了上面的DMV,按照索引缺失对于性能的影响,展现出信息:

SELECT avg_total_user_cost,avg_user_impact,user_seeks, user_scans,
ID.equality_columns,ID.inequality_columns,ID.included_columns,ID.statement 
FROM sys.dm_db_missing_index_group_stats GS
LEFT OUTER JOIN sys.dm_db_missing_index_groups IG On (IG.index_group_handle = GS.group_handle)
LEFT OUTER JOIN sys.dm_db_missing_index_details ID On (ID.index_handle = IG.index_handle)
ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC

 

   你也可以使用数据引擎优化顾问来找出缺失的索引以及需要创建哪些索引来提高性能。

 

    6)查看是否有书签查找,同样,在执行计划中找到书签查找十分容易,书签查找并不能完全避免,但是使用覆盖索引可以大大减少书签查找。

 

    7)查看排序操作,如果在执行计划中排序操作占去了很大一部分百分比,我会考虑以下几种方案:

  •      按照所排序的列创建聚集索引,但这种方式一直存在争议。因为最佳实践是使用唯一列或者Int类型的列作为主键,然后让SQL Server在主键上创建聚集索引。但是在特定情况下使用排序列创建聚集索引也是可以的
  •     创建一个索引视图,在索引视图上按照排序列创建聚集索引
  •     创建一个排序列的非聚集索引,把其他需要返回的列INCLUDE进去

      在我的另一篇文章中,我将会详细阐述选择最佳方案的方法。

 

    8)查看加在表上的锁,如果所查的表由于一个DML语句导致上锁,则查询引擎需要花一些时间等待锁的释放。下面是一些解决锁问题的方法:

  •     让事务尽可能的短
  •     查看数据库隔离等级,降低隔离等级以增加并发
  •     在Select语句中使用表提示,比如READUNCOMMITTED 或 READPAST.虽然这两个表提示都会增加并发,但是ReadUnCommited可能会带来脏读的问题,而READPAST会只返回部分结果集

 

    9)查看是否有索引碎片,索引碎片可以使用sys.dm_db_index_physical_statsDMV轻松查看,如果索引碎片已经大于30%,则推荐索引重建.而索引碎片小于30%时,推荐使用索引整理。索引碎片因为使查询需要读取更多的列从而增加了IO,而更多的页意味着占用更多的缓冲区,因此还会形成内存压力。

    如下语句根据索引碎片的百分比查看所有索引:

Declare @db	SysName;
Set @db = '<DB NAME>';

SELECT CAST(OBJECT_NAME(S.Object_ID, DB_ID(@db)) AS VARCHAR(20)) AS 'Table Name',
 CAST(index_type_desc AS VARCHAR(20)) AS 'Index Type',
 I.Name As 'Index Name',
 avg_fragmentation_in_percent As 'Avg % Fragmentation',
 record_count As 'RecordCount',
 page_count As 'Pages Allocated',
 avg_page_space_used_in_percent As 'Avg % Page Space Used'
FROM sys.dm_db_index_physical_stats (DB_ID(@db),NULL,NULL,NULL,'DETAILED' ) S
LEFT OUTER JOIN sys.indexes I On (I.Object_ID = S.Object_ID and I.Index_ID = S.Index_ID)
AND S.INDEX_ID > 0
ORDER BY avg_fragmentation_in_percent DESC

 

   下面语句可以重建指定表的所有索引:

ALTER INDEX ALL ON <Table Name> REBUILD;

 

    下面语句可以重建指定索引:

ALTER INDEX <Index Name> ON <Table Name> REBUILD;

 

    当然,我们也可以整理索引,下面语句整理指定表上的所有索引:

ALTER INDEX ALL ON <Table Name> REORGANIZE;

 

    下面语句指定特定的索引进行整理:

ALTER INDEX <Index Name> ON <Table Name> REORGANIZE;

 

   在重建或整理完索引之后,重新运行上面的语句来查看索引碎片的情况。

 

总结

    上面的9个步骤并不是优化一个SQL语句必须的,尽管如此,你还是需要尽快找到是哪个步骤导致查询性能的瓶颈从而解决性能问题。就像文中开篇所说,性能的问题往往是由于更深层次的原因,比如CPU或内存压力,IO的瓶颈(这个列表会很长….),因此,更多的研究和阅读是解决性能问题所必须的。

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

原文链接:http://www.sqlservercentral.com/articles/Performance+Tuning/70647/

o
粉丝 0
博文 500
码字总数 0
作品 0
私信 提问
加载中
请先登录后再评论。
运维不再专业救火 不会PHP照样找出代码性能问题

作者: 凉白开 网站:www.ttlsa.com 身处互联网的SA(运维)们总感叹自己职业的苦逼,Why?我来告诉你:APP奔溃、网站打不开、网站502、搜索缓慢、应用卡顿通通找运维,运营、项目经理、老板等...

cloudwiseAPM
2015/12/30
140
1
企业上云如何优化性能?

应用系统上线运行后,随着系统数据量的不断增长、访问量的不断上升,系统的响应速度通常会越来越慢,尤其日常峰值情况下常不能满足业务需要,甚至出现应用服务中断的现象,给企业造成巨大的品...

嘉为科技
01/07
13
0
Mysql 慢查询日志

概述 数据库查询的快慢是项目性能的一大指标,对于数据库查询的优化,除了要优化 SQL,更重要的是要找到需要优化的 SQL。 MySQL 数据库提供了一个「慢查询日志」可以帮助我们记录查询时间超过...

JNG
2017/04/11
2
0
没有铁打的知识体系,何来成长的技术复利?

当今这个时代,我们可以通过官方文档、搜索引擎学习到各种技术知识,可以说,没有什么技术是无法通过网络获取到的。但遗憾的是,这些碎片化的知识点终归只是一个“点”,不是“线”,更没有形...

给你指明出路的
2017/10/23
0
0
【华为云技术分享】智能诊断和优化,华为云DAS服务云DBA平台让您无忧运维

摘要:随着时代的发展,传统的“人工”运维方式,已经逐渐跟不上企业业务发展的需要。如何更好的保证数据库系统的稳定性、安全性、完整性和高性能,实现运维工具化、产品化、自助化、自动化,...

华为云开发者社区
05/11
3
0

没有更多内容

加载失败,请刷新页面

加载更多

浅谈对python pandas中 inplace 参数的理解

这篇文章主要介绍了对python pandas中 inplace 参数的理解,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧 pandas 中 inplace 参数在很多函数中都会有,它的作用是:是否...

Linux就该这么学
30分钟前
20
0
C++ 从基本数据类型说起

前言 int 在32位和64位操作系统,都是四个字节长度。为了能编写一个在32位和64位操作系统都能稳定运行的程序,建议采用std::int32_t 或者std::int64_t指定数据类型。*与long随操作系统子长变...

osc_sxdofc9c
30分钟前
9
0
游戏音乐的作用以及起源

游戏音乐是由特殊的音乐、语言符号、美学符号组成,在电子游戏的发展下,游戏音乐越来越成熟,游戏音乐与美术相融合,能够带给玩家视觉与声音的感官冲击,形成游戏音乐所具有的独特的审美效果...

奇亿音乐
31分钟前
10
0
2020,最新Model的设计-APP重构之路

很多的app使用MVC设计模式来将“用户交互”与“数据和逻辑”分开,而model其中一个重要作用就是持久化。下文中设计的Model可能不是一个完美的,扩展性强的model范例,但在我需要重构的app中,...

osc_mfzkzkxi
31分钟前
4
0
面对职业瓶颈,iOS 开发人员应该如何突破?

我们经常看到 iOS 开发人员(各种能力水平都有)的一些问题,咨询有关专业和财务发展方面的建议。 这些问题有一个共同点:前面都会说“我现在遇到了职业困境”,然后会问一些诸如“我是否应该...

osc_gfpedeca
32分钟前
21
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部