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

强制重新编译的演示代码51Testing软件测试网0_P/df%F&P$Lq
 51Testing软件测试网{trx{n^IB
USE AdventureWorks
IsN{2d)w @ D4h}9u0GO51Testing软件测试网*PK6AaF8qXG2_


f7j~cO~)\E3Y0--在SQL Profiler中监视Stored Procedures类别中的SP:Recompile事件51Testing软件测试网5n:uf!?9q$kw
--注意SQL Profiler中没有出现SP:Recompile事件
WL9I0Y'}+A&W_0--SQL Server在创建存储过程对象时不会编译,延迟编译51Testing软件测试网u dY7NxM
CREATE PROCEDURE dbo.DemoProc1
:t@
xt$J.O {_a!S8g0AS51Testing软件测试网7}m8[Ua1]j"D
CREATE TABLE #temp (IDA int, IDB int)
8GrjG3k)F?u:U0SELECT * FROM #temp
$K0lp e,r|
N8v0GO51Testing软件测试网9u8\ G*Og%bg

--运行两次,注意
xg&@Ar_i-^ K `0--第一次SQL Profiler中出现了SP:Recompile事件
%M;bX3|&tNB#Fy;x0--第二次SQL Profiler中没有出现SP:Recompile事件51Testing软件测试网ux B4[h
EXEC dbo.DemoProc151Testing软件测试网j+l,UFh

--注意每次运行都出现SP:Recompile事件
K9@$Qa)mh0EXEC dbo.DemoProc1 WITH RECOMPILE51Testing软件测试网.b7D{daC-R

DBCC freeproccache--清除过程缓存

--重新启动SQL Profiler51Testing软件测试网;vr`%]?GW:hM%Go
CREATE PROCEDURE dbo.DemoProc2
lI:A%hkI!IjMKf0WITH RECOMPILE51Testing软件测试网._\-o-t Z1yix6|+|H
AS51Testing软件测试网U){2\7ua"\'oq
CREATE TABLE #temp (IDA int, IDB int)
k'Dxm`$o/R0SELECT * FROM #temp51Testing软件测试网DN4I+v2u
GO

Y#y;qxjO0--注意每次运行SQL Profiler都出现SP:Recompile事件51Testing软件测试网1X-jKH7i.|n)W
EXEC dbo.DemoProc251Testing软件测试网*V6n-R&R8o+aUa R1_N

P3U}CH.Fa0
D6Gu4M;Bj$rY051Testing软件测试网C-|4[9UUB
CPU负载统计的演示代码
9`2ZWc1vV^0 
J4gc8jsb2y^0--运行以下脚本,复制输出结果
Q-xlP~0--然后启动多个运行时间较长的脚本51Testing软件测试网,H*C:[6?6eY
--再次运行以下脚本,复制输出结果51Testing软件测试网6?jx
`4}9B&G
--比较输出结果间的差异,主要关注runnable_tasks_count51Testing软件测试网9Kjk.J Pv
SELECT scheduler_id, cpu_id,51Testing软件测试网 [ TC8ni]c9hq
    parent_node_id, current_tasks_count,51Testing软件测试网(oj+f(PZw
    runnable_tasks_count, current_workers_count,51Testing软件测试网,A+r{+bal:Y`4OG
    active_workers_count, work_queue_count51Testing软件测试网8yF!N[*?0}G5F
 FROM sys.dm_os_schedulers51Testing软件测试网+recq.J2|3G.lf

/u/a)l-@!h0k@ _Dn9W0--统计查询优化器的相关信息51Testing软件测试网"Ls9qgO,u@ S)p
SELECT *
3B4d%et#Q.S0E)DO&\vg0FROM sys.dm_exec_query_optimizer_info51Testing软件测试网:cM*S@/OO~
WHERE counter = 'optimizations' OR counter = 'elapsed time'

--查找Query Plan的常规统计信息
j$b0y7L [#G0X;@e9T0SELECT plan_generation_num, creation_time,51Testing软件测试网b b!x pk-Y
 last_execution_time, execution_count,
-yA&~3M(FUhD:X0 total_worker_time, total_physical_reads,51Testing软件测试网
n? VzT o
 total_logical_reads, total_logical_writes,51Testing软件测试网@b5qxHgpana
 total_elapsed_time,
RYl;M&d+JF H0    SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
h9xfo N8l{%Hn*y0    ((CASE statement_end_offset
H(WfR\|o y0        WHEN -1 THEN DATALENGTH(st.text)51Testing软件测试网Ibf{Z6G:}
        ELSE qs.statement_end_offset END51Testing软件测试网DZDwp}
            - qs.statement_start_offset)/2) + 1) as statement_text
0[$jZy\bo0FROM sys.dm_exec_query_stats as qs
iDaC a-e$a!`V*{m0CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st

!_
A:aF\-p%])l,D0--查找Query Plan的CPU相关统计51Testing软件测试网:Q-O f$g(`j*~
--总计耗费CPU时间最长的查询计划
Z{j:Xj;v0SELECT TOP 5 total_worker_time, last_worker_time,51Testing软件测试网 ?0WW%y@$d
 max_worker_time, min_worker_time,
:g _N_&df0    SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
WN;V!Up$Il]#B0    ((CASE statement_end_offset51Testing软件测试网KH$[WC Z&Y [!?"n'[
        WHEN -1 THEN DATALENGTH(st.text)
_"s1n.b-Gzb|p.z0        ELSE qs.statement_end_offset END
t/xjcBY`0            - qs.statement_start_offset)/2) + 1) as statement_text
}(L0| Qq J5{0FROM sys.dm_exec_query_stats as qs51Testing软件测试网 YZZ?#FF
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
lR#sQ tO[0ORDER BY total_worker_time DESC51Testing软件测试网h%S0t h E3Jw

/S1G3^']zuS+V3pM0--单次执行耗费CPU时间最长的查询计划
Be\8c7g0SELECT TOP 5 total_worker_time, last_worker_time,
-n `I3O-a/x[0 max_worker_time, min_worker_time,51Testing软件测试网0DYWI r?O(?
    SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
}6GcGmtnE?8S0    ((CASE statement_end_offset51Testing软件测试网0OaX.s/x"W T
        WHEN -1 THEN DATALENGTH(st.text)51Testing软件测试网tQEf3w}:g8D
        ELSE qs.statement_end_offset END
_j"LLh_0            - qs.statement_start_offset)/2) + 1) as statement_text51Testing软件测试网;qL\7Q6|^|9PN+v
FROM sys.dm_exec_query_stats as qs
U.g-x?EN:mN^0CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st51Testing软件测试网0k [7l7uo;y:@
ORDER BY max_worker_time DESC51Testing软件测试网0h%A-A"Kd^$vZ"\

--利用次数最多的查询计划51Testing软件测试网"o:u c#_!p)?
SELECT TOP 5 creation_time, last_execution_time,51Testing软件测试网+i2n%x&XE%G&t
    execution_count,
8IK0o&|cDD} f0    SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
pB7qM)t&f#{i%z0    ((CASE statement_end_offset
Rv n9J#@8R{0        WHEN -1 THEN DATALENGTH(st.text)
jE@)sP$h6wA0        ELSE qs.statement_end_offset END
)Y vN_)v:r}.g0            - qs.statement_start_offset)/2) + 1) as statement_text51Testing软件测试网/k2X AI}4w[9Sp/G
FROM sys.dm_exec_query_stats as qs51Testing软件测试网/g'])JF"Z'e2]m
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st51Testing软件测试网H%KMS!_K%]&U
ORDER BY execution_count DESC51Testing软件测试网!B lK'e`&s\
GO
o\a(l$P0s vO%T051Testing软件测试网a
l-_AIA"~

/E+L J"HMO"dLU&S051Testing软件测试网+ET#k
iJ}Zoz
内存负载统计的演示代码
H*@"gn$e"k"f$A0 
#i5P3t:tX1Q+M0oP8?1i0--显示服务器的内存配置51Testing软件测试网9b,Ey%BM5m
--开启高级配置51Testing软件测试网2N s.^
S?'a8]
sp_configure 'show advanced options', '1'51Testing软件测试网.F"x5[&k)IY
RECONFIGURE51Testing软件测试网yv
P^/`"[
--显示各项内存相关运行值
5pQ?y9Eb{0sp_configure 'awe_enabled'
W;l|7sO0sp_configure 'min server memory'
`z-p#RIw,_0n0z0sp_configure 'max server memory'51Testing软件测试网`0["h{9NH
sp_configure 'min memory per query'51Testing软件测试网%~ev2jcCPj
sp_configure 'query wait'
$hU#Dm+yy_0--关闭高级配置
_b0I)C"^5d!h i0sp_configure 'show advanced options', '0'51Testing软件测试网b7lp5R4N"B
RECONFIGURE

--显示系统相关信息51Testing软件测试网-LmW;{r"r5{/U3F6_
select
i\+Z"zuF0 cpu_count,51Testing软件测试网aZ6d6p}2gva
 hyperthread_ratio,
\Nx Wri0 scheduler_count,
!w l
cJHZ4E:uVK0 physical_memory_in_bytes / 1024 / 1024 as physical_memory_mb,51Testing软件测试网2F z\:Pt1S
 virtual_memory_in_bytes / 1024 / 1024 as virtual_memory_mb,
3]h'x] Z*V^0 bpool_committed * 8 / 1024 as bpool_committed_mb,
"{-Z'O6D
Y-fS!w ?:O0 bpool_commit_target * 8 / 1024 as bpool_target_mb,

{*N-Ye&[mA'@4X]0 bpool_visible * 8 / 1024 as bpool_visible_mb
&ZL%NS^I u;V!v%j-c0from sys.dm_os_sys_info

--显示SQL Server的内存分配情况
D8O:WM`|4H9D0DBCC MEMORYSTATUS

:HX|6^SlZ)[;l7U1`g0--显示各种对象占用内存的数量51Testing软件测试网@Zds
{;F$aL
SELECT type, SUM (pages_allocated_count * page_size_in_bytes) as 'Bytes Used'
i`2D1E1HIL{ _0FROM sys.dm_os_memory_objects51Testing软件测试网i(J7bY4s+U#@f:U$z
GROUP BY type
)Q*|^7l'[N`Rh0ORDER BY 2 DESC;51Testing软件测试网x e(Y3L9qG#~!r
GO

--由多页分配器分配的内存总量
.tj+O]8K8qYN$jV0select sum(multi_pages_kb) / 1024.00 AS multi_pages_mb
W(C2?5|/m?0from sys.dm_os_memory_clerks51Testing软件测试网\"R]2z+q3tl]

qS/D+D*zf!Z'Ph0--统计各种类型Memory_Clerk由多页分配器分配的内存总量51Testing软件测试网/l,Il1u-fw7B
select type, sum(multi_pages_kb) / 1024.00 AS multi_pages_mb51Testing软件测试网*C:IDBQJ zD9^a
from sys.dm_os_memory_clerks 
"h|.a(w5j#ST,B0where multi_pages_kb != 0 51Testing软件测试网s foS3amP
group by type
J6{#Sj vu0order by 2 desc

ANqU ^RAO&q3h0--如果rounds_count和remove_rounds_count不断增长，代表内存面临压力
;b p?'Mx9E&M(]0select *
iauGu6Ge0from 
6q
hHBTr@w0    sys.dm_os_memory_cache_clock_hands51Testing软件测试网2i#M:c^ sd
where 51Testing软件测试网(@ A3w$Q(w#N
    rounds_count > 0
,[7\ [Iv't0    and removed_all_rounds_count > 051Testing软件测试网$]l+f sJ0?

X1L|:SF/sL?0--各种由于I/O Latch申请而导致等待的信息51Testing软件测试网xc9H-jZya!M^0gg
select wait_type, waiting_tasks_count, wait_time_ms,
f$sW$i s2A%g0signal_wait_time_ms, wait_time_ms / waiting_tasks_count AS 'avg_task_wait_time'
G$X)^4RUI _0from sys.dm_os_wait_stats 
j+A.^'SI7Uz0where wait_type like 'PAGEIOLATCH%'  and waiting_tasks_count > 0
:?SB t.nKG@GHp3t0order by wait_type51Testing软件测试网9@(|9w-`D0a.o!Vl

--查看有挂起IO的数据库或文件
:{|V(ehT2ed HuT0--运行Tough Taks 01.sql和Tough Tasks 02.sql51Testing软件测试网's a2I8^f
select database_id, file_id, io_stall, scheduler_address
_-c
eUla0from sys.dm_io_virtual_file_stats(NULL, NULL) t1,51Testing软件测试网j7g&?;dd],cC
     sys.dm_io_pending_io_requests as t2
H`'F9Y8}z"cWd-Yk0where t1.file_handle = t2.io_handle51Testing软件测试网lR[MT$lwj!iY

--寻找IO最频繁的5个查询
&T#fg+s
h0--其中的Query Plan可以到处为sqlplan文件
$coF[5IIr#d0--USE AdventureWorksDW
v-e:tZc(a*u0--SELECT * INOT dbo.TestProduct FROM dbo.DimProduct51Testing软件测试网i5?Q]i"K2@z
--DELETE FROM dbo.TestProduct51Testing软件测试网[G*W+@7]7sf8B6y|
select top 5 (total_logical_reads/execution_count) as avg_logical_reads,51Testing软件测试网
_seEK!cbs
                   (total_logical_writes/execution_count) as avg_logical_writes,
8A!d.@i.L\$h @0      (total_physical_reads/execution_count) as avg_physical_reads,51Testing软件测试网!v.{,f?KXNR
      Execution_count, statement_start_offset, p.query_plan, q.text51Testing软件测试网 Kb#Li];n6h
from sys.dm_exec_query_stats51Testing软件测试网k_0w9?F(f,}
 cross apply sys.dm_exec_query_plan(plan_handle) p51Testing软件测试网(zK+v&o!IS+w
 cross apply sys.dm_exec_sql_text(plan_handle) as q51Testing软件测试网v5Y0`0^eIx []1^
order by (total_logical_reads + total_logical_writes)/execution_count Desc

v'^iLP$`2`0--Query Option对执行计划及系统负载的影响51Testing软件测试网5? _
~ `6bG2p M)JT _
SET STATISTICS IO ON51Testing软件测试网d@&SvfKX;iW]?A
SET STATISTICS TIME ON

;H3m d0UsF0--注意服务器统计输出
O\|s g4U0DBCC DROPCLEANBUFFERS           --清除相关缓存51Testing软件测试网${T?g L8W;Q"g(R.~6{P
DBCC FREESYSTEMCACHE ('ALL')   --清除相关缓存51Testing软件测试网lUY K-c4nu)qT^
SELECT c.Title, c.FirstName, c.MiddleName, c.LastName,
,h1R6X V'L9F0 c.Suffix, e.Title AS JobTitle
It"{V8n@1Hn0FROM HumanResources.Employee AS e INNER JOIN
(_t9DF'kx+~0p'?/U0 Person.Contact AS c ON c.ContactID = e.ContactID

-|&mU6Z%{ CR6pUD3s0--注意服务器统计输出51Testing软件测试网y!T:f#? O~;AjA+X8{
DBCC DROPCLEANBUFFERS           --清除相关缓存
-?y+t a{ j#v0DBCC FREESYSTEMCACHE ('ALL')   --清除相关缓存51Testing软件测试网3Srd/m3XQf
SELECT c.Title, c.FirstName, c.MiddleName, c.LastName,
$\ MzZCJ0 c.Suffix, e.Title AS JobTitle
w#g?J `L \1iI6p0FROM HumanResources.Employee AS e INNER JOIN
e g!k.N%QUm d(o9^0 Person.Contact AS c ON c.ContactID = e.ContactID
0]"e fn7Q0 OPTION (MERGE JOIN)
\1OeD B^O0
9L,q"e)`pz%~0
8Mr;[ sv6s0
N Y2t tD*Pd[0Tempdb负载统计的演示代码
*s:@ u/vfX7pSeva051Testing软件测试网N X{OIy TR
--统计Tempdb各部分的空间消耗51Testing软件测试网Ew&es`.Q4K m4w
select
3aHR
hL.B0L!X&I0    sum(user_object_reserved_page_count)*8 as user_objects_kb,51Testing软件测试网!N-ol)@
w
    sum(internal_object_reserved_page_count)*8 as internal_objects_kb,51Testing软件测试网I&s%s|7r][
    sum(version_store_reserved_page_count)*8  as version_store_kb,51Testing软件测试网 aj UmvK]rI.cJ
    sum(unallocated_extent_page_count)*8 as freespace_kb
m GX&Hug%P t+g0from sys.dm_db_file_space_usage51Testing软件测试网@;A.dZ:U8_ I-U*b
r
where database_id = 251Testing软件测试网6K e/dvZ,q0K$ex

LS+N-eLD0--统计Tempdb中用户对象消耗的空间
b/O9CZ^0--由于演示系统空闲，在另一会话中创建一个全局临时表
Z-}8UOG'Hh\0use tempdb
'DV$uU:hY'S#l)m0declare userobj_cursor cursor for 
3u:D"Ij/F1_t0select 
3~ E+_!ZJX4m+b/PC0     sys.schemas.name + '.' + sys.objects.name 51Testing软件测试网xD0jha,N5ng
from sys.objects, sys.schemas51Testing软件测试网z:X7A2e EXu7\
where object_id > 100 and 51Testing软件测试网5@q!S*x6g
Z2n7A+B
      type_desc = 'USER_TABLE'and 
3f3K~#v uhO:V%}0      sys.objects.schema_id = sys.schemas.schema_id51Testing软件测试网q Unl&r:T+g"E
go51Testing软件测试网a^"]6P*{K
 51Testing软件测试网,jsn;k1JU'_.o
open userobj_cursor51Testing软件测试网}6ub(Zodw
go
{;RO7EX K0 
A7kMn:[4a%B$y+s(}J0declare @name varchar(256)
X]!UP q r {I%^0fetch userobj_cursor into @name51Testing软件测试网7N'?M%y s6OW}
while (@@FETCH_STATUS = 0) 
T-{7uE2ZSn;f|0begin51Testing软件测试网)rZ:i3c:IRr;n
    exec sp_spaceused @objname = @name
:d+R/}\kR0        fetch userobj_cursor into @name    
XG/Nn7h/}0end
gt#M-S'h1Ld0close userobj_cursor51Testing软件测试网rh^
js9Z
deallocate userobj_cursor

--统计每个会话创建的内部对象51Testing软件测试网#J3yx-R/P [Ubr K
select 51Testing软件测试网@.K9l `a7i;^q
    session_id, 51Testing软件测试网ns7w|Ss8p
    internal_objects_alloc_page_count, 51Testing软件测试网n[2[(ck!F%iB
    internal_objects_dealloc_page_count
1Ue6mls0from sys.dm_db_session_space_usage
s5g AjW{0order by internal_objects_alloc_page_count DESC 
q9WiYU(~v([7pJM0