How Set Options in SQL Server can impact a query plan.

Problem: At times application calls a stored procedure, experiences performance issues in executing it and the same procedure when executed from the SQL Server management studio shows good execution plans and performs well?

Solution: SET Options

Copies of the same compiled plan might differ only by the value in the set_options column. This indicates that different connections are using different sets of SET options for the same query. Using different sets of options is usually undesirable because it can cause extra compilations, less plan reuse, and plan cache inflation because of multiple copies of plans in the cache.

If you front end application is using a connection string to connect SQL Server, which later is used to call or execute the stored procedure may not have default set options like SSMS will have and this can deviate in replicating the problem.

As we can see below are the default options that are opted out in SSMS, right click on the query window and see query options to get this view.

As we can compare below the following defaults are set in across the applications.

	Applications using ADO .Net, ODBC or OLE DB	SSMS	SQLCMD, OSQL, BCP, SQL Server Agent	ISQL, DB Library
ANSI_NULL_DFLT_ON	ON	ON	ON	OFF
ANSI_NULLS	ON	ON	ON	OFF
ANSI_PADDING	ON	ON	ON	OFF
ANSI_WARNINGS	ON	ON	ON	OFF
CONACT_NULLS_YIELD_NULL	ON	ON	ON	OFF
QUOTED_IDENTIFIER	ON	ON	OFF	OFF
ARITHABORT	OFF	ON	OFF	OFF

So how do we know if there are any compiled stored procedures which have set options in the execution plans of the SQL Server.

In SQL Server Dynamic Management views, there is a view which captures the query plan of an object and its attributes to specify the set options. Below view accepts plan id of query or a procedure to see the set options.

sys.dm_exec_plan_attributes

Example:In this article, I  would like to create a procedure and execute it with two different set dateformat options and see how the execution plan differs and the way to find out the set options from plan cache using plan handle or sql handle or pool id.

CREATE PROCEDURE [dbo].[SearchEmployees] @EmployeeType VARCHAR(32) = NULL

            WITH EXECUTE AS CALLER

AS

BEGIN

            SELECT [EmployeeID],

                        [Name],

                        [PhoneNumber],

                        [EmployeeType]

            FROM [dbo].[Employees]

            WHERE EmployeeType = @EmployeeType

END

GO

--setting a date format to mdy see if the query optimizer choose to make a different compile plan.

SET DATEFORMAT dmy

GO

EXEC [dbo].[SearchEmployees] @employeeType = 'Developer'

GO

--setting a date format to mdy see if the query optimizer choose to make a different compile plan.

SET DATEFORMAT mdy

GO

EXEC [dbo].[SearchEmployees] @employeeType = 'Developer'

GO

  

Let’s see how does this set statements influence in the query plan for this procedure.

SELECT OBJECT_NAME(object_id, database_id) 'proc name',  

    d.cached_time, d.last_execution_time,

    d.last_elapsed_time, d.execution_count 

FROM sys.dm_exec_procedure_stats AS d 

WHERE DB_NAME(d.database_id)='DB'

and OBJECT_NAME(object_id, database_id) ='SearchEmployees'

ORDER BY [total_worker_time] DESC; 

OUTPUT:

proc name	cached_time	last_execution_time	last_elapsed_time	execution_count
SearchEmployees	2017-04-04 22:53:33.440	2017-04-04 22:53:33.440	72884	1
SearchEmployees	2017-04-04 22:53:33.533	2017-04-04 22:53:33.533	45883	1

As we can see they have difference in execution time milliseconds by 30,000 i.e 30 seconds and an application can time out in the 30 seconds delay.

Furthermore, I would like to investigate the set options to compare the plans and so I am using the dmv of the attributes to iron out the set options.

SELECT qs.plan_handle, a.attrlist

FROM   sys.dm_exec_cached_plans qs

CROSS  APPLY sys.dm_exec_sql_text(qs.plan_handle) est

CROSS  APPLY (SELECT epa.attribute + '=' + convert(nvarchar(127), epa.value) + '   '

              FROM   sys.dm_exec_plan_attributes(qs.plan_handle) epa

              WHERE  epa.is_cache_key = 1

              ORDER  BY epa.attribute

              FOR    XML PATH('')) AS a(attrlist)

WHERE  est.objectid = object_id ('dbo.SearchEmployees')

  AND  est.dbid     = db_id('DB')

OUTPUT:

plan_handle	attrlist	procedure_name
0x05000E00EB673A7CA0FBA1E40300000001	acceptable_cursor_options=0   compat_level=120   date_first=7   date_format=1   dbid=14	SearchEmployees
0x05000E00EB673A7C10FAA1E40300000001	acceptable_cursor_options=0   compat_level=120   date_first=7   date_format=2   dbid=14	SearchEmployees