Slow in the app, fast in SSMS

In one line: the application and SSMS connect with different SET options (usually ARITHABORT), and SET options are part of the plan cache key. So SSMS compiles its own fresh plan for your parameter, while the app keeps running a plan that was sniffed for somebody else’s.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/slow-in-app-fast-in-ssms on GitHub.

What you’ll see

  • A stored procedure that times out from the application and returns instantly when you paste the same EXEC into SSMS.
  • In the demo, the same call cost 21,915 logical reads from the “app” and 70 from “SSMS”: 300× the difference, for identical parameters.
  • Two cached plans for one procedure, with different set_options and different ParameterCompiledValues.

Why it happens

ADO.NET, ODBC, JDBC and most other drivers connect with ARITHABORT OFF. SSMS uses ARITHABORT ON. Connections with different SET options can’t share a plan, so when you run the procedure in SSMS it doesn’t find the app’s plan. It compiles a new one, sniffing your parameter value.

ARITHABORT didn’t make anything faster. When the demo reversed the order of the first calls, SSMS was exactly as slow as the app had been. The real problem is parameter sniffing.

What to do

  • Reproduce the app’s plan in SSMS: run SET ARITHABORT OFF; in the query window before the EXEC. Your session then lands on the app’s cache entry.
  • Fix the sniffing:
    • OPTION (RECOMPILE)
    • OPTIMIZE FOR
    • a Query Store hint
    • Parameter Sensitive Plan optimization on SQL Server 2022, but don’t count on it. It created no plan variants for this query on the tested build.

What doesn’t help

  • Setting ARITHABORT ON in the application. It only moves the app to a different cache entry and changes which customer pays. With the sniffs reversed, the big customer did 300,000 key lookups at 41× the reads of a scan.
  • Concluding “the database is fine, it’s the app”. Both are running the same procedure with different plans.

Check your server

-- Every cached copy of one procedure, its SET options, and what it was compiled for.
WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT  SetOptions   = so.v,
        ArithAbortOn = CASE WHEN so.v & 4096 = 4096 THEN 1 ELSE 0 END,
        CompiledFor  = qp.query_plan.value('(//ParameterList/ColumnReference/@ParameterCompiledValue)[1]', 'nvarchar(100)'),
        qs.execution_count,
        AvgReads     = qs.total_logical_reads / NULLIF(qs.execution_count, 0)
FROM    sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)    AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
CROSS APPLY (SELECT v = CAST(pa.value AS int) FROM sys.dm_exec_plan_attributes(qs.plan_handle) AS pa
             WHERE pa.attribute = 'set_options') AS so
WHERE   st.objectid = OBJECT_ID('dbo.YourProcedure') AND st.dbid = DB_ID();

Two rows with different SetOptions and different CompiledFor values are the whole diagnosis.

See it happen

The slow-in-app-fast-in-ssms demo plays both the app and SSMS in one session against a deliberately skewed table. It reproduces the complaint, shows the two cache entries, and proves that ARITHABORT isn’t the cause by reversing the order of the first calls.

Related: parameter sniffing · catch-all queries · Query Store plan regressions

Measured on SQL Server 2022 CU25 (16.0.4255.1), 8 schedulers. Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights