The Query Is Fast in SSMS but Slow From the Application

You have been handed a ticket that says the report takes four minutes. You paste the same query into SSMS, it comes back in under a second, and you are now the person who has to explain why the database is innocent. It is not innocent. The query really is slow for the application and really is fast for you, at the same moment, against the same rows.

There is no mystery in it. SQL Server does not cache one plan per query, it caches one plan per query per set of SET options, and SSMS and your application do not connect with the same ones. Two plans, two different shapes, and whichever connection compiled first with whichever parameter value decided what the other one would never see.

First, Make Sure You Are Comparing the Same Thing

Three cheap checks before any of the plan work, because two of them end the investigation.

• Is the application actually running the query you are running? Not the view of it in the ORM, the text that reaches the server. An application almost always sends it parameterised through sp_executesql, and you almost always paste it with literals. Those are two different queries to the optimiser before any SET option is involved. Capture what really arrives with Get Active Sessions and Requests while the slow call is in flight.
• Is it slow, or is it blocked? A query sitting behind someone else’s lock is not slow, it is queued, and no amount of plan work will touch it. The blocking_session_id column settles it in one row, and SQL Server Blocking Troubleshooting is the page for that case.
• Is the application timing out rather than running slowly? “Slow” from a developer often means a 30 second client timeout, which is a different failure with a different message. Execution Timeout Expired covers that one, including the detail that the SSMS query window ships with its execution time-out set to 0, meaning no limit at all. That alone explains a lot of queries that are “fine in SSMS”.

If the application is genuinely getting an answer, just a slow one, and nothing is blocking it, carry on.

The SET Options Really Are Different, and You Can See It

This is not folklore. MS Docs puts it in a warning box on the SET ARITHABORT page: the default for SQL Server Management Studio is ON, while a client connection in an application defaults to OFF, so the two have different cache entries and might get different query plans, which is what makes a poorly performing query hard to troubleshoot.

Run this while both connections are open. One row per session, and the first column after the name is the one that matters:

SELECT  s.session_id,
        s.program_name,
        CAST(s.arithabort AS int)              AS arithabort,
        CAST(s.ansi_warnings AS int)           AS ansi_warnings,
        CAST(s.ansi_nulls AS int)              AS ansi_nulls,
        CAST(s.ansi_padding AS int)            AS ansi_padding,
        CAST(s.quoted_identifier AS int)       AS quoted_identifier,
        CAST(s.concat_null_yields_null AS int) AS concat_null,
        CAST(s.ansi_null_dflt_on AS int)       AS ansi_null_dflt_on
FROM    sys.dm_exec_sessions AS s
WHERE   s.session_id > 50
ORDER BY s.program_name;

Two live connections to the same lab database, one opened by a .NET application with nothing set by hand, one with ARITHABORT ON as a query window gives you:

session_id|program_name  |arithabort|ansi_warnings|ansi_nulls|ansi_padding|quoted_identifier|concat_null|ansi_null_dflt_on
----------|--------------|----------|-------------|----------|------------|-----------------|-----------|------------------
78        |OrdersApp     |0         |1            |1         |1           |1                |1          |1
79        |DbaQueryWindow|1         |1            |1         |1           |1                |1          |1

Seven of the eight columns agree. One does not, and the client library never asked for it either way, which is the point: nobody configured this and nobody can see it from inside the application. The full list of what each option does is on SET statements on MS Docs.

ARITHABORT is the usual culprit but it is not the only one. QUOTED_IDENTIFIER splits the cache the same way, and when a query touches an indexed view or a filtered index it does not just change the plan, it fails outright with SET Options Have Incorrect Settings (Error 1934). If your symptom is an error rather than a slow query, that is the page you want.

The One Query: Does This Query Have More Than One Plan?

Everything above is background. This is the query that decides whether you are in the right place. If Query Store is on, it is the one to run, because it survives a restart, a memory flush and the plan being evicted before you got to the keyboard:

SELECT  q.query_id,
        CAST(cs.set_options AS int)                 AS set_options,
        CASE WHEN CAST(cs.set_options AS int) & 4096 = 4096
             THEN 'ON' ELSE 'OFF' END               AS arithabort,
        p.plan_id,
        CAST(SUM(rs.count_executions) AS int)       AS execs,
        CAST(AVG(rs.avg_logical_io_reads) AS int)   AS avg_logical_reads,
        CAST(MIN(rs.min_logical_io_reads) AS int)   AS min_logical_reads,
        CAST(MAX(rs.max_logical_io_reads) AS int)   AS max_logical_reads
