以下脚本在需要测试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.
对以上指标的解读见我前面的文章