Azure SQL Database Stored Procedure long execution time issue

Question

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.

Answer 1

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

Answer 2

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.