Have you every executed a query in SQL Server Management Studio, looked at the execution plan, and noticed that it was a different plan than what was generated on the server?

A potential reason for this could be a different option settings.  The options represent the SET values of the current session.  SET options can affect how the query is execute thus having a different execution plan.   You can find these options in two places within SSMS under Tools -> Options -> Query Execution -> SQL Server -> Advanced.

As well as Tools -> Options -> Query Execution -> SQL Server -> ANSI

@@Options

Using the interface to check what is set can get tiresome.  Instead, you can use the system function @@OPTIONS.  Each option shown above has a BIT value for all 15 options indicating whether or not it is enabled.

It would look like this: 001010101111000.  Each bit corresponds to an option in order from the table below:

For example, the right most three bits are 0. These correspond to:

• DISABLE_DEF_CNST_CHK
• IMPLICIT_TRANSACTIONS
• CURSOR_CLOSE_ON_COMMIT

So what does @@options really return?

Running @@OPTIONS on my machine it returns a value of 5496.  Useful right? Ok, not really.  It would be nice to know exactly which options are set and which aren’t without having to go through the UI.

@@OPTIONS takes the binary representation and does a BITWISE operation on it to produce an integer value based on the sum of which BITS are enabled.

Let’s assume for a moment that the only two options that are enabled on my machine are ANSI_PADDING and ANSI_WARNINGS.  The values for these two options are 8 and 16, respectively speaking.  The sum of the two is 24.

You have Options

Thankfully, there is an easier way.  The script below will do the work for you and display what options are enabled.

/***************************************************************
  Author: John Morehouse
  Summary: This script display what SET options are enabled for the current session. 
 
  You may alter this code for your own purposes. You may republish altered code as long as you give due credit. 
 
  THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
***************************************************************/SELECT 'Disable_Def_Cnst_Chk' AS 'Option', CASE @@options & 1 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION
SELECT 'IMPLICIT_TRANSACTIONS' AS 'Option', CASE @@options & 2 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION
SELECT 'CURSOR_CLOSE_ON_COMMIT' AS 'Option', CASE @@options & 4 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION
SELECT 'ANSI_WARNINGS' AS 'Option', CASE @@options & 8 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION
SELECT 'ANSI_PADDING' AS 'Option', CASE @@options & 16 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION
SELECT 'ANSI_NULLS' AS 'Option', CASE @@options & 32 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION
SELECT 'ARITHABORT' AS 'Option', CASE @@options & 64 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION
SELECT 'ARITHIGNORE' AS 'Option', CASE @@options & 128 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION
SELECT 'QUOTED_IDENTIFIER' AS 'Option', CASE @@options & 256 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION
SELECT 'NOCOUNT' AS 'Option', CASE @@options & 512 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION
SELECT 'ANSI_NULL_DFLT_ON' AS 'Option', CASE @@options & 1024 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION
SELECT 'ANSI_NULL_DFLT_OFF' AS 'Option', CASE @@options & 2048 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION
SELECT 'CONCAT_NULL_YIELDS_NULL' AS 'Option', CASE @@options & 4096 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION
SELECT 'NUMERIC_ROUNDABORT' AS 'Option', CASE @@options & 8192 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled' UNION
SELECT 'XACT_ABORT' AS 'Option', CASE @@options & 16384 WHEN 0 THEN 0 ELSE 1 END AS 'Enabled/Disabled'

The output of the script will give you this:

Next time you are investigating an execution plan, remember to check to see your options are.

Enjoy!

© 2017, John Morehouse. All rights reserved.