【转载】SQL2005常见性能问题排错代码

O?o Q2V7hu0强制重新编译的演示代码
{lr2wU2Aq2fz0 51Testing软件测试网-H4zbcky
USE AdventureWorks51Testing软件测试网N`y5qU;Vz
GO51Testing软件测试网q(\p*B3O3X
Z
51Testing软件测试网+dY6_s Z6aK
--在SQL Profiler中监视Stored Procedures类别中的SP:Recompile事件51Testing软件测试网B7rC'TN] j
--注意SQL Profiler中没有出现SP:Recompile事件
o:U j:tTaP0--SQL Server在创建存储过程对象时不会编译,延迟编译
{mUO5v:}.W0CREATE PROCEDURE dbo.DemoProc151Testing软件测试网;m
B5c*L'e e5l
AS
z D9c|g"m I-EY0CREATE TABLE #temp (IDA int, IDB int)51Testing软件测试网9{'ny!O I'_Jp9[*i
SELECT * FROM #temp51Testing软件测试网9tR4Xk5~'p T
GO

--运行两次,注意
,D+e:n{U-af0W2S+DN0--第一次SQL Profiler中出现了SP:Recompile事件51Testing软件测试网$eiL)J'G+x
--第二次SQL Profiler中没有出现SP:Recompile事件51Testing软件测试网^0Ufe%KC,H
EXEC dbo.DemoProc1

--注意每次运行都出现SP:Recompile事件51Testing软件测试网?9ixJ0D
EXEC dbo.DemoProc1 WITH RECOMPILE

DBCC freeproccache--清除过程缓存51Testing软件测试网5`f y:nH9n*At

6T,M\Z8Cw
NB"X0--重新启动SQL Profiler
0hUyC}0CREATE PROCEDURE dbo.DemoProc251Testing软件测试网1yYF[:s!G9Rf
WITH RECOMPILE51Testing软件测试网q ^4{i_oyub
AS51Testing软件测试网Wb$uG^V3dN4d#c
CREATE TABLE #temp (IDA int, IDB int)
'yII7wM6n q9}-B'p0SELECT * FROM #temp
~9];ZjB1s0GO

`$Fm5\D(Lm+N.P0--注意每次运行SQL Profiler都出现SP:Recompile事件
!NR,??yF.]0EXEC dbo.DemoProc2

o$bXA*\A*g0
i1q-d7nir0`0
GV xT'LGGE/[Z0CPU负载统计的演示代码
c foR4k
lidr0 51Testing软件测试网F+B*M0?[\
--运行以下脚本,复制输出结果51Testing软件测试网ahd6J9KhrT
--然后启动多个运行时间较长的脚本51Testing软件测试网 F |M+F,B)v]^ j
--再次运行以下脚本,复制输出结果51Testing软件测试网3Da-U;Luf
--比较输出结果间的差异,主要关注runnable_tasks_count
'S#kS6n;zY+TKV0SELECT scheduler_id, cpu_id,
"Ee3L;tQ
\h0    parent_node_id, current_tasks_count,51Testing软件测试网3}sa I8I/?x
    runnable_tasks_count, current_workers_count,
y/yNPQNk+p7[W0    active_workers_count, work_queue_count51Testing软件测试网4aH2}U`n_2O"J)GTKm
 FROM sys.dm_os_schedulers

--统计查询优化器的相关信息51Testing软件测试网
Z [\:XKoW1|V9D
SELECT *
G#kU"TA r Oo0FROM sys.dm_exec_query_optimizer_info
}WOk'mY0WHERE counter = 'optimizations' OR counter = 'elapsed time'51Testing软件测试网)LAo,xn6h'R

--查找Query Plan的常规统计信息
4U#`/l"nF0SELECT plan_generation_num, creation_time,
4jlfnTX0 last_execution_time, execution_count,51Testing软件测试网+C&s-?"|'~Js2N
 total_worker_time, total_physical_reads,51Testing软件测试网T9G$hjA
 total_logical_reads, total_logical_writes,51Testing软件测试网4S\;l sK
 total_elapsed_time,51Testing软件测试网yUl3S/o2C.C
    SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,51Testing软件测试网1tH1R9oD$i/k
    ((CASE statement_end_offset51Testing软件测试网T.L KN(wv3Kd
        WHEN -1 THEN DATALENGTH(st.text)
u V_^
} {OV0        ELSE qs.statement_end_offset END
]p0Q,Wz(u0            - qs.statement_start_offset)/2) + 1) as statement_text
-H N8q&Pi0FROM sys.dm_exec_query_stats as qs
Cv _N'F:E0CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st

