How to observe SQL Server generation and reuse execution plans

An SQL statement for data query must be evaluated by SQL Server and generate the corresponding execution plan. Finally, the actual query operation is generated based on the Execution Plan and the result set is returned to the client, evaluating and generating an execution plan is a few CPU-consuming tasks. Therefore, SQL Server does not re-evaluate and generate an execution plan every time, in addition, for many SQL statements with different parameter values, their execution plans should be the same, for example, for the following two SQL statements:

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

Therefore, to optimize the performance of the database system, the general database system caches the execution plan and designs a mechanism to reuse the cached execution plan.

To test and observe how SQL Server generates and reuse execution plans, you can use the following method:

First, use the following statement to clear the execution plan cache:

　　DBCC FREEPROCCACHE

Use the following statement to view the execution plan after clearing:

　　select * from sys.syscacheobjects

Run the following statement to generate an execution plan for 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

Finally, use the following statement to observe the execution plan generation and reuse mechanism.

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