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

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:

SQL
Copy
1-- Example of a problematic SET statement in a stored procedure
2CREATE PROCEDURE ExampleProcedure
3AS
4BEGIN
5    SET NUMERIC_ROUNDABORT ON;  -- Not recommended
6    SELECT * FROM ExampleTable;
7END;

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:

SQL
Copy
1-- Example of corrected stored procedure without SET statement
2CREATE PROCEDURE ExampleProcedure
3AS
4BEGIN
5    SELECT * FROM ExampleTable;  -- No alteration of session settings
6END;

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.
Categories

Maintenance Rules, Bugs

Additional Information
Example Test SQL
SQL
Copy
 1CREATE PROCEDURE test_recompile AS
 2
 3SET QUOTED_IDENTIFIER OFF 
 4SET ANSI_NULLS OFF 
 5SET ARITHABORT OFF 
 6SET ANSI_NULL_DFLT_ON OFF
 7SET ANSI_DEFAULTS OFF
 8SET ANSI_WARNINGS OFF 
 9SET ANSI_PADDING OFF
10SET CONCAT_NULL_YIELDS_NULL OFF
11SET NUMERIC_ROUNDABORT ON 
12SET NOCOUNT ON 
13SET ROWCOUNT 100 
14SET XACT_ABORT ON 
15SET IMPLICIT_TRANSACTIONS ON
16SET ARITHIGNORE OFF 
17SET LOCK_TIMEOUT 1
18SET FMTONLY ON 
19SET NOEXEC ON
20SET PARSEONLY OFF
21
22SELECT au_lname, au_fname, au_id from authors
23WHERE 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
See Also

Other Resources

Analysis Rules