测试SQL 语句执行时间

以下脚本在需要测试sql语句或者存储过程性能的时候可以用得上

--检查数据库SQL语句执行情况
CHECKPOINT --用于检查当前工作的数据库中被更改过的数据页或日志页，并将这些数据从数据缓冲器中强制写入硬盘
GO
DBCC FreeProcCache    --从缓冲池中删除所有缓冲区  
DBCC DropCleanBuffers --从过程高速缓存中删除所有元素
GO
DBCC FreeSystemCache('ALL') ;--清除SQL实例的计划缓存
GO
SET STATISTICS PROFILE ON --显示分析、编译和执行查询所需的时间（以毫秒为单位）。
SET STATISTICS IO   ON
SET STATISTICS TIME ON
GO
---------------------------
DECLARE @begintime DATETIME
DECLARE @endtime DATETIME
SET @begintime = getdate();

-- 要测试的SQL脚本开始
SELECT 
    newid() as [Rid]
    ,a.[So_No]
    ,a.[Po_No]
    ,a.[PackingWay_Code]
    ,a.[Mo_NO]
    ,a.[Carton_No]
    ,a.[Carton_Barcode]
    ,'' as [Pallet_Barcode]
    ,'Issuing' as [Bin_Barcode]
    ,'20' as [Bin_Remark]
    ,1 as [Carton_Qty]
    ,'2' as [Rec_Type]
    ,0 as [Lock_Flag]
    ,A.[Warehouse_Code]
    ,A.[Warehouse_Desc]
    ,A.[PackingWay_Mark]
    ,A.[Rec_Date]
    ,A.[Shelf_Pallet_Barcode]
    ,A.[Shelf_BIN_Barcode]
    ,A.[Shelf_BIN_Barcode_Name]
    ,A.[lpn]
FROM Issue_Dtl a 
left join StoreSubcon_Mst b on a.Carton_Barcode=b.Carton_Barcode
WHERE a.[Issue_Mst_Rid] = '375ABBDE-5E0F-4685-90D8-F949FF3843A4' and b.Carton_Barcode is null

--要测试的SQL脚本结束

SET @endtime = getdate();
SELECT DATEDIFF(ms,@begintime,@endtime)
----------------------------
SET STATISTICS PROFILE OFF
SET STATISTICS IO      OFF
SET STATISTICS TIME    OFF
GO

DBCC DropCleanBuffers 
DBCC FreeProcCache

执行结果如下：

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 223 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(654 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Issue_Dtl'. Scan count 11, logical reads 351774, physical reads 3, read-ahead reads 1216, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'StoreSubcon_Mst'. Scan count 11, logical reads 7120, physical reads 387, read-ahead reads 5841, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(12 row(s) affected)

 SQL Server Execution Times:
   CPU time = 2281 ms,  elapsed time = 2684 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

下面的测试结果是在有缓存的情况下的表现

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(654 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Issue_Dtl'. Scan count 11, logical reads 349971, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'StoreSubcon_Mst'. Scan count 11, logical reads 7120, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(12 row(s) affected)

 SQL Server Execution Times:
   CPU time = 2265 ms,  elapsed time = 609 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

对以上指标的解读见我前面的文章