SQLSERVER收集語(yǔ)句運(yùn)行的統(tǒng)計(jì)信息并進(jìn)行分析

SET STATISTICS TIME ON
SET STATISTICS IO ON
SET STATISTICS PROFILE ON

DBCC DROPCLEANBUFFERS
--清除buffer pool里的所有緩存數(shù)據(jù)
DBCC freeproccache
GO
--清除buffer pool里的所有緩存的執(zhí)行計(jì)劃
SET STATISTICS TIME ON
GO
USE [AdventureWorks]
GO
SELECT DISTINCT([ProductID]),[UnitPrice] FROM [dbo].[SalesOrderDetail_test]
WHERE [ProductID]=777
GO
SET STATISTICS TIME OFF
GO

SQL Server 分析和編譯時(shí)間:
CPU 時(shí)間 = 15 毫秒，占用時(shí)間 = 104 毫秒。
SQL Server 分析和編譯時(shí)間:
CPU 時(shí)間 = 0 毫秒，占用時(shí)間 = 0 毫秒。
(4 行受影響)
SQL Server 執(zhí)行時(shí)間:
CPU 時(shí)間 = 171 毫秒，占用時(shí)間 = 1903 毫秒。
SQL Server 分析和編譯時(shí)間:
CPU 時(shí)間 = 0 毫秒，占用時(shí)間 = 0 毫秒。

SET STATISTICS TIME ON
GO
SELECT DISTINCT([ProductID]),[UnitPrice] FROM [dbo].[SalesOrderDetail_test]
WHERE [ProductID]=777
GO
SET STATISTICS TIME OFF
GO

SQL Server 分析和編譯時(shí)間:
CPU 時(shí)間 = 0 毫秒，占用時(shí)間 = 0 毫秒。
SQL Server 分析和編譯時(shí)間:
CPU 時(shí)間 = 0 毫秒，占用時(shí)間 = 0 毫秒。
(4 行受影響)
SQL Server 執(zhí)行時(shí)間:
CPU 時(shí)間 = 156 毫秒，占用時(shí)間 = 169 毫秒。
SQL Server 分析和編譯時(shí)間:
CPU 時(shí)間 = 0 毫秒，占用時(shí)間 = 0 毫秒。

SET STATISTICS TIME OFF 
GO

DBCC DROPCLEANBUFFERS
GO
SET STATISTICS IO ON
GO
SELECT DISTINCT([ProductID]),[UnitPrice] FROM [dbo].[SalesOrderDetail_test]
WHERE [ProductID]=777
GO

SET STATISTICS IO ON
GO
SELECT DISTINCT([ProductID]),[UnitPrice] FROM [dbo].[SalesOrderDetail_test]
WHERE [ProductID]=777
GO

1 表 'SalesOrderDetail_test'。掃描計(jì)數(shù) 5，邏輯讀取 15064 次，物理讀取 0 次，預(yù)讀 0 次，lob 邏輯讀取 0 次，
2 lob 物理讀取 0 次，lob 預(yù)讀 0 次。

SET STATISTICS IO OFF
GO

SET STATISTICS PROFILE ON
GO
SELECT COUNT(b.[SalesOrderID])
FROM [dbo].[SalesOrderHeader_test] a
INNER JOIN [dbo].[SalesOrderDetail_test] b
ON a.[SalesOrderID]=b.[SalesOrderID]
WHERE a.[SalesOrderID]>43659 AND a.[SalesOrderID]53660
GO