What would you do if someone tells you to parse and compile a T-SQL query without executing it?
This could possibly be required under a situation wherein one needs to have a compiled plan available in the procedure cache before users get into a system and the server has either been restarted or has been rebuilt.
To the best of my knowledge, there is no UI element (a toolbar button, or a check-box) to achieve this. The only way I know is to use a SET option – NOEXEC.
SET NOEXEC
As discussed in a previous post, SET options allow us to change the behaviour of SQL Server for the current session with respect to handling of specific information like date and time values, locking, Query execution, transaction, ISO settings and Statistics.
Execution of a query is a two-step process and involves query compilation and subsequent execution of the compiled plan. When the NOEXEC option is set to ON, the SQL Server database engine only compiles the query, but does not execute it. Therefore, a compiled plan is produced and stored in the procedure cache (under normal circumstances).
Let’s do a simple test to confirm this.
USE AdventureWorks2012
GO
--Set the NOEXEC option to ON, telling the database engine
--to Parse and Compile the query, but not execute it
SET NOEXEC ON
SELECT '2' AS RoundNum,
Employee.BusinessEntityID,
Employee.BirthDate,
Employee.Gender,
Employee.JobTitle
FROM HumanResources.Employee AS Employee;
GO
SET NOEXEC OFF
--Check for caching
SELECT '2' AS RoundNum, usecounts, cacheobjtype, objtype, text
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE usecounts > 0 AND
text like '%FROM HumanResources.Employee%'
AND text NOT LIKE '%Check%'
ORDER BY usecounts DESC;
GO
Execution of both queries succeeds, and the 2nd query (which looks for presence of a cached plan) indicates that compilation has indeed taken place and the query plan generated.
Here’s what I would like to know: Have you ever encountered a situation wherein you required to set NOEXEC to ON? Do leave a note in the blog comments as you go.
Until we meet next time,
Be courteous. Drive responsibly.