Effect of set options of the query plan

Changing the set options for a query, can result in different query plans saved in cache for what is otherwise the same query. Best demonstrated with an example: –


EXEC sp_recompile N'dbo.uspGetBillOfMaterials';

--remove any previous plans from cache.

SET ANSI_PADDING ON;

EXECUTE dbo.uspGetBillOfMaterials
@StartProductID = 893,
@CheckDate = '2015-11-05T00:00:00';

SET ANSI_PADDING OFF;

EXECUTE dbo.uspGetBillOfMaterials
@StartProductID = 893,
@CheckDate = '2015-11-05T00:00:00';

Querying the plan cache for this particular stored procedure, now shows two different plans each with a usecounts value of 1. This can lead to two potential issues: –

  1. The plan cache becomes bloated with these types of plans. Leaving less room within the cache for other plans.
  2. Troubleshooting performance issues can lead to erroneous conclusions. Users could be experiencing performance issues with a particular query from a front-end application. If a DBA were to execute the same query from within SSMS with differing set options, a new plan could be compiled that could perform very differently.

Both issues above can be mitigated by having consistent set options. Set options were previously demonstrated in my post regarding bitwise operations. In the example above, we can see via sys.dm_exec_plan_attributes for the two plan handles that the set options values are 4346 and 4347 respectively. If we then run: –

WITH cteSetOptions (SetOption,Value) AS (
SELECT 'ANSI_PADDING',1 UNION ALL
SELECT 'Parallel Plan',2 UNION ALL
SELECT 'FORCEPLAN',4 UNION ALL
SELECT 'CONCAT_NULL_YIELDS_NULL',8 UNION ALL
SELECT 'ANSI_WARNINGS',16 UNION ALL
SELECT 'ANSI_NULLS',32 UNION ALL
SELECT 'QUOTED_IDENTIFIER',64 UNION ALL
SELECT 'ANSI_NULL_DFLT_ON',128 UNION ALL
SELECT 'ANSI_NULL_DFLT_OFF',256 UNION ALL
SELECT 'NoBrowseTable',512 UNION ALL
SELECT 'TriggerOneRow',1024 UNION ALL
SELECT 'ResyncQuery',2048 UNION ALL
SELECT 'ARITH_ABORT',4096 UNION ALL
SELECT 'NUMERIC_ROUNDABORT',8192 UNION ALL
SELECT 'DATEFIRST',16384 UNION ALL
SELECT 'DATEFORMAT',32768 UNION ALL
SELECT 'LanguageID',65536 UNION ALL
SELECT 'UPON',131072 UNION ALL
SELECT 'ROWCOUNT',262144)

SELECT [Set Option] = SetOption + ' option is set'
FROM cteSetOptions
WHERE Value & 4346 = Value

We see the results for the first query: –

ANSI_NULL_DFLT_ON option is set
ANSI_NULLS option is set
ANSI_WARNINGS option is set
ARITH_ABORT option is set
CONCAT_NULL_YIELDS_NULL option is set
Parallel Plan option is set
QUOTED_IDENTIFIER option is set

and the second: –

ANSI_NULL_DFLT_ON option is set
ANSI_NULLS option is set
ANSI_PADDING option is set
ANSI_WARNINGS option is set
ARITH_ABORT option is set
CONCAT_NULL_YIELDS_NULL option is set
Parallel Plan option is set
QUOTED_IDENTIFIER option is set

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s