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.
|
|
|
|
|
|