FROM    sys.query_store_query AS q
JOIN    sys.query_context_settings AS cs
        ON cs.context_settings_id = q.context_settings_id
JOIN    sys.query_store_plan AS p
        ON p.query_id = q.query_id
JOIN    sys.query_store_runtime_stats AS rs
        ON rs.plan_id = p.plan_id
WHERE   q.object_id = OBJECT_ID('dbo.usp_OrderTotalByCustomer')
GROUP BY q.query_id, cs.set_options, p.plan_id
ORDER BY q.query_id, p.plan_id;

The lab setup behind the numbers below: one table of 50,000 orders, 48,000 of them belonging to customer 1 and 2 of them to customer 500, a nonclustered index on CustomerId that does not cover the columns being summed, and a procedure taking the customer as a parameter. The application connection called it once with customer 500, then five times with customer 1. The query window called it five times with customer 1 and nothing else.

query_id|set_options|arithabort|plan_id|execs|avg_logical_reads|min_logical_reads|max_logical_reads
--------|-----------|----------|-------|-----|-----------------|-----------------|-----------------
1       |187        |OFF       |1      |6    |120073           |8                |144086
2       |4283       |ON        |2      |5    |852              |852              |852

One procedure. Two query_id values, because Query Store treats identical text under different SET options as different queries. Two plans. The same parameter value, customer 1, costs 144,086 logical reads on the application’s plan and 852 on the query window’s. That is 169 times the work for the same answer, and it is why the stopwatch disagrees with you.

The two set_options values differ by exactly 4096, which is the ARITHABORT bit. That is the whole diff, expressed as a number.

If Query Store Is Off

The plan cache answers the same question, with one large caveat: it only answers while the plans are still cached, and on an instance under memory pressure they may already be gone. sys.dm_exec_plan_attributes on MS Docs documents the attribute:

SELECT  cp.objtype,
        cp.usecounts,
        CAST(pa.value AS int)  AS set_options,
        CASE WHEN CAST(pa.value AS int) & 4096 = 4096
             THEN 'ON' ELSE 'OFF' END AS arithabort,
        OBJECT_NAME(st.objectid, st.dbid) AS object_name
FROM    sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_plan_attributes(cp.plan_handle) AS pa
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE   pa.attribute = 'set_options'
  AND   st.objectid  = OBJECT_ID('dbo.usp_OrderTotalByCustomer')
ORDER BY set_options;

Two rows for one object, with two different set_options, is the same finding. One row means the application and your query window are sharing a plan and this page is not your problem. Get Query Store Top Queries is the wider version of the first query if you do not yet know which procedure to name.

What the Two Plans Are Actually Doing

The SET options split the cache. They do not make a plan bad. What makes a plan bad is which parameter value happened to be in the room when it compiled, and with the cache split you get two separate rolls of that dice. SET SHOWPLAN_TEXT ON, then EXEC dbo.usp_OrderTotalByCustomer once for each value, shows both plans without running either. Customer 500, the one with 2 rows, gets a seek and a lookup. Customer 1, the one with 48,000 rows, gets a scan:

-- customer 500, 2 rows
|--Nested Loops(Inner Join, OUTER REFERENCES:([o].[OrderId]))
     |--Index Seek(OBJECT:([Lab1001_fast].[dbo].[Orders].[IX_Orders_CustomerId] AS [o]),
                   SEEK:([o].[CustomerId]=[@CustomerId]) ORDERED FORWARD)
     |--Clustered Index Seek(OBJECT:([Lab1001_fast].[dbo].[Orders].[PK__Orders__C3905BCF20830621] AS [o]),
                   SEEK:([o].[OrderId]=[o].[OrderId]) LOOKUP ORDERED FORWARD)

-- customer 1, 48,000 rows
|--Clustered Index Scan(OBJECT:([Lab1001_fast].[dbo].[Orders].[PK__Orders__C3905BCF20830621] AS [o]),
                   WHERE:([o].[CustomerId]=[@CustomerId]))

