如何觀察SQL Server 產生和重用執行計畫

一條查詢資料的SQL語句首先要被SQL Server 評估並產生對應的執行計畫，最後才是根據執行計畫來產生實際的查詢操作並返回結果集給用戶端，評估並產生執行計畫是幾件很消耗CPU的事情，因此SQL Server 並不會每次都去重新評估並產生執行計畫，而且對於很多隻是參數值不同的SQL來說他們的執行計畫應該是一樣的，比如對於如下的兩個SQL來說就應該是這樣的：

DECLARE @OrderID INT
SET @OrderID = 123
EXEC sp_executesql N'SELECT TOP 1 * FROM dbo.tblOrders WHERE OrderID = @OrderID', N'@OrderID INT', @OrderID
GO

DECLARE @OrderID INT
SET @OrderID = 456
EXEC sp_executesql N'SELECT TOP 1 * FROM dbo.tblOrders WHERE OrderID = @OrderID', N'@OrderID INT', @OrderID
GO

因此出於最佳化資料庫系統效能的考慮，一般的資料庫系統都會對執行計畫進行緩衝，並設計一套機制來設法重用已經緩衝的執行計畫。

要想測試和觀察SQL Server 如何產生和重用執行計畫，可以使用下面的方法：

首先使用下面的語句來清空執行計畫緩衝：

　　DBCC FREEPROCCACHE

使用下面語句查看清空後的執行計畫：

　　select * from sys.syscacheobjects

接著執行下面語句來使SQL Server產生執行計畫：

DECLARE @OrderID INT
SET @OrderID = 123
EXEC sp_executesql N'SELECT TOP 1 * FROM dbo.tblOrders WHERE OrderID = @OrderID', N'@OrderID INT', @OrderID
GO

DECLARE @OrderID INT
SET @OrderID = 456
EXEC sp_executesql N'SELECT TOP 1 * FROM dbo.tblOrders WHERE OrderID = @OrderID', N'@OrderID INT', @OrderID
GO

最後使用下面語句觀察執行計畫的產生和重用機制

select * from sys.syscacheobjects where sql not like '%syscacheobjects%' and sql like '%tblOrders%'