关于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瓶颈：
www.2cto.com
一、重编译
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
www.2cto.com
解决办法：
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
www.2cto.com
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