Users: 15,000+ | Page Views: 50,000+ | PPS: 2.5
TechDevOps.com
Explore Tools
{Experts in Microsoft SQL Server Operations, Performance, HA/DR, Data Migrations & Cloud Computing}



T-SQL SET PARSEONLY ON vs SET NOEXEC ON vs SET FMTONLY ON
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2016-09-02








These set options configure the current session as per below specifications.


SET PARSEONLY ON
Examines the syntax of each Transact-SQL statement and returns any error messages without compiling or executing the statement.

SET NOEXEC ON
Compiles each query but does not execute it.

SET FMTONLY ON
Returns only metadata to the client. Can be used to test the format of the response without actually running the query.



Compiled = Cache Entry Exists



SQL Server Architecture - Query Life Cycle:







Example #1: Table Exists


DBCC FREEPROCCACHE
go
DBCC DROPCLEANBUFFERS
go
SET PARSEONLY ON
GO
SELECT top 1 * from dbo.LOG_PROCESS
GO
SET PARSEONLY OFF
Go
--No record returned. No columns returned. No error.

--Check Cache
SELECT 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 '%SELECT top 1 * from dbo.LOG_PROCESS%' and [text] not like '%This%'
ORDER BY usecounts DESC;
--No cache entry


DBCC FREEPROCCACHE
go
DBCC DROPCLEANBUFFERS
go
SET NOEXEC ON
GO
SELECT top 1 * from dbo.LOG_PROCESS
GO
SET NOEXEC OFF
Go
--No record returned. Columns returned. No error.

--Check Cache
SELECT 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 '%SELECT top 1 * from dbo.LOG_PROCESS%' and [text] not like '%This%'
ORDER BY usecounts DESC;
--Cache entry exists



DBCC FREEPROCCACHE
go
DBCC DROPCLEANBUFFERS
go
SET FMTONLY ON
GO
SELECT top 1 * from dbo.LOG_PROCESS
GO
SET FMTONLY OFF
Go
--No record returned. Columns returned. No error.

--Check Cache
SELECT 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 '%SELECT top 1 * from dbo.LOG_PROCESS%' and [text] not like '%This%'
ORDER BY usecounts DESC;
--Cache entry exists



Example #2: Table does Not Exist


DBCC FREEPROCCACHE
go
DBCC DROPCLEANBUFFERS
go
SET PARSEONLY ON
GO
SELECT top 1 * from dbo.LOG_PROCESS_X
GO
SET PARSEONLY OFF
Go
--No record returned. No columns returned. No error (no error for invalid object)

--Check Cache
SELECT 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 '%SELECT top 1 * from dbo.LOG_PROCESS_X%' and [text] not like '%This%'
ORDER BY usecounts DESC;
--No cache entry exists


DBCC FREEPROCCACHE
go
DBCC DROPCLEANBUFFERS
go
SET NOEXEC ON
GO
SELECT top 1 * from dbo.LOG_PROCESS_X
GO
SET NOEXEC OFF
Go
--No record returned. No Columns returned. No error (no error for invalid object)

--Check Cache
SELECT 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 '%SELECT top 1 * from dbo.LOG_PROCESS_X%' and [text] not like '%This%'
ORDER BY usecounts DESC;
--Cache entry exists



DBCC FREEPROCCACHE
go
DBCC DROPCLEANBUFFERS
go
SET FMTONLY ON
GO
SELECT top 1 * from dbo.LOG_PROCESS_X
GO
SET FMTONLY OFF
Go
--No record returned. No Columns returned. Error for Invalid object name dbo.LOG_PROCESS_X.

--Check Cache
SELECT 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 '%SELECT top 1 * from dbo.LOG_PROCESS_X%' and [text] not like '%This%'
ORDER BY usecounts DESC;
--Cache entry exists

So, PARSEONLY and NOEXEC do not throw error for invalid object / object not exists.



Example #3: Syntax Error


DBCC FREEPROCCACHE
go
DBCC DROPCLEANBUFFERS
go
SET PARSEONLY ON
GO
SELECT top 1 *, from dbo.LOG_PROCESS
GO
SET PARSEONLY OFF
Go
--No record returned. No columns returned. Error for Incorrect syntax.

--Check Cache
SELECT 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 '%SELECT top 1 *, from dbo.LOG_PROCESS_X%' and [text] not like '%This%'
ORDER BY usecounts DESC;
--No Cache entry exists


DBCC FREEPROCCACHE
go
DBCC DROPCLEANBUFFERS
go
SET NOEXEC ON
GO
SELECT top 1 *, from dbo.LOG_PROCESS
GO
SET NOEXEC OFF
Go
--No record returned. No Columns returned. Error for Incorrect syntax.

--Check Cache
SELECT 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 '%SELECT top 1 *, from dbo.LOG_PROCESS_X%' and [text] not like '%This%'
ORDER BY usecounts DESC;
--No Cache entry exists



DBCC FREEPROCCACHE
go
DBCC DROPCLEANBUFFERS
go
SET FMTONLY ON
GO
SELECT top 1 *, from dbo.LOG_PROCESS
GO
SET FMTONLY OFF
Go
--No record returned. No Columns returned. Error for Incorrect syntax.

--Check Cache
SELECT 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 '%SELECT top 1 *, from dbo.LOG_PROCESS_X%' and [text] not like '%This%'
ORDER BY usecounts DESC;
--No Cache entry exists



These are useful if you have an overnight process such as a SQL Job for example and to prevent a failure due to syntax or schema changes, you
can set up a 2nd SQL Job that does a PARSEONLY and run that during business hours each day. Then a breaking syntax or schema change
would be detected at 3:00PM vs 3:00AM.