--查找Query Plan的CPU相关统计
1p L&Bx3I q D~E0--总计耗费CPU时间最长的查询计划
&xB'f,LE0SELECT TOP 5 total_worker_time, last_worker_time,51Testing软件测试网|H Be6NFf+L
 max_worker_time, min_worker_time,51Testing软件测试网7bW0X?8@5j0G
    SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
)T(h'dl%D2z8wh*Vz(A0q0    ((CASE statement_end_offset51Testing软件测试网 X4p9w1`?D
        WHEN -1 THEN DATALENGTH(st.text)
uGt U0W;@B+l(vFb0        ELSE qs.statement_end_offset END
0Fh:?\8o*H ?%bY2W0~0            - qs.statement_start_offset)/2) + 1) as statement_text51Testing软件测试网XI!s1o e#FKB*N E
FROM sys.dm_exec_query_stats as qs51Testing软件测试网
v(?MS%bV5r1P:TA
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
zG"S)n6{
Of0ORDER BY total_worker_time DESC51Testing软件测试网7^ lJ4Lf"W

Mk+TdL['Ro4m0U,|0--单次执行耗费CPU时间最长的查询计划51Testing软件测试网-cx%g?*\A+W1l
SELECT TOP 5 total_worker_time, last_worker_time,51Testing软件测试网Xy-G0d/| hS
 max_worker_time, min_worker_time,51Testing软件测试网 _ W @.rJ[3h
    SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,51Testing软件测试网.U4AW,TXc{ g
    ((CASE statement_end_offset
Nmv2W-m(o:`m;jn0        WHEN -1 THEN DATALENGTH(st.text)51Testing软件测试网)V cV5AG)^o
        ELSE qs.statement_end_offset END51Testing软件测试网Q/R.ng6e.?
            - qs.statement_start_offset)/2) + 1) as statement_text51Testing软件测试网(D
e)BL1qM3r_Ee
FROM sys.dm_exec_query_stats as qs51Testing软件测试网4E;j#^$O)L9ZjhIi
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st51Testing软件测试网W4U1Z|!x/iv_p~
ORDER BY max_worker_time DESC51Testing软件测试网0P#RyK.OtT

--利用次数最多的查询计划51Testing软件测试网dh5W W*Jdb6P
SELECT TOP 5 creation_time, last_execution_time,

T1k9q&Zo!}:v.u+q0    execution_count,
Ww$? d*P6bA+yf
Z,i0    SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
Nus ^!Glz1~ q0    ((CASE statement_end_offset

B%[J8z4L _0        WHEN -1 THEN DATALENGTH(st.text)
G_[1M3UId_~:V0        ELSE qs.statement_end_offset END51Testing软件测试网:}bk9VY W'T)i*~
            - qs.statement_start_offset)/2) + 1) as statement_text
,N"G$j+a`c0FROM sys.dm_exec_query_stats as qs51Testing软件测试网8X/F XSS;Kj
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
@3X"\%F6ke0ORDER BY execution_count DESC51Testing软件测试网7f/H*E!?)fN5|pt `
GO
%o]-[ suz0
6xT d*f5n$K0
6G"S(? ].H!~Q0
9J M/W5Q%HrP0内存负载统计的演示代码
"S6kZ}9J dS/x0 51Testing软件测试网N Q:h"SFW9[!C![#cg
--显示服务器的内存配置
#w+^%mW0e#Os`0--开启高级配置51Testing软件测试网^-IrO,L"U
l6ml6{r
sp_configure 'show advanced options', '1'
:@,j5ckaPk"xK0RECONFIGURE
*^Hn4oBhN0--显示各项内存相关运行值51Testing软件测试网rpRJD3J&s eSR
sp_configure 'awe_enabled'
y)GmJ2D9F1k@g/{0sp_configure 'min server memory'
2Cxw-f"wy#}nM0sp_configure 'max server memory'
.m(I!~!D5qAk4l0sp_configure 'min memory per query'
N_SM R2?"i_0sp_configure 'query wait'51Testing软件测试网$Ib!Jqjf
U
--关闭高级配置51Testing软件测试网2M4W f-HW
WZ9|)m~$u
sp_configure 'show advanced options', '0'
UHeqyZ_0RECONFIGURE51Testing软件测试网0R.{(B$eT ~

--显示系统相关信息51Testing软件测试网 a"df&k&aY
select
4U6B(Uk2J_$SxFK2X\,V0 cpu_count,
a%x W`*[!?ei0 hyperthread_ratio,
;Fe}4\'afP4o(d0 scheduler_count,
@K(N/d*cI7~k&`Xq0 physical_memory_in_bytes / 1024 / 1024 as physical_memory_mb,
%oCs$Ru2kUN0 virtual_memory_in_bytes / 1024 / 1024 as virtual_memory_mb,
[/yh ^6\%c0 bpool_committed * 8 / 1024 as bpool_committed_mb,
)c$GO(z#W'A }\%P5J&j;@0 bpool_commit_target * 8 / 1024 as bpool_target_mb,51Testing软件测试网7{$wP%V J&d
 bpool_visible * 8 / 1024 as bpool_visible_mb51Testing软件测试网 o~&lU5}sG
from sys.dm_os_sys_info

?*_ x+^7~0--显示SQL Server的内存分配情况
&^6@7UF {r@0DBCC MEMORYSTATUS51Testing软件测试网%lSk;ve*d(v j}

--显示各种对象占用内存的数量
o o.Vgoyu0SELECT type, SUM (pages_allocated_count * page_size_in_bytes) as 'Bytes Used'51Testing软件测试网0{9_W\5\!`
FROM sys.dm_os_memory_objects51Testing软件测试网Xv6QM}5R
GROUP BY type
9OPfi s6Sd Q0ORDER BY 2 DESC;
JaS x4M;RO0GO51Testing软件测试网@\i3|fE&EPDGs

| G
uA{l0--由多页分配器分配的内存总量
}+O#G;cfj4J2q/b0select sum(multi_pages_kb) / 1024.00 AS multi_pages_mb
${J b&x;To5wS-Eu0from sys.dm_os_memory_clerks51Testing软件测试网D\+U
gyO&k'@D

1@2tp#AY,e.oWp0--统计各种类型Memory_Clerk由多页分配器分配的内存总量
$V4VLI]5JZ| Oe0select type, sum(multi_pages_kb) / 1024.00 AS multi_pages_mb
4ab,t0@ |4X(]8PH0from sys.dm_os_memory_clerks 
.Ua)o/JF$@O;B0where multi_pages_kb != 0 
M!xc2TD~0group by type
z B1KSg3|xz4a0order by 2 desc51Testing软件测试网e&a4X
Yqr6e

,Il#|p,q5i+N0|%O0--如果rounds_count和remove_rounds_count不断增长，代表内存面临压力51Testing软件测试网3I(]:n!c1};|;Qj
select *
_})vL8@@1W2_0D;{(W0from 
U
~'ZaX;X!Z0    sys.dm_os_memory_cache_clock_hands
o0i,sKSz(J t*M0where 
/K5f+|aS(|$W^0    rounds_count > 0
!Jl8[7_mv"K;TrV*VW0    and removed_all_rounds_count > 0

--各种由于I/O Latch申请而导致等待的信息51Testing软件测试网)]w+g&Z7CL;h/a
select wait_type, waiting_tasks_count, wait_time_ms,51Testing软件测试网?wYEOdx
signal_wait_time_ms, wait_time_ms / waiting_tasks_count AS 'avg_task_wait_time'51Testing软件测试网.h@'[ v{q"OO.cj#p
from sys.dm_os_wait_stats 51Testing软件测试网GY]8A`5wF,z
where wait_type like 'PAGEIOLATCH%'  and waiting_tasks_count > 051Testing软件测试网l)dZ;L\sq
order by wait_type

({,Q|.a x:}S0--查看有挂起IO的数据库或文件51Testing软件测试网6[8d:z Fl(f
--运行Tough Taks 01.sql和Tough Tasks 02.sql51Testing软件测试网s ?ju#azAdK
select database_id, file_id, io_stall, scheduler_address51Testing软件测试网&m m0V2Q!|m!WJn}
from sys.dm_io_virtual_file_stats(NULL, NULL) t1,51Testing软件测试网YKEKl(a
?6@
     sys.dm_io_pending_io_requests as t2
l"V!vc0itRl~0where t1.file_handle = t2.io_handle

W/cwp(B-Dj(|@+?0--寻找IO最频繁的5个查询
1_!HJyRp
H0--其中的Query Plan可以到处为sqlplan文件
p9K;b%zvhSc0--USE AdventureWorksDW
g$e-L%v0ilB0--SELECT * INOT dbo.TestProduct FROM dbo.DimProduct
1Nk2f+~
Q @A-s2k0--DELETE FROM dbo.TestProduct51Testing软件测试网T%d9RZF ]A
select top 5 (total_logical_reads/execution_count) as avg_logical_reads,51Testing软件测试网iK4f0^,]6VO
                   (total_logical_writes/execution_count) as avg_logical_writes,
RL,J t |Y@,S/y0      (total_physical_reads/execution_count) as avg_physical_reads,
S-xk!L1z RO;d0      Execution_count, statement_start_offset, p.query_plan, q.text
%V!pU4[P4bXMt0from sys.dm_exec_query_stats
.sTyt'v0 cross apply sys.dm_exec_query_plan(plan_handle) p51Testing软件测试网"G1TdbQ+l
 cross apply sys.dm_exec_sql_text(plan_handle) as q51Testing软件测试网"un0I B'nd
order by (total_logical_reads + total_logical_writes)/execution_count Desc

--Query Option对执行计划及系统负载的影响51Testing软件测试网(p#a*]2T8ZT(e4Wx
SET STATISTICS IO ON
r;z;}3Je(^d0SET STATISTICS TIME ON

--注意服务器统计输出
AAF$PC {m3U.i0DBCC DROPCLEANBUFFERS           --清除相关缓存51Testing软件测试网,S*OBcQY]8Mb,E
DBCC FREESYSTEMCACHE ('ALL')   --清除相关缓存
b |Q/I,N0SELECT c.Title, c.FirstName, c.MiddleName, c.LastName,51Testing软件测试网+Qf2XAi:j L0l$WVM
 c.Suffix, e.Title AS JobTitle
*pz2l n)~0FROM HumanResources.Employee AS e INNER JOIN
N,Ojr'Z1O.D QN_$Q0 Person.Contact AS c ON c.ContactID = e.ContactID51Testing软件测试网o+_|a3p

--注意服务器统计输出
3Pz/F5i4\%ubr0DBCC DROPCLEANBUFFERS           --清除相关缓存
"@#c C6l.]]0DBCC FREESYSTEMCACHE ('ALL')   --清除相关缓存
uTa%u A'u#E0SELECT c.Title, c.FirstName, c.MiddleName, c.LastName,51Testing软件测试网7K)ymYpC}j
 c.Suffix, e.Title AS JobTitle51Testing软件测试网3o%]clNq
FROM HumanResources.Employee AS e INNER JOIN51Testing软件测试网z+P0A5`&V1thMc
 Person.Contact AS c ON c.ContactID = e.ContactID
R5eb\vZ5u\0 OPTION (MERGE JOIN)51Testing软件测试网|K7Vc[+x%}@/j
51Testing软件测试网!X VZ7i D

o2Jewt(c0
2ooO@p)~+M^0Tempdb负载统计的演示代码
5DGN1| r'Vz051Testing软件测试网"gD+IC[.iG
--统计Tempdb各部分的空间消耗
;iy8k7qk(RMB9OKG0select
{Bd0jKC1f0    sum(user_object_reserved_page_count)*8 as user_objects_kb,51Testing软件测试网$C,GQJz3S.Q5e#R ~W
    sum(internal_object_reserved_page_count)*8 as internal_objects_kb,51Testing软件测试网p9VpE/kZ)g#T_%M[ yE
    sum(version_store_reserved_page_count)*8  as version_store_kb,
B,Iod*Z%X"uB0    sum(unallocated_extent_page_count)*8 as freespace_kb51Testing软件测试网 e ~)K$C7_n
from sys.dm_db_file_space_usage51Testing软件测试网La6F1c+[4n#Q+B
where database_id = 251Testing软件测试网S4{ N.p:]#P

--统计Tempdb中用户对象消耗的空间51Testing软件测试网
dc;EY*IB/Fky
--由于演示系统空闲，在另一会话中创建一个全局临时表51Testing软件测试网1^ PfFU1H
use tempdb
%l5L v&]#c0declare userobj_cursor cursor for 51Testing软件测试网%h:qgv8d8Y/C:G7f1Y
select 
S*sQP@'N5F0     sys.schemas.name + '.' + sys.objects.name 51Testing软件测试网Y.`3d4I2uK9u4] eq3{(@
from sys.objects, sys.schemas51Testing软件测试网\WX
}2O ~
Y.K
where object_id > 100 and 51Testing软件测试网-WC7El*b
MO,}Lz
      type_desc = 'USER_TABLE'and 51Testing软件测试网S;L"N:J`C
      sys.objects.schema_id = sys.schemas.schema_id51Testing软件测试网N6N QxX%kn0bD
go51Testing软件测试网g/o:D.j4JB
 
EW(nZ WF| y:I0open userobj_cursor51Testing软件测试网3SC([A7G$_ dW@
go
?#m]J$o0 51Testing软件测试网 ^+}!l@!Jk[
declare @name varchar(256)
$^ O~uc5sE0fetch userobj_cursor into @name51Testing软件测试网r4qH+wY,mX
while (@@FETCH_STATUS = 0) 51Testing软件测试网4e@t]m
begin51Testing软件测试网7~b1C8F;WS0k
    exec sp_spaceused @objname = @name51Testing软件测试网VB-H,o3a(J3D
        fetch userobj_cursor into @name    
p^ Rg)Hv\0end51Testing软件测试网_j@$R^baf0f
close userobj_cursor51Testing软件测试网#L1}h"GV
deallocate userobj_cursor51Testing软件测试网AEG-T
rU*co&F-T1A

&DK_(]S2f/M i3v0--统计每个会话创建的内部对象51Testing软件测试网 `1g p[!MJ
select 51Testing软件测试网7NV`2Gz1[+h
    session_id, 
~x(|]2O;T7pA'`0    internal_objects_alloc_page_count, 
2K3B e ~N?YC0    internal_objects_dealloc_page_count51Testing软件测试网vG'j^[7Q!~
from sys.dm_db_session_space_usage

n'x1nr|R0order by internal_objects_alloc_page_count DESC 
S7@2rn{~ ?051Testing软件测试网rI_:h\ Q3[&X E6E