优化 sql server 2005 的动态管理视图dmv和函数dmf sql server 05提供了动态管理视图dynamic management views和函数 functions,方便了我们对系统运行情况的监控, 故障 诊断 和 性能 优化 . 配合 profiler,dashboard 一起 使用 很 不错 . 1. 使用 sys.dm_exe
优化sql server 2005 的动态管理视图dmv和函数dmf
sql server 05提供了动态管理视图dynamic management views和函数 functions,方便了我们对系统运行情况的监控,故障诊断和性能优化.配合profiler,dashboard一起使用很不错.
1.使用sys.dm_exec_query_stats和sys.dm_exec_sql_text找到cpu占用率高的语句
select top 100 execution_count,
total_logical_reads /execution_count as [avg logical reads],
total_elapsed_time /execution_count as [avg elapsed time],
db_name(st.dbid) as [database name],
object_name(st.dbid) as [object name],
object_name(st.objectid) as [object name 1],
substring(st.text, (qs.statement_start_offset / 2) + 1,
((case statement_end_offset when - 1 then datalength(st.text) else qs.statement_end_offset end - qs.statement_start_offset)
/ 2) + 1) as statement_text
from sys.dm_exec_query_stats as qs cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
where execution_count > 100
order by 1 desc;
--关于statement_start_offset/2的。更多内容http://www.cnblogs.com/ewialitwind/
io跟踪
setstatistics io on
go
selecttop1*from sales.customer where customertype 's';
customerid territoryid accountnumber customertype rowguid modifieddate
customerid territoryid accountnumber customertype rowguid modifieddate
----------- ----------- ------------- ------------ ------------------------------------ -----------------------
11000 9 aw00011000 i 477586b3-2977-4e54-b1a8-569ab2c7c4d4 2004-10-13 11:15:07.263
(1 行受影响)
表 'customer'。扫描计数 1，逻辑读取 6 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
--如果需要清理缓存池 dbcc dropcleanbuffer
cpu时间
declare@xint;
declare@cpu_startint;
set@x=1;
set@cpu_start=@@cpu_busy;
set@x=@x+1;
print'ms%20of%20cput%20for%20loop1:'%20
+cast%20(%20(@@cpu_busy-@cpu_start)%20+@@timeticks/1000aschar);
set@cpu_start=@@cpu_busy;
set@x=@x+1;
print'ms of cput for loop1:'
+cast ( (@@cpu_busy-@cpu_start) +@@timeticks/1000aschar);
--注意这两个参数 @@cpu_busy @@timeticks.健康知识胆系结石的治疗