SQL SERVER查詢最佳化工具:統計SQL語句執行時間

1.使用SET STATISTICS TIME ON  
-- 首先清除緩衝   
DBCC DROPCLEANBUFFERS;   
DBCC FREEPROCCACHE;   
   
-- 運行   
SET STATISTICS TIME ON;   
   
SELECT orderid, custid, empid, shipperid, orderdate, filler   
FROM dbo.Orders   
WHERE orderdate >= '20060101'   
  AND orderdate < '20060201';   
   
SET STATISTICS TIME OFF;   
GO   

您將會得到類似的訊息:  

SQL Server 分析和編譯時間:    
   CPU 時間 = 15 毫秒，佔用時間 = 33 毫秒。   
   
SQL Server 執行時間:   
   CPU 時間 = 0 毫秒，佔用時間 = 6 毫秒。   

輸出顯示了分析和編譯該查詢所花費的純CPU時間和實耗時間,以及執行查詢所花費的時間. 

問題是:SET STATISTICS TIME ON;SET STATISTICS TIME OFF之間有多少條SQL語句,就輸出多少次.閱讀比較費盡. 

2.使用getdate()  
declare @d datetime   
   
set @d=getdate()   
   
--------------------------------------------------------   
--/**   
declare @year int   
   
set @year = 2000   
   
 while @year<2065   
   
         begin   
   
                   exec sp_reset @year,1   
   
                   set @year = @year+1   
   
         end   
   
--------------------------------------------------------   
   
select [語句執行花費時間(毫秒)]=datediff(ms,@d,getdate())   
--*/   

在執行查詢前在變數中儲存GETDATE的值,查詢後提取變數中儲存的值.兩個日期的差值就是本次批處理啟動並執行時間.  

3.使用 SQL SERVER Profiler 

SQL SERVER Profiler可以監測資料庫正在啟動並執行SQL查詢.如果要得到查詢運行,請監測SP:StmtCompleted和SQL:BatchCompleted事件.Duration列代表執行的時間.

優點:是即時監測. 

4.使用sys.dm_exec_query_stats和sys.dm_exec_sql_text. 

 sys.dm_exec_query_stats :返回緩衝查詢計劃的彙總效能統計資訊。緩衝計劃中的每個查詢語句在該視圖中對應一行，並且行的生存期與計劃本身相關聯。在從緩衝刪除計劃時，也將從該視圖中刪除對應行。

sys.dm_exec_sql_text :返回由指定的 sql_handle 標識的 SQL 批處理的文本。該資料表值函式將替換系統函數 fn_get_sql。 

 SELECT   total_worker_time/1000 N'所用的CPU總時間ms'     
        , total_elapsed_time/1000  N'總花費時間ms'     
        , (total_elapsed_time / execution_count)/1000  N'平均時間ms'     
        ,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) N'執行語句'     
FROM sys.dm_exec_query_stats AS qs     
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st     

優點:可以得到sql查詢的曆史和他們分別花費的時間.這在曆史分析非常有用.