SQL Server IO 統計資訊是重要的效能調校依據,第一步就是嘗試看懂其他的涵意。
SET STATISTICS IO { ON | OFF }
執行階段
進行設定,而不是在剖析階段
進行設定。為了建構輸出的最終資料集,在達到分葉層級之後朝任何方向啟動以擷取所有值的搜尋/掃瞄次數。
以下範例使用 `微軟 [AdventureWorks2014].[Sales].[Customer]`
主索引鍵的叢集索引
或唯一索引
,而且您只要搜尋一個值,掃瞄計數就是 0。 例如 WHERE Primary_Key_Column = value
。SELECT
[CustomerID]
,[PersonID]
,[StoreID]
,[TerritoryID]
,[AccountNumber]
,[rowguid]
,[ModifiedDate]
FROM [AdventureWorks2014].[Sales].[Customer]
where [CustomerID]=1
SELECT
[CustomerID]
,[PersonID]
,[StoreID]
,[TerritoryID]
,[AccountNumber]
,[rowguid]
,[ModifiedDate]
FROM [AdventureWorks2014].[Sales].[Customer]
where [AccountNumber]='AW00000001'
value
。實際測試下 ,有下列三種情形會出現 掃瞄計數=
1
SELECT
[CustomerID]
,[PersonID]
,[StoreID]
,[TerritoryID]
,[AccountNumber]
,[rowguid]
,[ModifiedDate]
FROM [AdventureWorks2014].[Sales].[Customer]
SELECT
[CustomerID]
,[PersonID]
,[StoreID]
,[TerritoryID]
,[AccountNumber]
,[rowguid]
,[ModifiedDate]
FROM [AdventureWorks2014].[Sales].[Customer]
where [TerritoryID]=1
[AdventureWorks2014].[Sales].[Customer]
已存在唯一叢集索引(unique clustered index)
,為了不影響原本 table 設計,就從[AdventureWorks2014].[Sales].[Customer]
複製一份資料,接著建立非唯一叢集索引(non-unique clustered index)
來模擬
USE [AdventureWorks2014]
GO
SELECT *
INTO [AdventureWorks2014].[Sales].[Customer_test]
FROM [AdventureWorks2014].[Sales].[Customer]
Go
CREATE CLUSTERED INDEX [ClusteredIndex_CustomerID] ON [Sales].[Customer_test]
(
[CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
SELECT
[CustomerID]
,[PersonID]
,[StoreID]
,[TerritoryID]
,[AccountNumber]
,[rowguid]
,[ModifiedDate]
FROM [AdventureWorks2014].[Sales].[Customer]
where [CustomerID]=1
SELECT
[CustomerID]
,[PersonID]
,[StoreID]
,[TerritoryID]
,[AccountNumber]
,[rowguid]
,[ModifiedDate]
FROM [AdventureWorks2014].[Sales].[Customer]
where [CustomerID] in (1,2,3,4,5)
page
(非筆數)。--> 會包含 physical reads
及 read-ahead reads