Session SET Options
Overview
The oldest complaint in the profession: it is fast in Management Studio and slow from the application. That is usually true, it is almost never about the tool, and no ranked report can show why.
A compiled plan is cached under the session’s SET options as well as under the statement text. Management Studio connects with ARITHABORT ON; most client libraries connect with it OFF. So the same statement text from the two is compiled twice, cached twice, and free to be on two different plans at two different speeds. Every ranked report adds those copies together and shows the average of a quick query and a slow one.
Query Store has kept the evidence the whole time, in sys.query_context_settings. This report reads it and shows:
- One statement, two copies, and one of them slower. The real thing.
- Settings that make part of the schema unavailable. A filtered index, an indexed view and an index on a computed column can only be used, and only written to, by a session with seven options set the documented way.
- The plain waste. Every extra copy is a compilation and a cache entry nobody asked for.
What a copy is
A copy of a statement is the same statement text from the same object compiled under one set of session settings. The settings are:
- The SET options, stored as a bit mask in
set_options: ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_WARNINGS, ANSI_PADDING, CONCAT_NULL_YIELDS_NULL, NUMERIC_ROUNDABORT, and a few more. - The language, date format and first day of the week.
- The default schema the name resolution started from.
- Internal status flags Query Store keeps and does not document. Two copies can differ only there, and the page says so with both values rather than calling them the same.
The report compares the copy most executions used against the copy that behaves least like it.
Where to find it
In the tree, under a database, Real Time → Query Store → Session SET Options.
The report works on SQL Server 2016 and newer. The Query Store folder is hidden on older versions and on master and tempdb.
Requirements
| Requirement | Why |
|---|---|
| SQL Server 2016 or newer | Query Store and sys.query_context_settings arrived in SQL Server 2016. |
| Query Store on for the database | The statements and their settings live inside Query Store. |
VIEW DATABASE STATE |
To read the Query Store catalog views. |
The toolbar
| Control | What it does |
|---|---|
| Split statements / Session settings | The grid lists either the statements compiled more than once, or every set of session settings in the window. |
| 24 h / 3 d / 7 d / 30 d | The window. |
| Every copy / 5+ runs / 25+ runs / 100+ runs | How many executions a copy needs before it counts. The default is 5: a statement the service runs ten thousand times that somebody once ran by hand is not a split workload. |
| Turn Query Store on | Only when Query Store is off and a setting would fix it. |
| Refresh | Re-reads Query Store. |
Reading the chart
At the top, a verdict names the worst finding. Five tiles carry the totals:
| Tile | What it is |
|---|---|
| Session settings in use | Distinct sets of session settings in the window. Each is its own plan cache key. Click to switch the grid to the settings. |
| Statements compiled twice | Statements with more than one copy that cleared the floor. Click to go back to the statements. |
| Extra compilations | Copies past the first, which is the cache waste. |
| Time in the slower copy | Time that would not have been spent if every run had got the fastest copy. Click to show only the statements with a slower copy. |
| Cannot use filtered indexes | Sets of settings that break the rule for filtered indexes, indexed views and computed column indexes, and whether this database has any. Click to list them. |
Under the tiles, one row per split statement:
- Each mark is one copy, placed at that copy’s average duration.
- The ring is the copy most executions used, and each disc is a copy compiled under other settings. They are different shapes so the chart still reads in black and white.
- The bar joins the copies. Its length is the finding.
- The axis is logarithmic. The length of a bar is how many times apart the copies are, so 10x apart is the same length on a statement that takes a millisecond and on one that takes a minute.
- The bar’s color is the finding: red for a slower copy, amber for a copy that cannot use filtered indexes, blue for copies at the same speed, gray for a split on something unpublished.
- The note on the right says how far apart the copies are, and the time lost.
Hover over a row for every copy’s settings and run time.
Reading the grid
Split statements
| Column | What it is |
|---|---|
| # | Position, worst finding first, then by total duration. |
| Finding | One copy slower, a copy cannot use filtered indexes, split on something unpublished, or compiled N times at the same speed. |
| Differs by | What separates the copy most runs used from the odd one out, for example “ARITHABORT off in settings 1, on in settings 4”. |
| Time lost | Runs on the slower copies x how much slower each was than the fastest. |
| Apart | Slowest over fastest. |
| Copies in detail | Every copy’s settings id, runs and average. |
| Fastest copy / Slowest copy | Average duration of each. |
| Runs | Regular executions across the copies. |
| Copies | Copies that cleared the floor. |
| Object | The procedure, function or trigger, when the statement belongs to one. A procedure appears here when it was altered under different settings. |
| Statement | The statement text, with the parameter list Query Store prefixes removed. |
The Copies in detail column takes the width the others leave, and hovering over a row shows every column in full.
A statement is slower when one copy averages at least twice another and the time lost is a second or more.
Session settings
| Column | What it is |
|---|---|
| Settings | The context_settings_id. |
| SET options | The options that are unusual: the ones that are off, and NUMERIC_ROUNDABORT or FORCEPLAN when on. “all as expected” is Management Studio’s settings. |
| Also | Language, date format, first day of the week, default schema, and context flags such as trigger. |
| Filtered indexes | Whether a session with these settings can use a filtered index, and if not, which options are wrong. |
| Statements / Runs / Plans | What ran under these settings. |
| Share of time | Their share of the window’s duration. |
| set_options | The mask as Query Store stores it. |
| Internal status | Query Store’s undocumented status flags for the row. |
| Last run (UTC) | The most recent execution. |
Double-click a row to see the statement with every copy decoded, or the settings with every option spelled out. Right-click for:
| Action | What it does |
|---|---|
| Explain these copies | Every copy’s settings, runs, average, CPU and reads. |
| Explain these settings | Every option on or off, with the ones the index rule wants the other way marked. |
| Show the statement | The statement in the query window. |
| Copy a query listing the sessions connected with each set of options | A read only query against sys.dm_exec_sessions for the programs connected now and their options. Copied, never run. |
| Go to Parameter Sensitive Plans / Plan Regressions | For a statement with a slower copy. |
| Copy query text | The statement, without the parameter list. |
| Copy the query behind this report | The whole batch, ready to run in SSMS. |
Where the data comes from
| Source | What it gives |
|---|---|
sys.query_context_settings |
The settings behind each compiled copy. |
sys.query_store_query |
The copy: statement text, object and settings together. |
sys.query_store_runtime_stats |
Executions, duration, CPU and reads per plan per interval. |
sys.query_store_runtime_stats_interval |
The window boundaries. |
sys.indexes, sys.index_columns, sys.columns |
Filtered indexes, indexed views and computed column indexes in the database. |
The index rule
A filtered index, an indexed view and an index on a computed column need ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL and QUOTED_IDENTIFIER on and NUMERIC_ROUNDABORT off. A session with one of them wrong does not get a warning while reading: the index is silently not considered. Its first insert, update or delete against a table carrying one fails with error 1934.
ARITHABORT off does not count on its own while ANSI_WARNINGS is on. At compatibility level 90 and above, which is every level SQL Server 2016 supports, ANSI_WARNINGS ON makes ARITHABORT count as on for this rule. This was checked: an insert into a table with a filtered index succeeded from a session with ARITHABORT OFF and ANSI_WARNINGS ON, and failed with error 1934 once ANSI_WARNINGS was OFF. Without this, every ordinary .NET connection would be reported as unable to use the database’s filtered indexes.
How the numbers are made
Regular executions only, and a copy’s duration is its total over the window divided by its executions. Aborted and failed executions are counted in the footer.
Query Store keeps averages per interval, not single executions, so the spread inside a copy is not drawn. A copy that is slower is still clearly separated from one that is not.
The window ends at the last completed interval and is bounded at both ends.
Messages you may see
A statement is being compiled 2 times, and one copy runs 238x slower than another. The copies differ in their settings and one of them is on a worse plan. Changing ARITHABORT in the application rarely fixes it; the slower copy’s plan, often compiled for a different parameter value, is the problem.
N sets of session settings cannot use the indexes this database has. Those sessions ignore the filtered indexes, indexed views or computed column indexes, and their writes to those tables fail.
N statements are being compiled more than once for the same text. The copies cost the same today. The fix is a line in the connection string.
N sets of session settings break the rule for filtered indexes. This database has none of those indexes yet, so nothing is failing.
Everything that ran arrived with the same session settings. Nothing is compiled twice.
Related reports
| Report | Why you would go there |
|---|---|
| Parameter Sensitive Plans | When the slower copy was compiled for a different parameter value. |
| Plan Regressions | To force the faster plan for the slower copy’s query. |
| Plan Differences | To see what is different inside two plans of one query. |
| One Time Use Queries | When the plan cache is full of copies nobody reuses. |
| Needs Parameters | When ad hoc text is what splits a statement into many copies. |
Frequently asked questions
Should I set ARITHABORT ON in the application? Usually not. The copies being different is what exposed the problem; the slower copy’s plan is the problem. Clear that plan from the cache or force the good plan, and check whether the statement is parameter sensitive.
Two copies have identical settings. Why are they two copies? They differ in Query Store’s internal status flags, which the page shows in the Internal status column. A statement run from inside a trigger, or through a different kind of batch, can be compiled separately that way.
Why do I see QUOTED_IDENTIFIER off? sqlcmd and some older tools connect with it off unless told otherwise, and so do objects created from those tools. Those sessions cannot use or write to filtered indexes and indexed views.
Why a log scale? The statements on one page run from microseconds to minutes. On a linear axis every row but the slowest would sit at zero.