優化-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跟蹤

   set statistics io on
   go
   select top 1 * from sales.customer where customertype <> 'S';
   CustomerID  TerritoryID AccountNumber CustomerType rowguid                              ModifIEdDateCustomerID  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 @x int;
   declare @cpu_start int;
   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 / 1000 as char);
   set @cpu_start = @@cpu_busy;
   while @x < 100000
       set @x = @x + 1;
   print 'ms of cput for loop1:'            + cast ( (@@cpu_busy - @cpu_start) + @@timeticks / 1000 as char);--注意這兩個參數 @@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文件，那就是資源數據庫了
   

   SELECT CASE when dbid = 32767 
               then 'Resource' 
               else DB_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.0 AS [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 
      (SELECT SUM(total_elapsed_time) AS [total_elapsed_time],
              plan_handle  
         FROM sys.dm_exec_query_stats  
         GROUP BY plan_handle) qs
       ON cp.plan_handle = qs.plan_handle 
   WHERE objtype = 'Proc'
     AND UPPER(
   -- remove white space first
               REPLACE( 
                REPLACE(
                 REPLACE(
                  REPLACE(
                   REPLACE(
                    REPLACE(
                     REPLACE(text,'       ',' '),
                    '       ',' '),
                   '      ',' '),
                  '     ', ' '),
                 '    ',' '),
                '   ',' '),
               '  ',' ')
               ) 
          LIKE '%CREATE PROC%'
   GROUP BY dbid, objectid
   ORDER BY SUM(total_elapsed_time) / SUM(usecounts) * 1.0 DESC;