SA0111 : Do not use WAITFOR DELAY/TIME statement in stored procedures, functions, and triggers

Introduction

Blocking execution in SQL Server with WAITFOR can cause performance issues.

Description

The problem occurs when the WAITFOR statement is used within a stored procedure, function, or trigger in SQL Server. This statement is designed to pause execution until a specific time or interval has elapsed. However, in Online Transaction Processing (OLTP) systems, this delay can lead to significant performance degradation and is generally not desirable unless there’s a specific, justified reason.

For example:

-- Example of a problematic query using WAITFOR
CREATE PROCEDURE SampleProcedure
AS
BEGIN
    WAITFOR DELAY '00:00:10'; -- Delay execution for 10 seconds
    SELECT * FROM ImportantTable;
END;

In this example, the WAITFOR DELAY statement causes a 10-second pause every time the procedure runs. This delay can unnecessarily block resources and reduce the efficiency of high-transaction environments, leading to bottlenecks and delayed processing.

Increased transaction response time, impacting overall system performance.
Potential resource contention as subsequent transactions wait for delayed operations to complete.

How to fix

To resolve issues related to the usage of WAITFOR DELAY/TIME in SQL Server and improve performance, follow the steps below.

Follow these steps to address the issue:

1.Identify occurrences of WAITFOR statements within your stored procedures, functions, or triggers. Review the necessity of each WAITFOR operation.

2.Evaluate if the delay introduced by WAITFOR is essential for business logic. If it is not critical, remove or refactor the statement to improve performance.

3.If maintaining a delay is necessary, consider moving the logic outside of the SQL Server process. For example, incorporate the delay in application code where waiting impacts the system less severely.

4.Ensure that any remaining use of WAITFOR is optimized and does not occur in high-transaction or time-critical processes.

For example, refactor the procedure as follows:

-- Example of refactored query without WAITFOR
CREATE PROCEDURE SampleProcedure
AS
BEGIN
    -- Removed WAITFOR DELAY to improve performance
    SELECT * FROM ImportantTable;
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

5 minutes per issue.

Additional Information

WAITFOR (Transact-SQL)

Example Test SQL

CREATE PROCEDURE testsp_SA0111
    (
    @DelayLength char(8)= '00:00:00'
    )
AS
BEGIN
    WAITFOR DELAY @DelayLength

  WAITFOR DELAY @DelayLength -- IGNORE:SA0111

  WAITFOR TIME '10:20:00 00:00:00:'

    DECLARE @conversation_group_id UNIQUEIDENTIFIER

    WAITFOR (
        GET CONVERSATION GROUP @conversation_group_id
        FROM ExpenseQueue ),
    TIMEOUT 60000 ;

END;

Analysis Results

	Message	Line	Column
1	SA0111 : Do not use WAITFOR DELAY/TIME statement in stored procedures, functions, and triggers.	7	4
2	SA0111 : Do not use WAITFOR DELAY/TIME statement in stored procedures, functions, and triggers.	11	1

Analysis Rules