Both plans are correct for the value they were built for. The damage is reuse. Compile on customer 500, then run customer 1 on that plan, and the server performs 48,000 individual lookups into the clustered index because it was told to expect 2. SET STATISTICS IO ON prices it, all three statements in one session so the plan is genuinely reused: 8 logical reads for the rare customer that compiled the plan, 144,086 for the hot customer on that same plan, and 852 once WITH RECOMPILE lets it build its own.

Logical reads rather than seconds on purpose. Seconds depend on what else the box was doing, while 144,086 against 852 is the same number on your hardware and on mine. The mechanism underneath is explained properly in Parameter Sniffing in SQL Server, which walks the compile-and-reuse cycle with its own worked example. This page is about why you cannot see it from SSMS. That one is about why it happens at all.

Do Not Fix This by Changing SSMS

Somebody will suggest putting SET ARITHABORT OFF at the top of your query window so you see what the application sees, or the reverse, adding SET ARITHABORT ON to the application’s connection so the problem goes away. Be clear about what each one does.

• Setting ARITHABORT OFF in your query window is a diagnostic, and a good one. It puts you in the application’s cache entry so you can reproduce the slow run and look at the real plan. Do it, measure, then take it back out. It fixes nothing and it is not meant to.
• Setting ARITHABORT ON in the application moves the problem, it does not solve it. The application now shares a cache entry with every query window on the estate, which means the next time a DBA runs that procedure with an unusual parameter at 2am, the application inherits the plan that gets compiled. You have swapped a predictable bad plan for an unpredictable one.
• Neither of them is the cause. The cause is one query that needs two different plans for two different parameter values, and that is still true after you have made every connection agree. The SET option difference is what hid it from you, not what created it, and the fixes that do address it are next.

The Fixes, in the Order Worth Trying

All of these were measured against the same procedure and the same 48,000 row customer, so the numbers compare directly with the 144,086 above. Logical reads, because this box cannot measure duration honestly:

1. Find out whether it regressed, before changing anything. If this used to be fast for the application and is not any more, a plan changed, and Query Store knows when. Get Query Store Regressions and Forced Plans lists the queries that have more than one plan with materially different runtimes, which is this exact shape, plus anything already forced on the instance. Start there rather than guessing which of the fixes below you need.

2. Check the statistics first. A plan compiled against a row estimate that was true last quarter is the same failure with a different cause, and it costs nothing to rule out. Get Statistics Health gives the last update and the modification counter per statistic. If the estimate is wrong because the statistics are stale, none of the hints below are the right answer.

3. RECOMPILE, when the compile is cheaper than the bad plan. EXEC ... WITH RECOMPILE on the call, or OPTION (RECOMPILE) on the statement inside the procedure, which is usually what you want because it recompiles one statement rather than the whole object. Measured above: 852 reads instead of 144,086. The cost is a compile on every execution, which is real but small next to three orders of magnitude of reads. It is the right first move on a procedure called hundreds of times a day, not millions.

4. OPTIMIZE FOR a value you choose. If one plan suits almost all the traffic, name the value that produces it and stop letting the first caller decide:

SELECT COUNT_BIG(*) AS Orders, SUM(o.Amount) AS Total, MAX(o.OrderDate) AS LastOrder
FROM   dbo.Orders AS o
WHERE  o.CustomerId = @CustomerId
OPTION (OPTIMIZE FOR (@CustomerId = 1));

852 either way, including for the customer with 2 rows, which previously cost 8. That is the trade, written down: the rare call gets about 100 times worse so the common call stops being 169 times worse. Say that out loud before you ship it, because it is the thing people forget they chose. Query hints on MS Docs has the syntax for both forms.

And measure OPTIMIZE FOR UNKNOWN rather than reaching for it. It is the hint most often recommended for this symptom, and on this data it was the worst option of the set. It ignores the parameter and estimates from the average density of the column, which here produced the seek and lookup plan and then ran it against 48,000 rows. It read 14 logical pages compiling on the rare customer.

Then 152,537 reads on the hot customer, which is worse than doing nothing. On skewed data the average is a value that does not exist, and a plan built for a customer who does not exist suits nobody. On evenly distributed data it behaves well. The only way to know which you have is to run it.

5. Force the plan, when you need it stable today. Query Store will pin a specific plan to a specific query and keep pinning it through recompiles and restarts:

EXEC sys.sp_query_store_force_plan @query_id = 1, @plan_id = 12;

