In part 1 of this series, I showed you how to get the compiled parameter values for an execution plan that you are investigating. You’ve identified it as a bad execution plan. You’ve already fixed the production emergency by removing the bad plan from the plan cache. You’re almost ready to start testing for a workaround or a solution to avoid this issue in the future.
Before you start testing, you have to get your SSMS window set to the same options as the application.
Getting the App’s SET Options
Most applications use a database driver that has ARITHABORT set to off. SSMS has ARITHABORT set to on. Because of this difference, you get a different execution plan than what the application is using. This is why you could be troubleshooting a performance issue in production where the query is fast for you in SSMS, yet it is timing out when the application runs the same query. Erland has a really good article on this: Slow in the Application, Fast in SSMS?
It’s easy to fix: just get your SSMS SET options the same as the application. For most applications, all you need to do is set ARITHABORT to off.
But I can never remember which setting is different, so I always check which options are being used by an application session and then do the same in my SSMS query window.
What are the different ways we can check which settings are being used by a session?
There are easy ways to see your own current settings, such as via DBCC USEROPTIONS, but this post is about getting the settings the application is using.
Various DMVs
The SET options can be queried via various DMVs: sys.dm_exec_sessions, sys.dm_exec_requests and sys.dm_exec_plan_attributes.
sys.dm_exec_sessions and sys.dm_exec_requests have the data in bit columns, one column per setting.
sys.dm_exec_plan_attributes has the data in a single column called set_options. You have to use bitwise operators to figure out which ones are set.
The way it is formatted does not give me a quick copy/paste script. A quick Google search didn’t lead me to an already-built script (drop a comment if you have/find one). Even if I could find a script, I may not always have access to it or be able to find it. I need something quick when I’m working on a production issue.
sp_WhoIsActive
sp_WhoIsActive provides the info too in the additional_info column, but it’s in XML format.
|
1 |
EXEC sp_WhoIsActive @get_additional_info = 1 |
sp_BlitzCache
If you scroll all the way to the right in the sp_BlitzCache output, you’ll see a “SET Options” column.
It’s not clickable like it is in sp_WhoIsActive, but you can copy/paste the output to a new window. There are a few problems with this output:
- It’s not clickable like it is in sp_WhoIsActive. You can copy/paste the output, but it’s not in a format that you can just run.
- It’ll only show you the options that are on. You’d have to know which ones to turn off.
- You’re not sure if this is the plan that the application is using, though the “# Executions” would give you a clue.
It’s in the execution plans too!
If you’ve got the execution plan that the application is using, you can see the SET options in the plan’s properties. Click on the root operator and either hit F4 or go to Properties from the right-click menu. Expand the “Set Options” section.
Extended Event Session
There are two events in an XE session that contain the data: login and existing_connection. The trick is that you have to enable collect_options_text to see the data, otherwise the options_text column is blank.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE EVENT SESSION SetOptions ON SERVER ADD EVENT sqlserver.login(SET collect_options_text=(1) ACTION( sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.session_id, sqlserver.username ) ) ADD TARGET package0.ring_buffer WITH ( MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=ON, STARTUP_STATE=OFF ); |
SQL Profiler/Trace
In my opinion, using SQL Profiler is the easiest and possibly the fastest way to get the SET options. The data is available in the “Audit Login” and “ExistingConnection” events. I always just use the ExistingConnection event so that I don’t have to wait for a new session to connect. I start the trace and then immediately stop it.
You can also do a server-side trace, SQL Trace, but that just complicates things since I’d need to output to a file and then read it.
Why not just change SSMS?
You could modify your SSMS settings to match the application’s, but I don’t ever do that. An application could be using different options than the default for the database driver it is using. Plus, changing SSMS wouldn’t give me the right options for execution plans from an Agent job, SQLCMD or bcp. Those have QUOTED_IDENTIFIER set to OFF.
So many different ways to check SET options
I’m sure I’m missing some ways to check the SET options of a session. Leave a comment with what I’m missing. Maybe you can even convince me not to use Profiler to get this info but probably not! PROFILER 4 LYFE!
For more information on troubleshooting Parameter Sniffing issues, check out Brent’s post.
What’s Next?
You still aren’t quite ready to start testing yet. Next up:
24 Comments. Leave new
Sorry if it was obvious, but I am confused, at the top it is stated to set ARITHABORT on ssms the same as the application,
but at the bottom (why not just change ssms) it says not to change ssms?
Or should i only temporarily disable ARITHABORT when troubleshooting queries?
Thanks in advance,
Randi
It doesn’t say not to change SSMS. It says I don’t. You could match your SSMS SET settings to your application if that’s the only application type you’ll be working on. I prefer to not do that as I’m not confident that I won’t encounter a different driver that will have something else set that my SSMS settings aren’t set to. Thus, I only make this change in a specific SSMS query window when I am specifically working on parameter sniffing issues. But you definitely could make this change in SSMS.
This is the script I’ve compiled from SQL’s online resources (https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-plan-attributes-transact-sql), and saved as a snippet (Red-Gate) for interpreting set_options:
,CASE WHEN [cs].[set_options] & 1 = 1 THEN ‘Y’ ELSE ‘N’ END AS [ANSI_PADDING]
,CASE WHEN [cs].[set_options] & 2 = 2 THEN ‘Y’ ELSE ‘N’ END AS [Parallel_Plan]
,CASE WHEN [cs].[set_options] & 4 = 4 THEN ‘Y’ ELSE ‘N’ END AS [FORCEPLAN]
,CASE WHEN [cs].[set_options] & 8 = 8 THEN ‘Y’ ELSE ‘N’ END AS [CONCAT_NULL_YIELDS_NULL]
,CASE WHEN [cs].[set_options] & 16 = 16 THEN ‘Y’ ELSE ‘N’ END AS [ANSI_WARNINGS]
,CASE WHEN [cs].[set_options] & 32 = 32 THEN ‘Y’ ELSE ‘N’ END AS [ANSI_NULLS]
,CASE WHEN [cs].[set_options] & 64 = 64 THEN ‘Y’ ELSE ‘N’ END AS [QUOTED_IDENTIFIER]
,CASE WHEN [cs].[set_options] & 128 = 128 THEN ‘Y’ ELSE ‘N’ END AS [ANSI_NULL_DFLT_ON]
,CASE WHEN [cs].[set_options] & 256 = 256 THEN ‘Y’ ELSE ‘N’ END AS [ANSI_NULL_DFLT_OFF]
,CASE WHEN [cs].[set_options] & 512 = 512 THEN ‘Y’ ELSE ‘N’ END AS [NoBrowseTable]
,CASE WHEN [cs].[set_options] & 1024 = 1024 THEN ‘Y’ ELSE ‘N’ END AS [TriggerOneRow]
,CASE WHEN [cs].[set_options] & 2048 = 2048 THEN ‘Y’ ELSE ‘N’ END AS [ResyncQuery]
,CASE WHEN [cs].[set_options] & 4096 = 4096 THEN ‘Y’ ELSE ‘N’ END AS [ARITH_ABORT]
,CASE WHEN [cs].[set_options] & 8192 = 8192 THEN ‘Y’ ELSE ‘N’ END AS [NUMERIC_ROUNDABORT]
,CASE WHEN [cs].[set_options] & 16384 = 16384 THEN ‘Y’ ELSE ‘N’ END AS [DATEFIRST]
,CASE WHEN [cs].[set_options] & 32768 = 32768 THEN ‘Y’ ELSE ‘N’ END AS [DATEFORMAT]
,CASE WHEN [cs].[set_options] & 65536 = 65536 THEN ‘Y’ ELSE ‘N’ END AS [LanguageID]
,CASE WHEN [cs].[set_options] & 131072 = 131072 THEN ‘Y’ ELSE ‘N’ END AS [UPON]
,CASE WHEN [cs].[set_options] & 262144 = 262144 THEN ‘Y’ ELSE ‘N’ END AS [ROWCOUNT]
Nice! That could easily be modified to output the SET commands so that we can setup our environment to match that of the execution plan, but I’m too lazy to write it. Haha!
Angela, here is one I wrote a while back that does the same kind of thing:
DECLARE @options TABLE ([name] nvarchar(35), [minimum] int, [maximum] int, [config_value] int, [run_value] int)
DECLARE @optionsCheck TABLE([id] int NOT NULL IDENTITY, [setting_name] varchar(128))
DECLARE @current_value INT;
INSERT INTO @options ([name], [minimum], [maximum], [config_value], [run_value])
EXEC sp_configure ‘user_options’;
SELECT @current_value = [config_value] FROM @options;
INSERT INTO @optionsCheck
([setting_name])
VALUES
(‘DISABLE_DEF_CNST_CHK’),
(‘IMPLICIT_TRANSACTIONS’),
(‘CURSOR_CLOSE_ON_COMMIT’),
(‘ANSI_WARNINGS’),
(‘ANSI_PADDING’),
(‘ANSI_NULLS’),
(‘ARITHABORT’),
(‘ARITHIGNORE’),
(‘QUOTED_IDENTIFIER’),
(‘NOCOUNT’),
(‘ANSI_NULL_DFLT_ON’),
(‘ANSI_NULL_DFLT_OFF’),
(‘CONCAT_NULL_YIELDS_NULL’),
(‘NUMERIC_ROUNDABORT’),
(‘XACT_ABORT’)
SELECT fn.[value],
oc.[setting_name],
[server_option] = CASE WHEN (@current_value & fn.[value]) = fn.[value] THEN ‘ON’ ELSE ‘-‘ END,
[client_option] = CASE WHEN (@@options & fn.[value]) = fn.[value] THEN ‘ON’ ELSE ‘-‘ END
FROM @optionsCheck oc
CROSS APPLY (
SELECT [value] = CASE WHEN oc.id > 1 THEN POWER(2, oc.id – 1) ELSE 1 END
) fn
I’ll start off by saying I thoroughly enjoy the work you guys do, and there’s a ton of knowledge that is shared here that I appreciate, but I have to disagree with the recommendation of setting ARITHABORT to OFF. This is in fact the exact opposite of what Microsoft recommends:
https://docs.microsoft.com/en-us/sql/t-sql/statements/set-arithabort-transact-sql
The problem is usually .Net developers don’t really understand how to fix that setting in their connection strings. You can configure an instance to default connections to ARITHABORT ON for when the application doesn’t specify this setting, using SSMS server properties, connections page, arithmetic abort setting:
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-properties-connections-page
You missed the entire point of this blog post. I am not saying or recommending to turn on or off any setting. I am saying that in order to troubleshoot a parameter sniffing issue, you must match the application’s SET options or you could get a different execution plan. Most database drivers have it set to off, and thus I must match that setting in my query window where I am actively troubleshooting a parameter sniffing issue.
I didn’t miss the point, I understand they should be the same to compare, but having them set to the value that Microsoft says not to use because it leads to bad performance seems like a poor way to troubleshoot the query.
First step is reproducing the issue, which you can’t do if your settings don’t match. Then test various things. Nowhere am I recommending arithabort be off or on in this blog post. Arithabort happens to be the setting that differs between database drivers and SSMS, which is the only reason why it was mentioned here.
Microsoft indicates, “When *troubleshooting* queries with Management Studio always match the client ARITHABORT setting.” This is on the same page starting this discussion (i.e., https://docs.microsoft.com/en-us/sql/t-sql/statements/set-arithabort-transact-sql), and this agrees with the spirit of Tara’s article.
This is a script I use to list the options that are set for my session:
/*
SQL Server offers many built in metadata functions and one of these functions, @@OPTIONS which
allows you to get the current values that are set for the current session. When each connection is made
the default values are established for each connection and remain set unless they are overridden by some
other process.
*/
DECLARE @options INT
SELECT @options = @@OPTIONS
PRINT @options
IF ( (1 & @options) = 1 ) PRINT ‘DISABLE_DEF_CNST_CHK’
IF ( (2 & @options) = 2 ) PRINT ‘IMPLICIT_TRANSACTIONS’
IF ( (4 & @options) = 4 ) PRINT ‘CURSOR_CLOSE_ON_COMMIT’
IF ( (8 & @options) = 8 ) PRINT ‘ANSI_WARNINGS’
IF ( (16 & @options) = 16 ) PRINT ‘ANSI_PADDING’
IF ( (32 & @options) = 32 ) PRINT ‘ANSI_NULLS’
IF ( (64 & @options) = 64 ) PRINT ‘ARITHABORT’
IF ( (128 & @options) = 128 ) PRINT ‘ARITHIGNORE’
IF ( (256 & @options) = 256 ) PRINT ‘QUOTED_IDENTIFIER’
IF ( (512 & @options) = 512 ) PRINT ‘NOCOUNT’
IF ( (1024 & @options) = 1024 ) PRINT ‘ANSI_NULL_DFLT_ON’
IF ( (2048 & @options) = 2048 ) PRINT ‘ANSI_NULL_DFLT_OFF’
IF ( (4096 & @options) = 4096 ) PRINT ‘CONCAT_NULL_YIELDS_NULL’
IF ( (8192 & @options) = 8192 ) PRINT ‘NUMERIC_ROUNDABORT’
IF ( (16384 & @options) = 16384 ) PRINT ‘XACT_ABORT’
That’s a nice script, I tweaked it to print all the SET options, including the OFF ones, in case you need to copy-paste one window’s settings into another window 🙂
/*
SQL Server offers many built in metadata functions and one of these functions, @@OPTIONS which
allows you to get the current values that are set for the current session. When each connection is made
the default values are established for each connection and remain set unless they are overridden by some
other process.
*/
DECLARE @options INT;
SELECT @options = @@OPTIONS;
PRINT (‘–Options = ‘ + CONVERT(VARCHAR(10),@options));
PRINT ‘SET DISABLE_DEF_CNST_CHK ‘ + CASE WHEN 0 < 1 & @options THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET IMPLICIT_TRANSACTIONS ' + CASE WHEN 0 < 2 & @options THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET CURSOR_CLOSE_ON_COMMIT ' + CASE WHEN 0 < 4 & @options THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET ANSI_WARNINGS ' + CASE WHEN 0 < 8 & @options THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET ANSI_PADDING ' + CASE WHEN 0 < 16 & @options THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET ANSI_NULLS ' + CASE WHEN 0 < 32 & @options THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET ARITHABORT ' + CASE WHEN 0 < 64 & @options THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET ARITHIGNORE ' + CASE WHEN 0 < 128 & @options THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET QUOTED_IDENTIFIER ' + CASE WHEN 0 < 256 & @options THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET NOCOUNT ' + CASE WHEN 0 < 512 & @options THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET ANSI_NULL_DFLT_ON ' + CASE WHEN 0 < 1024 & @options THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET ANSI_NULL_DFLT_OFF ' + CASE WHEN 0 < 2048 & @options THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET CONCAT_NULL_YIELDS_NULL '+ CASE WHEN 0 < 4096 & @options THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET NUMERIC_ROUNDABORT ' + CASE WHEN 0 < 8192 & @options THEN 'ON' ELSE 'OFF' END + ';';
PRINT 'SET XACT_ABORT ' + CASE WHEN 0 < 16384& @options THEN 'ON' ELSE 'OFF' END + ';';
YOU’RE MY HERO!
Pleased to help ya! (also, I just like swimming in T-SQL)
On a side note, it would be brilliant if there was a way for the comments section to not auto-butcher T-SQL by changing “SQL” quotes to “wordy” quotes…
Indeed!
I can’t believe I’ve found an answer to why queries run faster in SSMS vs the App when troubleshooting parameter sniffing issues. Part 3 of this series is just as helpful too. Thank you!!!
Tara, I wanted to point out something that I learned through trial and error. You mentioned that clients often have ARITHABORT turned off. This is sort of true. For .net code that uses a sqlconnection the some of the set options are actually pulled from the server user_options. If you turn on your ARITHABORT ON at the server level then all .net connections from there on follow suit. I do not know if other connections like Java follow the same pattern, but I have proved it to be true with .net.
Now that’s interesting!
Here is the script i wrote to change the server options, meant to supply that before:
— NOTE: By enabling this at the instance level all .net clients will automatically start connecting with using SET ARITHABORT ON
DECLARE @options TABLE ([name] nvarchar(35), [minimum] int, [maximum] int, config_value] int, [run_value] int);
DECLARE @Value INT;
INSERT INTO @options ([name], [minimum], [maximum], [config_value], [run_value])
EXEC sp_configure ‘user_options’;
SELECT @Value = [config_value] | 64
FROM @options;
EXEC sp_configure ‘user_options’, @Value;
RECONFIGURE;
SELECT * FROM @options; — prior state
EXEC sp_configure ‘user_options’; — current state
Tara, feel free to blog about it once you verify it. I don’t blog and I have never seen a post out there about this. You don’t even have to credit me if you don’t want to. 🙂
I just might, but I’d totally give you credit. I need to do some research on that user_option settings. I knew it existed but never have used it. I’ve never seen it in use either.
[…] Many individuals have blogged up to now how having ARITHABORT OFF in your connection settings can adversely have an effect on your question efficiency and produce unhealthy plans. Erland Sommarskog has a really effectively written article: Gradual within the Utility, Quick in SSMS?. My co-worker Daniel Berber was talked about on this article alerting Erland to the face that ARITHABORT ought to all the time be on. Tara Kizer talks about it on this article: Troubleshooting Parameter Sniffing Points the Proper Method: Half 2. […]
[…] This is only one method, there are other methods. My second option is usually to drop into the DMVs – but others exist beyond that. Tara Kizer jumps into some of those other methods here. […]
Hello all you happy people. I’ve researched this to a point where I don’t even want to talk about it anymore.
1. If SET ARITHABORT OFF (or any user_options) is merely passed by a connection, it will override the instance default.
2. ANSI_WARNINGS implicitly sets ARITHABORT ON, however it does not solve the bizarre plan choices QO (Query Optimizer) makes.
The Upshot:
Going forward I’ll include a step that handles default connection settings in all of my future installs of SQL Server. But keep in mind, the app/protocol that is connecting to SQL can potentially override this. Joy. Have Fun.