优化-SQL Server 05 的动态管理视图DMV和函数DMF

SQL Server 05提供了动态管理视图Dynamic Management Views和函数 Functions,方便了我们对系统运行情况的监控,故障诊断和性能优化.配合Profiler,dashboard一起使用很不错.

1. 使用sys.dm_exec_query_stats和sys.dm_exec_sql_text找到CPU占用率高的语句 这里有篇文章不错 http://database.ctocio.com.cn/tips/159/7771659.shtml
   

   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://topic.csdn.net/u/20100308/14/6b6307d1-efea-459c-b3cd-b50c29d3642e.html 

   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;
   while@x<10000
   set@x=@x+1;
   print'ms of cput for loop1:' 
   +cast ( (@@cpu_busy-@cpu_start) +@@timeticks/1000aschar);
   set@cpu_start=@@cpu_busy;
   while@x<100000
   set@x=@x+1;
   print'ms of cput for loop1:' 

     +cast ( (@@cpu_busy-@cpu_start) +@@timeticks/1000aschar);

   --注意这两个参数 @@cpu_busy @@timeticks
2. 使用sys.dm_exec_cached_plans和sys.dm_exec_sql_text找到执行最频繁的语句 这里有篇文章不错http://database.ctocio.com.cn/tips/161/7810661.shtml
   当dbid值是32767时，就会出现这种情况。因为数据库的ID号与系统数据库，即所谓的资源库是有联系的。这个资源库不是众所周知，但它却是存在于系统中的一个实际数据库，他的确存在，但你在SQL Server Management Studio中却看不到它。在你的数据文件目录下，有一个以字符串“mssqlsystemreource”开始命名的MDF和LDF文件，那就是资源数据库了
   

   SELECTCASEwhen dbid =32767 
   then'Resource' 
   elseDB_NAME(dbid) end[DB_NAME], 
   OBJECT_SCHEMA_NAME(objectid,dbid) AS[SCHEMA_NAME], 
   OBJECT_NAME(objectid,dbid)AS[OBJECT_NAME], 
   SUM(usecounts) AS[Use_Count], 
   SUM(total_elapsed_time) AS[total_elapsed_time],
   SUM(total_elapsed_time) /SUM(usecounts) *1.0AS[avg_elapsed_time],
   substring(convert(char(23),DATEADD(ms,sum(total_elapsed_time)/1000,0),121),12,23) 

    AS total_elapsed_time_ms,

   dbid, 
   objectid 
   FROM sys.dm_exec_cached_plans cp
   CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) 
   JOIN 
   (SELECTSUM(total_elapsed_time) AS[total_elapsed_time],
   plan_handle 
   FROM sys.dm_exec_query_stats 
   GROUPBY plan_handle) qs
   ON cp.plan_handle = qs.plan_handle 
   WHERE objtype ='Proc'
   ANDUPPER(
   -- remove white space first
   REPLACE( 
   REPLACE(
   REPLACE(
   REPLACE(
   REPLACE(
   REPLACE(
   REPLACE(text,'',''),
   '',''),
   '',''),
   '', ''),
   '',''),
   '',''),
   '','')
   ) 
   LIKE'%CREATE PROC%'
   GROUPBY dbid, objectid
   ORDERBYSUM(total_elapsed_time) /SUM(usecounts) *1.0DESC;