SELECT  p.plan_id, p.is_forced_plan, p.last_force_failure_reason_desc
FROM    sys.query_store_plan AS p
WHERE   p.query_id = 1
ORDER BY p.plan_id;

That second query comes back with is_forced_plan 1 for plan 12 and 0 for plan 1, and last_force_failure_reason_desc NONE on both. After that, the application’s connection ran 852 reads for both the rare and the common customer, with no change to the application and no change to the procedure. Two things to hold on to. Check last_force_failure_reason_desc afterwards and periodically, because a forced plan that can no longer be produced, say because somebody dropped the index it used, silently stops being forced. And forcing is a splint, not a repair, so write down the query, the plan and the date, because in two years nobody will remember why that plan is pinned. Monitoring performance by using the Query Store on MS Docs covers forcing and its failure modes.

On SQL Server 2022 and later there is also Parameter Sensitive Plan optimisation, which lets the engine cache more than one plan for the same statement and pick between them by parameter value. It is on by default at compatibility level 160 and it addresses exactly this shape of problem, for the subset of queries that qualify. It is worth knowing about before you hand-tune something the engine may already be handling. See Parameter Sensitive Plan optimization on MS Docs, and Compatibility Levels: What Actually Changes for what else moves when you raise the level.

The Other Half of This Symptom: an nvarchar Parameter on a varchar Column

Same complaint, completely different cause, and it is worth checking early because it is quicker to prove. You type a search by reference code in SSMS and it returns instantly. The application runs what looks like the same query and scans the table. Nothing to do with ARITHABORT.

A .NET string parameter is sent as nvarchar unless somebody set the type by hand. A column declared varchar cannot be seeked with an nvarchar predicate, because nvarchar has the higher data type precedence, so the column gets converted instead of the parameter and the index is no longer usable. The literal you typed in SSMS was a varchar, so it seeked.

Pass the value and nothing else, as $c.Parameters.AddWithValue("@code", "CUST000500") does, and the client library picks the type for you. It picked SqlDbType.NVarChar, size 10. Query Store then recorded the text SQL Server actually received as (@code nvarchar(10))SELECT CustomerId, CustomerName FROM dbo.Customer WHERE CustomerCode = @code, against the two forms typed by hand with the literals N'CUST000500' and 'CUST000500'.

The plans for those two hand-typed forms, against a varchar(20) column with its own nonclustered index. The difference is one letter N:

-- varchar literal, what you typed in SSMS
|--Index Seek(OBJECT:([Lab1001_fast].[dbo].[Customer].[IX_Customer_CustomerCode]),
              SEEK:([Customer].[CustomerCode]='CUST000500') ORDERED FORWARD)

-- nvarchar parameter, what the .NET client sends
|--Index Scan(OBJECT:([Lab1001_fast].[dbo].[Customer].[IX_Customer_CustomerCode]),
              WHERE:(CONVERT_IMPLICIT(nvarchar(20),[Customer].[CustomerCode],0)=N'CUST000500'))

Seek becomes scan, and CONVERT_IMPLICIT wrapped around the column is the fingerprint to search for in any plan. On this 12,847 row table it cost 4 logical reads against 43. On a table with ten million rows it is the difference between a lookup and reading the whole index, every call. Get Implicit Conversions finds every cached plan on the instance carrying one, which is the right way to answer “where else is this happening”. Data type precedence on MS Docs is the rule that decides which side gets converted.

The fix is in the application, not the database: set the parameter type explicitly to match the column. Changing the column to nvarchar instead is a schema change that touches every index on it, and is rarely the cheaper option.

When It Is Not SQL Server at All

Two cases where the plans are identical, the reads are identical, and the application is still slower. Rule them in or out before you spend a day on hints.

• The application is not waiting on the query, it is waiting on itself. A result set returned row by row through a client loop, or an ORM materialising 40,000 objects, spends its time after SQL Server has finished. The tell is that the server-side duration is short while the user-side duration is long. Get Query Performance Deep-Dive gives you the server’s own figure for that statement to compare against the application’s stopwatch.
• It is a timeout, not a slow query. If the application dies at a suspiciously round number of seconds and SSMS never does, that is the client’s command timeout, and SSMS has its own set to 0 by default so it will never hit one. Execution Timeout Expired covers which timer belongs to whom, what SQL Server records when a client gives up, which is almost nothing, and why raising the number is usually the wrong move.

Common Questions