Exploring SQL Server query plan SET options

Written by Raul Gonzalez | 10-Dec-2018 12:52:00

Every time SQL Server is requested to execute a query, it needs to go through different phases to complete that request.

During one of those phases the query optimiser generates an execution plan which will determine how the different objects that participate in that request will be used, in which order and how they will interact to satisfy the user's request.

This process is usually very CPU intensive because as the number of objects involved increases, the different possible ways to get the job done grow exponentially and figuring out which one would be not just be best, only good enough might be complicated.

So in order to avoid having to repeat that costly process again and again for maybe the same queries, SQL Server will store them in the plan cache, which lives in memory.

The plan cache will keep these plans in memory to reuse them when required in a way that the more frequently they are used, the longer they would stay in memory.

Background

Working on a client's system, I was troubleshooting some performance issues on a specific stored procedure. When I queried the plan cache to see what the plan was for this procedure, I found not one but two plans there.

Why SQL Server would need two different plans for the same stored procedure? Let me show you.

In this example I'm going to use the sample database [AdventureWorks2014], but you can use what you have available if you prefer.

Setting up the playground

We are going to execute one stored procedure and see how SQL Server will store the plan in the plan cache

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

USE [AdventureWorks2014]
GO
 
DECLARE @StartProductID int = 750
DECLARE @CheckDate datetime = '2010-08-05'
 
EXECUTE [dbo].[uspGetBillOfMaterials]
   @StartProductID
  ,@CheckDate
GO
 
SELECT st.database_id 
        , DB_NAME(st.database_id) AS database_name
        , st.object_id
        , OBJECT_SCHEMA_NAME(st.object_id, st.database_id)
            + '.' + OBJECT_NAME(st.object_id, st.database_id) AS object_name
        , st.cached_time 
        , qp.query_plan 
    FROM sys.dm_exec_procedure_stats AS st 
        OUTER APPLY sys.dm_exec_query_plan(st.plan_handle) AS qp 
    WHERE st.object_id = OBJECT_ID('AdventureWorks2014.dbo.uspGetBillOfMaterials')
GO

So far so good, we can see that the procedure was compiled and cached, on the rightmost column we can see the estimated plan that lives in the plan cache.

Now I'm going to generate a new plan for the same procedure that will coexists in the plan cache with the previous one, allow me a second :)

[Something happens here]

And here I'm back, let's have another look at the plan cache.

If we run the previous query, you can see a second plan was generated some time after the first one.

Each plan is generated under the influence of specific ANSI settings which determine the behaviour of the SQL Server Query Optimiser.

You might have seen this behaviour when working with filtered indexes, indexed views and computed columns.

How to know which settings apply to each query plan

As I said, each plan is generated for specific ANSI settings, but which are those?

SQL Server keep track of those settings in a DMV called sys.dm_exec_plan_attributes where a row is stored per plan and attribute, we are looking at the attribute 'set_options' and the possible values are defined in Books Online.

The value is a bit value so in order to display the different settings we need to compare the value bit-wise, let me show you how.

First we'll build a temp table to store the possible values and then compare to the value stored in the column explained above.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

CREATE TABLE #set_options ([option] SYSNAME, [value] INT)
INSERT INTO #set_options 
    VALUES ('ANSI_PADDING', 1)
    ,('Parallel Plan', 2)
    ,('FORCEPLAN', 4)
    ,('CONCAT_NULL_YIELDS_NULL', 8)
    ,('ANSI_WARNINGS', 16)
    ,('ANSI_NULLS', 32)
    ,('QUOTED_IDENTIFIER', 64)
    ,('ANSI_NULL_DFLT_ON', 128)
    ,('ANSI_NULL_DFLT_OFF', 256)
    ,('NoBrowseTable', 512)
    ,('TriggerOneRow', 1024)
    ,('ResyncQuery', 2048)
    ,('ARITH_ABORT', 4096)
    ,('NUMERIC_ROUNDABORT', 8192)
    ,('DATEFIRST', 16384)
    ,('DATEFORMAT', 32768)
    ,('LanguageID', 65536)
    ,('UPON', 131072)
    ,('ROWCOUNT', 262144)    
GO
 
SELECT st.database_id AS db_id
        , DB_NAME(st.database_id) AS database_name
        , st.object_id
        , OBJECT_SCHEMA_NAME(st.object_id, st.database_id)
            + '.' + OBJECT_NAME(st.object_id, st.database_id) AS object_name
        , st.cached_time 
        --, qp.query_plan 
        , pa.value AS set_options 
        , STUFF((SELECT ', ' + s.[option]
                    FROM #set_options AS s 
                    WHERE CONVERT(INT, pa.value) & s.value <> 0 
                    ORDER BY s.[option] ASC
                    FOR XML PATH('')), 1, 2, '') AS set_options 
    FROM sys.dm_exec_procedure_stats AS st 
        OUTER APPLY sys.dm_exec_query_plan(st.plan_handle) AS qp 
        OUTER APPLY sys.dm_exec_plan_attributes(st.plan_handle) AS pa
    WHERE st.object_id = OBJECT_ID('AdventureWorks2014.dbo.uspGetBillOfMaterials')
        AND pa.attribute = 'set_options'
    ORDER BY cached_time ASC
GO

And you can see what the trick was the the second run, I specified SET ARITHABORT OFF and therefore the plan that existed in the plan cache couldn't be reused.

Conclusion

ANSI settings might make your troubleshooting a challenge, as you can get the best plan possible in SSMS, but your application users wouldn't just because the application use different settings.

Probably the next step is to go check if you have multiple plans for the same procedure and start asking questions and compare the performance to know how to improve it.

Thanks for reading!

View full post