Have you ever 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 setting. The options represent the SET values of the current session. SET options can affect how the query is executed 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.
Options
| Value |
Configuration
|
|
1
|
DISABLE_DEF_CNST_CHK |
|
2
|
IMPLICIT_TRANSACTIONS |
|
4
|
CURSOR_CLOSE_ON_COMMIT |
|
8
|
ANSI_WARNINGS |
|
16
|
ANSI_PADDING |
|
32
|
ANSI_NULLS |
|
64
|
ARITHABORT |
|
128
|
ARITHIGNORE |
|
256
|
QUOTED_IDENTIFIER |
|
512
|
NOCOUNT |
|
1024
|
ANSI_NULL_DFLT_ON |
|
2048
|
ANSI_NULL_DFLT_OFF |
|
4096
|
CONCAT_NULL_YIELDS_NULL |
|
8192
|
NUMERIC_ROUNDABORT |
|
16384
|
XACT_ABORT |
For example, the rightmost 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, and 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 what your options are.
Enjoy!