The SET Options Your Application Sets Without Telling You — and How They Silently Break Query Performance

SELECT
  s.session_id,
  s.login_name,
  s.host_name,
  s.program_name,
  -- Decode the set_options bitmask into human-readable flags:
  CASE WHEN (s.set_options & 64) <> 0  THEN 'ON' ELSE 'OFF' END  AS arithabort,
  CASE WHEN (s.set_options & 16) <> 0  THEN 'ON' ELSE 'OFF' END  AS ansi_nulls,
  CASE WHEN (s.set_options & 8)  <> 0  THEN 'ON' ELSE 'OFF' END  AS ansi_padding,
  CASE WHEN (s.set_options & 256) <> 0 THEN 'ON' ELSE 'OFF' END  AS quoted_identifier
FROM   sys.dm_exec_sessions s
WHERE  s.is_user_process = 1
ORDER BY s.program_name, s.login_name;

SELECT
  cp.usecounts,
  cp.size_in_bytes / 1024                                     AS plan_kb,
  cp.objtype,
  qs.total_elapsed_time / qs.execution_count / 1000            AS avg_ms,
  qs.total_logical_reads / qs.execution_count                   AS avg_reads
FROM       sys.dm_exec_cached_plans   cp
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
JOIN       sys.dm_exec_query_stats    qs ON qs.plan_handle = cp.plan_handle
WHERE      st.objectid = OBJECT_ID('dbo.YourProcedureName')
ORDER BY   qs.total_elapsed_time / qs.execution_count DESC;

-- More than 1 row = multiple plans for the same proc.
-- Compare avg_reads: the bad plan will have 100x-10000x more reads.

SELECT
  r.session_id,
  s.program_name,
  r.status,
  r.total_elapsed_time / 1000 AS elapsed_ms,
  r.logical_reads,
  CASE WHEN (s.set_options & 64) <> 0 THEN 'ON' ELSE 'OFF' END AS arithabort,
  st.text  AS query_text
FROM       sys.dm_exec_requests   r
JOIN       sys.dm_exec_sessions   s  ON s.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle)  st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) qp
WHERE      r.status = 'running';

-- Pattern to look for:
-- SSMS sessions: ARITHABORT = ON, elapsed = 40ms
-- App sessions:  ARITHABORT = OFF, elapsed = 22,000ms

-- For ADO.NET connections, add this to the connection string handler:
-- Or run at connection open time:
SET ARITHABORT ON;

-- EF Core / ADO.NET: add an interceptor or run in an IDbCommandInterceptor
-- This aligns application SET options with SSMS defaults
-- and forces both to share the same plan cache entries.
-- Downside: if the sniffed plan is still bad, this does not help.

ALTER PROCEDURE dbo.YourProcedureName
  @Param1 INT
AS
SELECT ...
WHERE  SomeColumn = @Param1
OPTION (OPTIMIZE FOR (@Param1 UNKNOWN));

-- Forces the optimizer to use average statistics instead of sniffed values.
-- Best for procedures called with wildly varying parameter values.
-- Produces a generic plan — not optimal for any one value, acceptable for all.

ALTER PROCEDURE dbo.YourProcedureName
  @Param1 INT
WITH RECOMPILE  -- compile a fresh plan on every call
AS
SELECT ...

-- Only use this when:
-- (a) The data distribution varies enormously by parameter value
-- (b) The compile cost (~1-5ms) is acceptable
-- (c) The procedure is not called thousands of times per second

-- Required SET options for indexed view eligibility:
-- Run this block and confirm each is ON:
SELECT
  'ARITHABORT'           AS option_name, CASE WHEN (@@options & 64)  <> 0 THEN 'ON' ELSE 'OFF' END AS status UNION ALL
SELECT  'ANSI_NULLS',            CASE WHEN (@@options & 16)  <> 0 THEN 'ON' ELSE 'OFF' END        UNION ALL
SELECT  'QUOTED_IDENTIFIER',     CASE WHEN (@@options & 256) <> 0 THEN 'ON' ELSE 'OFF' END       UNION ALL
SELECT  'ANSI_WARNINGS',         CASE WHEN (@@options & 8)   <> 0 THEN 'ON' ELSE 'OFF' END       UNION ALL
SELECT  'NUMERIC_ROUNDABORT',    CASE WHEN (@@options & 8192) = 0 THEN 'OFF (required)' ELSE 'ON (blocks indexed view)' END;

-- Run this immediately after every connection open:
-- (Add to your DbContext OnConfiguring, connection factory, or middleware)
SET ARITHABORT ON;
SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET QUOTED_IDENTIFIER ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;