SA0089 : The option has a not recommended value SET which will cause the stored procedure to be recompiled

Introduction

Avoid using SET statements to configure session options within stored procedures to prevent potential performance issues.

Description

When developing stored procedures, certain session settings are crucial for consistent behavior and performance. If these settings are altered via SET statements within the procedure, it can cause unnecessary recompilations, impacting performance negatively.

For example:

-- Example of a problematic SET statement in a stored procedure
CREATE PROCEDURE ExampleProcedure
AS
BEGIN
    SET NUMERIC_ROUNDABORT ON;  -- Not recommended
    SELECT * FROM ExampleTable;
END;

This example is problematic because changing NUMERIC_ROUNDABORT to ON inside a stored procedure is not recommended. Altering this setting can lead to recompilation every time the procedure is executed, which slows down execution.

Recompilation occurs each time the stored procedure is run, increasing CPU usage and execution time.
Inconsistent query results might occur if session settings differ from expected defaults, complicating troubleshooting.

How to fix

This section provides guidance on how to modify stored procedures to avoid using inappropriate SET statements, which can lead to performance issues due to unnecessary recompilations.

Follow these steps to address the issue:

1.Identify stored procedures using SET statements that alter session configurations, such as SET NUMERIC_ROUNDABORT ON .

2.Remove or replace the identified SET statements within the stored procedures to ensure the session settings align with recommended defaults outside of the procedure.

3.Refactor the code to rely on database or application-level configurations for session settings as needed, ensuring consistency and avoiding recompilations.

For example:

-- Example of corrected stored procedure without SET statement
CREATE PROCEDURE ExampleProcedure
AS
BEGIN
    SELECT * FROM ExampleTable;  -- No alteration of session settings
END;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

1 hour per issue.

Additional Information

Troubleshooting stored procedure recompilation

Example Test SQL

CREATE PROCEDURE test_recompile AS

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS OFF
SET ARITHABORT OFF
SET ANSI_NULL_DFLT_ON OFF
SET ANSI_DEFAULTS OFF
SET ANSI_WARNINGS OFF
SET ANSI_PADDING OFF
SET CONCAT_NULL_YIELDS_NULL OFF
SET NUMERIC_ROUNDABORT ON
SET NOCOUNT ON
SET ROWCOUNT 100
SET XACT_ABORT ON
SET IMPLICIT_TRANSACTIONS ON
SET ARITHIGNORE OFF
SET LOCK_TIMEOUT 1
SET FMTONLY ON
SET NOEXEC ON
SET PARSEONLY OFF

SELECT au_lname, au_fname, au_id from authors
WHERE au_lname like 'L%'

Analysis Results

	Message	Line	Column
1	SA0089 : The SET ARITHABORT option to OFF will cause the stored procedure to be recompiled everytime it is execued.	5	4
2	SA0089 : The SET ANSI_NULL_DFLT_ON option to OFF will cause the stored procedure to be recompiled everytime it is execued.	6	4
3	SA0089 : The SET ANSI_DEFAULTS option to OFF will cause the stored procedure to be recompiled everytime it is execued.	7	4
4	SA0089 : The SET ANSI_WARNINGS option to OFF will cause the stored procedure to be recompiled everytime it is execued.	8	4
5	SA0089 : The SET ANSI_PADDING option to OFF will cause the stored procedure to be recompiled everytime it is execued.	9	4
6	SA0089 : The SET CONCAT_NULL_YIELDS_NULL option to OFF will cause the stored procedure to be recompiled everytime it is execued.	10	4

Analysis Rules