Azure SQL Database Stored Procedure long execution time issue

Arundhati Sen 151 Reputation points
2024-02-07T16:10:08.8533333+00:00

In my Azure SQL Database I have Stored Procedure sp_subledger (Attached the code : sp_subledger.txt) The Stored Procedure is completing within 1 min when Tenant = 1 is provided but when Tenant = 2 is provided it is running for more than 2 hours. for (Tenant 2) execute statement: EXEC [dbo].[sp_subledgers] @Tenant = '2', @Date = '2023-10-07' ,@depreciationBookCode_value = 'DEFAULT', @GlAccounts_exclude = '0100' for (Tenant 1) execute statement: EXEC [dbo].[sp_subledgers] @Tenant = '1', @Date = '2023-10-07' ,@depreciationBookCode_value = 'GROUP', @GlAccounts_exclude = '0100' However, when the code block within the Stored Procedure is run separately, it completes in less than a minute for both Tenant 1 and Tenant 2.   I've attempted various strategies to rectify the situation. Unfortunately, none of these methods have proven useful.:

1.Turning off parameter sniffing               ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF; 2. Declaring local variable inside the Stored Procedure 3. utilizing different configurations:              ALTER DATABASE Blackline SET QUERY_STORE CLEAR DBCC FREEPROCCACHE SET ARITHABORT ON 4. attempting to recompile the Procedure (EXEC sp_recompile N'[dbo].[sp_subledgers]') Another issue is that I can't access the execution plan for the Stored Procedure since it stalls for 2 hours, requiring me to abort the run manually. Additionally, while the Stored Procedure is running for Tenant=2, the CPU usage of the Database reaches 100%

Please help how to resolve.

Azure SQL Database
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
62 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,591 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Saurabh Sharma 23,776 Reputation points Microsoft Employee
    2024-02-07T21:01:49.1233333+00:00

    Hi @Arundhati Sen

    Welcome to Microsoft Q&A! Thanks for posting the question.

    Can you please check how different is the data distribution or statistics for Tenant 2 vs Tenant 1?

    Also, you could try the below if this helps -

    • Query Store feature to monitor and analyze the performance of your stored procedure.
    • Check if you get anything in Query Performance insights as it helps to identify the top resource consuming and long-running queries in your workload. You can also drill down into details of a query to view the query text as well.
    • Automatic Tuning feature to get recommendations or apply optimizations automatically for your database.

    Hope this helps to narrow down your problem and help resolve the performance issue.

    Please let us know if you have any other questions.

    Thanks

    Saurabh

    0 comments
  2. Erland Sommarskog 105.8K Reputation points MVP
    2024-02-07T22:46:00.4933333+00:00

    The procedure is way too complex for it be possible for any closer review in the scope of a forum like this.

    You would need to track down which is the slow statement in the procedure and capture the query plans in both tenants to get an idea of what is going on. But as Saurabh, the data profiles may mean a lot to it.

    You say that if you run the code block outside of the SP, it runs fast. But it is not clear how you do that. A common mistake is to hardcode parameter values, which changes the situation for the optimizer quite considerably.

    I notice that there are large queries with the UNION operator. UNION implies DISTINCT, so if you know that the data sets are disjunct - or you don't want duplicates to be removed, change UNION to UNION ALL. Removing possible duplicates comes with a cost in performance.