[转载]MS SQL性能调优
转自 大材小用 http://www.cnblogs.com/slade/archive/2012/04/29/2476130.html
MS SQL性能调优之一（MS SQL性能调优初探）
（一）我的系统越来越慢
用户总抱怨系统越来越慢，报表出不来，下单很慢。高峰期间一切都慢下了，平常很快的操作也不行了。总之我的系统随着时间越来越慢，我们开始总把这种情况归结为数据太多了，硬件跟不上了。真的是这样吗？
（二）我们是否规范的管理了数据库？
（1）、我们是否有一个称职的DBA？
（2）、我们是否监控服务器的性能指标？
（3）、我们是否正确的使用并配置了服务器？
（4）、我们的程序设计是否合理？
。。。。。。。。很多的疑问。
（三）优化的方法论
（1）、分析服务器的整体性能。
（2）、分析阻塞和等待。
（3）、确定优化的方法。
（4）、修改程序设计架构。
（5）、索引管理、T-SQL调整等（DBA的细节优化应该放在最后一位）。
先简单地对性能管理管理开一个头，以后逐步添加该系统文章，敬请期待。
MS SQL性能调优之二（分析服务器的整体性能）
（一）是否存在一个服务器性能基线？
(1)、有了基线才有一个衡量的指标，否则一切计数器都是没有意思。否则可能我的系统本来就很慢，例如我就是一个OLAP系统，每天就花了三个小时产生一个报表。也不是问题。
(2)、有了基线才能确认我的服务器确实存在调整的必要了，例如今天发布新的代码后，性能明显偏离基线了，说明我的程序或者T-SQL代码可能却在缺陷。
（二）如何建立一个基线。
（1）、使用system perfmon收集性能计数器信息。
（2）、使用sys.dm_os_performance_counters DMV,可惜该DMV不包含一些OS级别的性能计数器。
（3）、根据前两部的信息确定一个合理的基线。
（三）几个重要计数器。
（1）、Memory：Pages/sec
这个计数器表示了每秒钟内存和磁盘之间交换的页面数，如果该值很高的话，可能预示你的服务器可能存在内存压力。可以考虑让SQL动态的分配内存，不要设置SQL最大使用内存，如果该服务器还运行其他消耗内存的程序可以考虑移除，或者增加很多的内存。一般该值推荐在0～20。
（2）、SQLServer:Buffer Manager：Buffer cache hit ratio
这个计数器表示SQLServer从缓存中而不是磁盘中获得数据的概率。在一个OLTP程序中为了较好响应时间，该计数器值应该大于99%。负责也可能预示你的服务器可能存在内存压力，解决办法同上。
（3）、PhysicalDisk ：Avg. Disk Queue Length
这个计数器表示磁盘队列长度，该值应该低于2，如果该计数器值很高，可能预示你的服务器存在IO的压力，应该增加IO子系统性能，配置更好的RAID,或者是更好的存储。
（4）System：Processor Queue Length
这个计数器表示CPU列长度，该值应该低于2，如果该计数器值很高，可能预示你的服务器存在CPU的压力，应该增加更多或者更强的CPU。或者减少服务器的负载。
（5）Processor：% Processor Time
这个计数器表示CPU的使用率，该值最好低于30%,不能高于80%,如果该值达到99%,可能服务器就已经僵死了，应该产生一个警报通知DBA介入。解决该问题可以Processor Queue Length解决方法一样。
MS SQL性能调优之三（T-SQL优化）
(一)T-SQL原则
1、不要在OLTP服务器做统计和报表运行，应该讲Group by等类似的统计语句放到单独的服务器运行(即OLTP和OLAP分开)，可以采用手工或者MS SQL复制将统计的数据分发到OLAP服务器。
2、T-SQL是否返回了过多的数据？客户端真的需要这么多数据吗？返回过多的数据意味很多的IO操作，频繁的执行类似语句对IO CPU均会产生压力。
3、索引是否符合SAGE。
4、是否将频繁执行语句封装成了储存过程？可以减少网络流量，减少执行计划的编译时间。
5、程序员是否执行大量的游标语句？一切关系型数据都是擅长集合运算。
6、客户端调用的SP中，是否SET NOCOUNT ON？不关闭该选项的话，服务器会发送消息给客户端，增加了网络流量。
7、事务还能更短吗？过长的事务意味持有锁的时间也越长，事务应该越短越好。
(二)优化示例(不断更新中)：
1、UNION和UNION ALL比较
View Code
--建立示例表 CREATE TABLE UNION_TEST1 ( ID INT ) go CREATE TABLE UNION_TEST2 ( ID INT ) go --插入示例数据 DECLARE @i INT SET @i=1 WHILE @i < 1000 BEGIN INSERT INTO UNION_TEST1 SELECT @i INSERT INTO UNION_TEST2 SELECT 10000+@i SET @i=@i + 1 END SELECT ID FROM UNION_TEST1 UNION SELECT ID FROM UNION_TEST2 SELECT ID FROM UNION_TEST1 UNION ALL SELECT ID FROM UNION_TEST2
如果两个联合的记录没有重复数据应该使用UNION ALL。
比较执行计划(发现明显消耗影响，这个例子大概相差5倍)
2、SAGE例子
View Code
--建立示例表 CREATE TABLE SAGE_test ( ID INT ) --建立索引 CREATE INDEX idx_id ON sage_test(ID) --插入示例数据 DECLARE @i INT SET @i=1 WHILE @i < 100000 BEGIN INSERT INTO SAGE_test SELECT @i SET @i=@i + 1 END SET statistics io ON SELECT ID FROM SAGE_test WHERE ID = 3 SELECT ID FROM SAGE_test WHERE Abs(ID) = 3
对应的统计信息，消耗差异明显。
表 'SAGE_test'。扫描计数 1，逻辑读取 2 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。 表 'SAGE_test'。扫描计数 1，逻辑读取 270 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
SQL SERVER性能调优之四（使用Profiler捕获慢查询）
（一）慢的标准是什么？
1、对于OLTP系统来说，用户等待3秒就失去耐心了，当然这是一个上限标准，系统的响应时间当然是越快越好。
2、对于OLAP系统来说，这个标准很难确定，我所在的公司，有些复杂的报表需要在一台强劲的服务器上运行一天，但是用户也可以接受。
3、所以我们应该关注OLTP系统的慢查询，不能让客户下订单需要10S以上才响应吧。
（二）捕获方法
1、首先在SSMS，工具菜单下打开Profiler。
2、输入你用户名密码登陆。
3、为你的跟踪取一个名字。
4、事件选择选择，选择以下两列即可，
Stored Procedures RPC:Completed TSQL SQL:BatchCompleted
5、点击列筛选器，为Duration设置一个过滤值。本例子设置为3000（即3S）。
6、点击运行。开始信息的收集。
（三）优化捕获方法
虽然可以采用以上方法收集慢查询，但是跟踪也会对服务器造成额外的开销。
所以推荐在后台收集。可以在Profiler界面选择文件-》导出-》编写跟踪定义的脚本-》用于SQL 2005-2008.
代码如下：
View Code
/****************************************************/
/* Created by: SQL Server 2008 Profiler */
/* Date: 2012/04/29 18:59:08 */
/****************************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 11, @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @on
exec sp_trace_setevent @TraceID, 12, 11, @on
exec sp_trace_setevent @TraceID, 12, 12, @on
exec sp_trace_setevent @TraceID, 12, 13, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - 201027c7-ca0b-4ae3-ad06-9be3ed6ce0aa'
set @bigintfilter = 3000000
exec sp_trace_setfilter @TraceID, 13, 0, 4, @bigintfilter
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
（四）分析慢查询
1、 对于SQL DBA来说可以总是习惯用SQL语句在解决问题。
可以用如下语句以结果集形式返回Trace信息。
select * from fn_trace_gettable(N'c:\MyTrace.trc',-1)
2、可以将结果插入一个表中，分析慢查询语句的执行频率，调整写法或者优化索引在逐个解决。
SQL SERVER性能调优之五（CPU性能分析）
（一）概述
（1）CPU对一台数据库服务器来说，是非常重要的资源。废话少说大家都懂的。
（2）如何监控CPU性能。
2.1 可以使用system perfmon收集Processor：% Processor Time性能计数器，可以参见另一篇文章
2.2使用SQL Server Performance Dashboard。
（二）什么原因会导致CPU性能问题？
（1）大量消耗CPU的运算，如哈希运算，排序运算。
（2）执行计划的编译和重编译。
（3）缺乏索引的大量IO操作也会引起额外的CPU消耗。
（4）并行操作。
等等。。。。
(三)具体手段
(1)几个重要的等待，先确定服务器级别的等待。
SELECT wait_type, Sum(signal_wait_time_ms) AS TotalSignalWaitTime FROM sys.dm_os_wait_stats GROUP BY wait_type ORDER BY 2 DESC |
CXPACKET等待:等待多个线程同步，会出现此等待。
SOS_SCHEDULER_YIELD：放弃CPU,并等待继续执行。
如果发现大量CXPACKET等待，可能由于并行造成，在OLTP系统，可以考虑降低并行度。
如果发现大量SOS_SCHEDULER_YIELD等待，可能说明你的CPU存在瓶颈。
（2）几个重要的计数器
sql statistics: sql compilations/sec 每秒的查询编译次数
sql statistics: sql recompilations/sec 每秒的查询重编译次数
如果发现以上两个性能计数器的值非常高，可以CPU性能问题有编译造成。可以采用存储过程封装T-SQL语句，参数化查询，紧急时可以开启数据强制参数化。
（3）语句级别处理
SELECT TOP 10 total_worker_time / execution_count AS [Avg CPU Time], (SELECT Substring(text, statement_start_offset / 2, ( CASE WHEN statement_end_offset = -1 THEN Len( CONVERT(NVARCHAR(max), text)) * 2 ELSE statement_end_offset END - statement_start_offset ) / 2 ) FROM sys.Dm_exec_sql_text(sql_handle)) AS query_text, * FROM sys.dm_exec_query_stats ORDER BY [Avg CPU Time] DESC |
select top 10 (total_logical_reads/execution_count) as avg_logical_reads, (total_logical_writes/execution_count) as avg_logical_writes, (total_physical_reads/execution_count) as avg_phys_reads, Execution_count, statement_start_offset as stmt_start_offset, sql_handle, plan_handle from sys.dm_exec_query_stats order by (total_logical_reads + total_logical_writes) Desc |
使用该查询可以查询CPU和IO消耗最高的10个语句，可以逐一分析，是否可以更改写法或者适当添加索引，降低CPU使用率（一般高CPU和高IO关联，所以可以想办法先降低IO，直观的处理，就是减少语句的逻辑读）。
（四）总结
CPU优化是一个长期的过程，需要定义将当前的性能计数器和基线对比，尽早的发现问题，CPU出现性能问题，有时候也不一定代表就是CPU出现瓶颈了，可能预示其他硬件出现故障了，或者索引被意外删除了，网站受到攻击等等，所以CPU监控至关重要。
浙公网安备 33010602011771号