Systems Engineering and RDBMS

SET Options and Execution Plans

Posted by decipherinfosys on January 21, 2008

In one of the client engagements, we had seen that the runtime execution plan that was getting picked up was different than the plan that we would have thought that the optimizer would go by (based on testing and analysis against the same data set). Now, there can be several reasons for this including what was actually going on in the system at the execution time, improper statistics, hardware configuration and layout of the files etc.. In this particular case, it turned out that the SET options that were being used by one of the applications that was pointing to the common database were not inline with the rest of the application/stored procedure code. That is fraught with danger not only for the application development but also for troubleshooting purposes.

If the SET options used by the application(s) that are pointing to the database are different and the SET options that you are using while troubleshooting the issue using SSMS (SQL Server Management Studio), then it is possible for you and the application (at runtime) to have different execution plans. Ideally, once you have established what kind of SET options are needed for the application, you should use the same for all the applications and troubleshooting that you are going to do for that environment. Let’s first pick up a simple example to demonstrate this. We will pick up an example of an indexed view to clearly show how the SET based options change the execution plan. Anyone who has used indexed views in SQL Server knows that for it’s creation and it’s usage, certain SET options need to be in place for the session. You can read more about those SET options in BOL (Books Online). Here is a link for the online version that talks about the different SET options:

http://msdn2.microsoft.com/en-us/library/ms190356.aspx

Now, let’s create a parent and a child table and populate it with some sample data:

CREATE TABLE TEST_TBL_1 (COL1 INT IDENTITY PRIMARY KEY, COL2 NVARCHAR(10));
CREATE TABLE TEST_TBL_2 (COL3 INT IDENTITY PRIMARY KEY, COL4 INT, CONSTRAINT FK_TBL_2_TBL_1 FOREIGN KEY (COL4) REFERENCES TEST_TBL_1 (COL1));

SET NOCOUNT ON
GO
INSERT INTO TEST_TBL_1 (COL2) VALUES (‘RECORD 1’);
INSERT INTO TEST_TBL_1 (COL2) VALUES (‘RECORD 2’);
INSERT INTO TEST_TBL_1 (COL2) VALUES (‘RECORD 3’);
INSERT INTO TEST_TBL_1 (COL2) VALUES (‘RECORD 4’);
INSERT INTO TEST_TBL_1 (COL2) VALUES (‘RECORD 5’);

INSERT INTO TEST_TBL_2 (COL4) VALUES (1);
INSERT INTO TEST_TBL_2 (COL4) VALUES (1);
INSERT INTO TEST_TBL_2 (COL4) VALUES (2);
INSERT INTO TEST_TBL_2 (COL4) VALUES (3);
GO

And now, let us create a view on which we will then create an index.  All of the required SET options are already in place for the creation of the view and the index.

CREATE VIEW dbo.VW_TEST
WITH SCHEMABINDING
AS
SELECT A.COL1, A.COL2, B.COL3, B.COL4
FROM dbo.TEST_TBL_1 AS A
INNER JOIN dbo.TEST_TBL_2 AS B
ON A.COL1 = B.COL4
GO

CREATE UNIQUE CLUSTERED INDEX VW_TEST_IND_1 ON VW_TEST (COL1, COL3)
GO

And now, let’s fire off a SQL statement that gets all the data for us and let’s check it’s execution plan to see whether it used our indexed view:

SELECT * FROM dbo.TEST_TBL_1 AS A
INNER JOIN dbo.TEST_TBL_2 AS B
ON A.COL1 = B.COL4

Execution Plan:

StmtText
—————————————————————————–
|–Clustered Index Scan(OBJECT:([MAINDB].[dbo].[VW_TEST].[VW_TEST_IND_1]))

And now, let us change one of the required SET options and re-try this:

SET CONCAT_NULL_YIELDS_NULL OFF
GO

SELECT * FROM dbo.TEST_TBL_1 AS A
INNER JOIN dbo.TEST_TBL_2 AS B
ON A.COL1 = B.COL4

Execution Plan:

StmtText
——————————————————————————————————————————————————————————————-
|–Nested Loops(Inner Join, OUTER REFERENCES:([B].[COL4]))
|–Clustered Index Scan(OBJECT:([MAINDB].[dbo].[TEST_TBL_2].[PK__TEST_TBL_2__13F1F5EB] AS [B]))
|–Clustered Index Seek(OBJECT:([MAINDB].[dbo].[TEST_TBL_1].[PK__TEST_TBL_1__1209AD79] AS [A]), SEEK:([A].[COL1]=[MAINDB].[dbo].[TEST_TBL_2].[COL4] as [B].[COL4]) ORDERED FORWARD)

As you can see from the output above, the indexed view was not used in this execution after we changed one of the required SET options behavior.  It now used the primary key indexes for those 2 tables (the constraint that you see above are system generated since we did not give a name to those primary keys which is not a good practice but this is just a demo to illustrate the issue🙂 )

So, as you can see from above, it is very important to evaluate what SET options you have in place for the different applications and sessions that are pointing to the database and then evaluate your execution plans from that perspective.  Also, one should strive to use the same SET options for all sessions once you have finalized them during your benchmarking and performance testing phase.

2 Responses to “SET Options and Execution Plans”

  1. […] by decipherinfosys on February 12, 2008 In one of our previous blog post, we had covered the importance of the SET options and how they can effect the usage/non-use of the […]

  2. […] to point out that you should read this MSDN post to see which SET options affect results.  And here is an example of such a scenario happening in your environment.  And another post on the same […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: