Inpart 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.
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.
SET options in sys.dm_exec_requests and sys.dm_exec_sessions
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.
SET options in sys.dm_exec_plan_attributes
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 provides the info too in the additional_info column, but it’s in XML format.
EXEC sp_WhoIsActive @get_additional_info = 1
SET options in sp_WhoIsActive
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.
SET options in an execution plan
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.
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 );
SET options in an XE session
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.
SET options in Profiler
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 outBrent’s post.
Before you start testing, you need to be aware of the density vector issue. I’ll cover that topic in the next and final part of this series.