#0205 – SQL Server – SET options – NOEXEC – Parse, Compile, but do not Execute a T-SQL query

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.

3 thoughts on “#0205 – SQL Server – SET options – NOEXEC – Parse, Compile, but do not Execute a T-SQL query”

a.diniz October 18, 2012 at 9:46 AM

I haven’t come across this before but it seems like a good solution to warming up the query cache. Very interesting. It’s worth pointing out though that if you want to use it to warm up the cache, you must execute SET NOEXEC in its own batch prior to executing the query i.e. add the ‘GO’ statement after SET NOEXEC ON;

SET NOEXEC ON;
GO

SELECT ‘2’ AS RoundNum,
Employee.BusinessEntityID,
Employee.BirthDate,
Employee.Gender,
Employee.JobTitle
FROM HumanResources.Employee AS Employee;

Omitting the ‘GO’ results in a plan containing the ‘SET NOEXEC ON’ statement being cached. Not what we want!

On a separate note, I have a question… I knew that hitting ‘Display Estimated Execution Plan’ in Management Studio does not add the plan to the cache. I just assumed it was because the query doesn’t get executed. Knowing that SET NOEXEC ON prevents execution but still adds the plan to the cache (if it wasn’t there already) raises the question: “at what point does a plan get added to the cache?”

LikeLike

Reply ↓