SQL Server uses the following mechanisms for caching queries and procedures:

ad-hoc batch caching - saves query plans automatically

The text of the subsequent batches must exactly match the first batch (exact textual matches - data and data type must be the same)

SELECT * FROM Products WHERE unitprice = $12.5
SELECT * FROM Products WHERE unitprice = $12.5 -- the same query plan as the previous batch
SELECT * FROM Products WHERE unitprice = 12.5 -- another query plan

auto-parametrization - saves query plans automatically SQL Server tries to create a template on the basis of SQL statements, where constants are parameters

-- The following queries will have the same query plan
SELECT * FROM Products WHERE ProductID = 7890
SELECT * FROM Products WHERE ProductID = 1234
SELECT * FROM Products WHERE ProductID = 7889

sp_executesql - SQL Server uses ad-hoc batch caching (requires persistent object management)

-- The following queries will have the same query plan
EXEC sp_execute ('SELECT * FROM Members WHERE id=@v1', '@v1 INT', @v1 = 700)
EXEC sp_execute ('SELECT * FROM Members WHERE id=@v1', '@v1 INT', @v1 = 231)
EXEC sp_execute ('SELECT * FROM Members WHERE id=@v1', '@v1 INT', @v1 = 897)

sp_prepare and sp_execute

• inform that the batch will be used repeatedly
• do not require persistent object management
• do not requires the full text of the batch (only parameters)
• support execution of SQL statements in ADO, OLE DB, and ODBC

Stored procedures sp_executesql and sp_prepare/sp_execute are typically called from client applications.

PARSE

• check syntax of the SQL statement
• break down the statement into logical units such as: keywords, parameters, operators, and identifiers

RESOLVE

• validate the names of objects are present
• determine user's permissions to the object
• the statement is resolved to a single execution plan

OPTIMIZE

• determine the most efficient method of executing the statement (the lowest cost)

COMPILE

• translate the query into an executable form
• the query plans are stored in the procedure cache - a temporary storage location for specific stored procedures

EXECUTE

• HOLDLOCK = SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
• OPTION (FAST n) - Returns n rows as fast as possible
• ROBUST PLAN - Forces the optimizer to use a query execution plan that will be best for the maximum row size.

Performance implications of using NOT IN, EXISTS, and LEFT OUTER JOIN

It seems that the NOT IN statement offers really poor performance. That's why EXISTS or LEFT OUTER JOIN should be used instead. There is a debate which is better – EXISTS or LEFT OUTER JOIN.

NOT EXISTS should be used because of the following reasons:

• When using NOT IN, the query performs nested full table scans, whereas for NOT EXISTS, query can use an index within the sub-query.
• EXISTS stops processing after the condition is met while JOIN and IN have to go over the whole table.