最近有个培训,需要讲解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