Fast in SSMS, Slow in the App

If you have experienced a stored procedure or ad hoc query that runs fast in SSMS, but is slow in your application, the difference in T-SQL SET options between SSMS and your application may partially or wholly explain the difference in execution time. Microsoft offers a brief explanation for the phenomenon. Erland Sommarskog has an epic post that goes into even more detail. To try to resolve the performance issue, we can observe the SET options that are ON (enabled) by default in SSMS and duplicate those options for your stored procedure or ad hoc query.

Default SET Options for SSMS

Copy & paste this query into SSMS and execute it:

/*
	https://www.mssqltips.com/sqlservertip/1415/determining-set-options-for-a-current-session-in-sql-server/
*/
WITH OptionValues AS (
	SELECT
		optionValues.id,
		optionValues.name,
		optionValues.description,
		ROW_NUMBER() OVER (ORDER BY id) AS bitNum
	FROM 
	(
		VALUES
		(1, 'DISABLE_DEF_CNST_CHK', 'Controls interim or deferred constraint checking.'),
		(2, 'IMPLICIT_TRANSACTIONS', 'For dblib network library connections, controls whether a transaction is started implicitly when a statement is executed. The IMPLICIT_TRANSACTIONS setting has no effect on ODBC or OLEDB connections.'),
		(4, 'CURSOR_CLOSE_ON_COMMIT', 'Controls behavior of cursors after a commit operation has been performed.'),
		(8, 'ANSI_WARNINGS', 'Controls truncation and NULL in aggregate warnings.'),
		(16, 'ANSI_PADDING', 'Controls padding of fixed-length variables.'),
		(32, 'ANSI_NULLS', 'Controls NULL handling when using equality operators.'),
		(64, 'ARITHABORT', 'Terminates a query when an overflow or divide-by-zero error occurs during query execution.'),
		(128, 'ARITHIGNORE', 'Returns NULL when an overflow or divide-by-zero error occurs during a query.'),
		(256, 'QUOTED_IDENTIFIER', 'Differentiates between single and double quotation marks when evaluating an expression.'),
		(512, 'NOCOUNT', 'Turns off the message returned at the end of each statement that states how many rows were affected.'),
		(1024, 'ANSI_NULL_DFLT_ON', 'Alters the session'+CHAR(39)+'s behavior to use ANSI compatibility for nullability. New columns defined without explicit nullability are defined to allow nulls.'),
		(2048, 'ANSI_NULL_DFLT_OFF', 'Alters the session'+CHAR(39)+'s behavior not to use ANSI compatibility for nullability. New columns defined without explicit nullability do not allow nulls.'),
		(4096, 'CONCAT_NULL_YIELDS_NULL', 'Returns NULL when concatenating a NULL value with a string.'),
		(8192, 'NUMERIC_ROUNDABORT', 'Generates an error when a loss of precision occurs in an expression.'),
		(16384, 'XACT_ABORT', 'Rolls back a transaction if a Transact-SQL statement raises a run-time error.')
	) AS optionValues(id, name, description)
),
OptionValueSettings AS
(
	SELECT *, CASE WHEN (@@OPTIONS & id) = id THEN 1 ELSE 0 END AS setting
	FROM OptionValues
)
SELECT *,
	'SET ' + s.name + CASE WHEN s.setting = 1 THEN ' ON;' ELSE ' OFF;' END AS SetStatement
FROM OptionValueSettings s
ORDER BY s.setting, s.name

The query is sorted by [setting], so scroll down to the bottom of the result set to the rows WHERE [setting] = 1. It should look similar to this:

Copy the output of the [SetStatement] column and paste it into the header/top of your stored procedure or ad hoc query. For example:

CREATE OR ALTER PROCEDURE dbo.GetStuff
AS
BEGIN
	SET ANSI_NULL_DFLT_ON ON;
	SET ANSI_NULLS ON;
	SET ANSI_PADDING ON;
	SET ANSI_WARNINGS ON;
	SET ARITHABORT ON;
	SET CONCAT_NULL_YIELDS_NULL ON;
	SET QUOTED_IDENTIFIER ON;

	--Other stored proc queries.
	--Other statements.
	--Etc
END

After the modified stored procedure or ad hoc query is deployed, there is a good chance it will perform as well from your application as it does from SSMS.