sql优化 性能快速定位

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/

posted @ 2018-04-24 17:29  郭大侠1  阅读(1111)  评论(0)  编辑  收藏  举报