SQL Server Stored Procedure Performance: The Critical Impact of ANSI_NULLS Settings

Problem Background and Phenomenon Analysis

In SQL Server database development, a perplexing phenomenon frequently occurs: identical SQL queries perform excellently when executed directly but suffer dramatic performance degradation when encapsulated within stored procedures. This article explores the root causes and solutions based on a real-world case study.

The core query in the case study is as follows:

DECLARE @SessionGUID uniqueidentifier
SET @SessionGUID = 'BCBA333C-B6A1-4155-9833-C495F22EA908'

SELECT *
FROM Report_Opener
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank

When executed directly, this query shows a subtree cost of only 0.502, indicating high efficiency. However, when the same logic is encapsulated in a stored procedure:

CREATE PROCEDURE dbo.ViewOpener @SessionGUID uniqueidentifier AS
SELECT *
FROM Report_Opener
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank

The execution cost surges to 19.2, representing nearly a 40-fold performance degradation. This performance disparity is common in database development, but the underlying causes are often overlooked.

Execution Plan Comparative Analysis

Comparing the two execution plans reveals significant differences:

Efficient Execution Plan Characteristics:

• Utilizes index seek operations
• Employs nested loops joins
• Implements reasonable sorting and compute scalar operations
• Maintains concise and efficient execution paths

Inefficient Execution Plan Characteristics:

• Exhibits extensive spooling operations
• Uses table scans instead of index seeks
• Shows increased join operation complexity
• Features lengthy execution paths with high resource consumption

The key distinction lies in the inefficient plan's "eager spooling" operation, processing approximately 6 million rows, while the efficient plan avoids such resource-intensive operations.

Root Cause: ANSI_NULLS Setting Differences

Through thorough investigation, the problem根源 was identified in differing ANSI_NULLS settings. The critical findings are:

Slow Stored Procedure Definition:

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.ViewOpener_Slow
    @SessionGUID uniqueidentifier
AS
SELECT *
FROM Report_Opener
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank
GO

Fast Stored Procedure Definition:

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.ViewOpener_Fast
    @SessionGUID uniqueidentifier 
AS
SELECT *
FROM Report_Opener
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank
GO

The core difference: the slow version uses SET ANSI_NULLS OFF, while the fast version uses SET ANSI_NULLS ON.

ANSI_NULLS Mechanism Detailed Explanation

The ANSI_NULLS setting affects how SQL Server handles NULL value comparisons:

When ANSI_NULLS is ON:

• Uses standard ANSI SQL semantics
• NULL comparisons with any value (including another NULL) return UNKNOWN
• Complies with ISO SQL standards

When ANSI_NULLS is OFF:

• Uses traditional SQL Server semantics
• Allows NULL = NULL to return TRUE
• Not compliant with latest standards, primarily for backward compatibility

In our case study, the view contains conditions like (table.column IS NOT NULL), and different ANSI_NULLS settings cause the query optimizer to generate completely different execution plans.

Development Tool Default Setting Variations

The problem is further complicated by default setting differences across SQL Server tools:

• Query Analyzer: Default ANSI_NULLS ON
• Enterprise Manager: Default ANSI_NULLS OFF

This default setting variation explains why developers observe good performance during testing but experience performance degradation after deployment. Microsoft acknowledges this behavior in Knowledge Base article KB296769.

Solutions and Best Practices

Solution 1: Explicit ANSI_NULLS Setting

Explicitly specify ANSI_NULLS settings in stored procedure definitions:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE dbo.YourProcedure
    @Parameter data_type
AS
BEGIN
    -- Stored procedure logic
    SELECT * FROM YourTable WHERE Conditions
END
GO

Solution 2: Local Variables to Avoid Parameter Sniffing

While the primary cause in this case is ANSI_NULLS, parameter sniffing remains a common issue:

CREATE PROCEDURE GetOrders(@CustID varchar(20))
AS
BEGIN
    DECLARE @LocalCustID varchar(20)
    SET @LocalCustID = @CustID
    
    SELECT * 
    FROM orders
    WHERE customerid = @LocalCustID
END

Solution 3: Dynamic SQL and Execution Options

In certain scenarios, consider dynamic SQL or execution-time recompilation:

-- Dynamic SQL approach
CREATE PROCEDURE dbo.DynamicQuery
    @SessionGUID uniqueidentifier
AS
BEGIN
    DECLARE @SQL NVARCHAR(1000)
    SET @SQL = N'SELECT * FROM Report_Opener WHERE SessionGUID = @GUID ORDER BY CurrencyTypeOrder, Rank'
    
    EXEC sp_executesql @SQL, N'@GUID uniqueidentifier', @SessionGUID
END

-- Execution-time recompilation
EXEC YourProcedure @Parameter WITH RECOMPILE

Preventive Measures and Development Standards

1. Unified Development Environment Settings: Ensure all development tools use identical ANSI settings
2. Code Review Inclusion of SET Options: Verify stored procedure SET options during code reviews
3. Automated Deployment Checks: Validate stored procedure settings in CI/CD pipelines
4. Documentation Standards: Clearly define the ANSI standard version used by the team
5. Comprehensive Performance Testing: Conduct performance tests under different settings

Technical Evolution and Compatibility Considerations

As SQL Server versions evolve, ANSI standard compatibility becomes increasingly important:

• Starting with SQL Server 2005, behavior of certain SET options has changed
• SQL Server 2016 and later versions impose stricter ANSI standard requirements
• Future versions may no longer support ANSI_NULLS OFF

New projects should always use ANSI_NULLS ON to ensure forward compatibility.

Conclusion

The root causes of SQL Server stored procedure performance issues often lie in subtle details. While ANSI_NULLS setting differences may appear minor, they can cause significant execution plan variations. By understanding this mechanism, developers can:

• Avoid common performance pitfalls
• Establish unified development standards
• Enhance application stability and performance
• Prepare for future version upgrades

Remember: in database performance optimization, the devil is in the details, and ANSI settings represent one of these critical details.