关于SQL Server 监控与优化

最近有个培训，需要讲解SQL Server优化，做些储备。

 

首先做几个名词的说明：
执行计划（Plan）：sql 编译后的代码段，并不是二进制代码，而是针对SQL的执行代码，叫做plan。一个过程可能由于参数和分支执行不同，sql会编译成多个plan。
Perfmon：Server系统提供的performancemonitor，性能分析工具
DMVs：SQL Server提供的用于分析sql性能的一些系统视图
DBCC：SQL Server的一种控制台命令，对于数据库进行物理或者逻辑处理。

SQL server 性能瓶颈一般可以分为三类：
1. CPU瓶颈
2. 内存瓶颈
3. IO读写瓶颈

下面我们来分别说明一下。

产生cpu瓶颈：

一、重编译 
              exec('sql'); 
              withrecompiles; 
              临时表变化；
       处理：Perfmon counters
              Batch　Requests/sec 每秒执行批处理数
              SQLcompilations/sec 每秒编译sql语句数 参数不同会生成不同plan被系统catch 正常使用


一段时期后应当不应该太多
              SQLRecompilations/sec
              Rationo recompile to batch requests should be very low
             
查询重编译语句
select top 25
       SQLText.text,sql_handle,plan_generation_num,
       execution_count,dbid,objectid
from sys.dm_exec_query_stats a
       crosapply sys.dm_exec_sql_text(sql_handle)as SQLText
where plan_generation_num>1
order by plan_generation_num desc

查询系统中最慢的50条语句
select top 50 
       sum(qs.total_worker_time)as TotalCpuTime,
       sum(qs.execution_count)as TotalExecutionCount,
       count(*)as NumberOfStatements,
       qs.plan_handle
from sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time) desc


查看编译花费时间
select * from sys.dm_exec_query_optimizer_info

解决办法：
       1. 尽量避免在存储过程中使用set语句设置数据库状态（会引起重编译）
       2. 使用临时表时，使用表变量或者使用keep plan
       3. 尽量避免使用select * from tabel，如果schema变化会引起重编译
       4. 不要把DML和DDL写在一起进行交互。会引起schema变化，引起重编译
       5. 尽量避免使用if 每一个条件都会产生一个执行计划
      
二、查询
1.连接查询（需要根据两个表数据量不同具体调试）

查看那些查询最耗费时间
Find queries use most cumulative cpu through
       dm_exec_query_stats
Look for cpu intensive operators through
       dm_exec_cached_plans


三、并行处理

Perfmon：Process-%Processor Time -SqlServr.exe

DMVs:
Find query plans that may run in parallel
select p.*,q.*,cp.plan_handle
from sys.dm_exec_cached_plans cp
       crossapply sys.dm_exec_query_plan(cp.plan_handle) p
       crossapply sys.dm_exec_sql_text(cp.plan_handle)as q
where
       cp.cacheobjtype='CompiledPlan' and
       p.query_plan.value('declarenamespace 


p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";max(//p:RelOp/@Parallel)','float')>0


Only parallel query could use more cpu time thanthe elapsed time
select qs.sql_handle,qs.statement_start_offset,
       qs.statement_end_offset,q.dbid,q.objectid,q.number,
       q.encrypted,q.text
from sys.dm_exec_query_stats qs
       crossapply sys.dm_exec_sql_text(qs.plan_handle) as q
where qs.total_worker_time >qs.total_elapsed_time


SQL Traces:
Showplans that have Parallelism operators
       selectEventClass,StmtText
       from::fn_trace_gettable('c:\temp\high_cpu_trace.trc',default)
       whereStmtText like '%Parallelism%'
Parallel query use more cpu time than theelapsed time
       selecteventClass,StmtText
       from::fn_trace_gettable('c:\temp\high_cpu_trace.trc',default)
       whereeventclass in(10,12)
              --Rpc:Completed,SQL:BatchCompleted
       andcpu>duration/1000
              --Cpu is in milliseconds,duration in microseconds


处理：
       1. 增加CPU个数
       2. 优化查询语句
       3. 表值函数和CLR函数比较耗费cpu适当优化

内存优化

检查内存压力来自内部还是外部方法：
使用任务管理工具 在性能选项卡下，“AvailablePhysical Memory”<50M 可用内存小于50M
检查页文件Page File size
       -页文件超过物理内存2倍
       -使用任务管理工具 在性能选项卡下commit change 不应该大于物理内存
查看其它应用程序是否占用了过多内存
       -除去SQL Server之外的应用程序是否占用大量内存，例如IE，抢占sql内存


确认内部内存压力：


dbcc memorystatus


查看内存分配情况
DMVs
select type,sum(multi_pages_kb)
from sys.dm_os_memory_clerks
where multi_pages_kb !=0
group by type


排错流程：
1. 你的服务器是面临外部内存压力还是内部内存压力
       外部压力：调节SQLserver 对内存的使用量，设置sql属性，扩大使用内存，杀掉外部进程
       内部压力：DBCC或者DMVs具体解决。
       错误701：内存不足，外部压力，扩大内存或者减少应用
       错误802：bufferpool没有内存，如果释放buffer pool，则所有plan都没了
       错误8645：执行过程中等待内存分配超时。使用很多临时表，大量并发。找到问题查询

2. 收集性能数据SQL Server：BufferManager，SQL Server:Memory Manager
3. 验证配置参数（sp_configure)
       'minmemory per query','min/max server memory',
       'aweenabled'-- 大内存支持 超过4g需要使用,'Lock pages in memory'
4. DBCC Memorystatus

IO瓶颈

检查
1 perfmon
       磁盘队列 avg.disk queue length>2 说明有等待
       平均读写 avg.disk sec/read?0.12 , avg.disksec/write >0.12
       磁盘时间 %Disk time>50%
       avg.diskreads/sec>85%,avg.disk writes/sec>85%
2 使用RAID
Raid 0-- I/Os per disk=(reads+writes)/number ofdisks 性能最大提升
Raid 1-- I/Os per disk = [reads+(2*writes)]/2
Raid 5-- I/Os per disk=[reads+(4*writes)]/number of disks 读性能提示，写性能不如raid0
Raid 10--I/Os per disk=[reads+(2*writes)]/number of disks


3 DMVs
-- Physical IO wait when reading and writingbuffer pages
selectwait_type,waiting_tasks_count,wait_time_ms 
from sys.dm_os_wait_stats
where wait_type like 'PAGEIOLATCH%'
order by wait_type
--不同的锁等待的时间


--pending IO request
selectdatabase_id,file_id,io_stall,io_pending_ms_ticks, scheduler_address
from sys.dm_io_virtual_file_stats(null,null)t1,
       sys.dm_io_pending_io_requestsas t2
where t1.file_handle=t2.io_handle
-- 如果有数据，说明iO有问题


解决：
通过磁盘索引向导，优化索引，减少磁盘扫描


找到最浪费IO性能的查询
select top 5
(total_logical_reads/execution_count) asavg_logical_reads,
(total_logical_writes/execution_count)asavg_logical_writes,
(total_physical_reads/execution_count)asavg_physical_reads,
execution_count,
statement_start_offset,
sql_handle,
plan_handle,
SQLText.Text
from sys.dm_exec_query_stats
cross apply sys.dm_exec_sql_text(sql_handle) asSQLText
order by(total_logical_reads+total_logical_writes)/execution_count desc