Az SQL DB performance issues

The performance issue you're experiencing with the stored procedure in the UAT environment compared to the Dev environment can be attributed to several factors related to query execution plans and database state. Here are some possible root causes, workarounds, and diagnostic steps:

Root Causes

1. Different Execution Plans: The SQL Query Optimizer may generate different execution plans for the same stored procedure based on the database state, statistics, or available resources. If the execution plan in UAT is suboptimal, it can lead to increased latency.
2. Stale Statistics: If the statistics in the UAT environment are outdated or not representative of the underlying data distribution, the optimizer may make poor cardinality estimates, resulting in inefficient query plans.
3. Resource Availability: Differences in server resources (CPU, memory, I/O) between the Dev and UAT environments can influence the execution plan chosen by the optimizer. For example, if UAT has fewer resources, it may lead to slower performance.
4. Database Compatibility Level: If the compatibility level of the UAT database differs from that of the Dev database, it could affect the behavior of the query optimizer and the execution plans generated.

Workarounds

1. Update Statistics: Regularly update statistics in the UAT environment to ensure that the optimizer has accurate information about data distribution. This can be done using the UPDATE STATISTICS command.
2. Recompile the Stored Procedure: You can force the stored procedure to recompile using the WITH RECOMPILE option or by altering the procedure to include this option. This may help generate a more optimal execution plan based on current statistics.
3. Use Query Hints: If you identify a specific execution plan that performs better, you can use query hints to influence the optimizer's choices without changing the stored procedure code.

Diagnostic Steps

1. Compare Execution Plans: Capture and compare the actual execution plans of the stored procedure in both environments. Look for differences in the operators used and the estimated vs. actual row counts.
2. Check for Missing Indexes: Use tools like the Database Advisor or query execution plans to identify any missing indexes that could improve performance in the UAT environment.
3. Monitor Resource Usage: Analyze the resource utilization during the execution of the stored procedure in both environments to identify any bottlenecks in the UAT environment.
4. Database Compatibility Level: Verify and compare the database compatibility levels between Dev and UAT to ensure they are aligned.

By following these steps, you should be able to identify the underlying causes of the performance differences and implement solutions to improve the stored procedure's execution time in the UAT environment.

References:

• Detect query performance bottlenecks
• Tune your database

For reference, this was the answer I posted to your other post about the same question:

The only suggestion I have directly with the code is that

SQL

DECLARE group_cursor CURSOR FOR 
SELECT DISTINCT [group] FROM @FilterTable;

Should be, out of principle:

SQL

DECLARE group_cursor CURSOR STATIC LOCAL FOR 
SELECT DISTINCT [group] FROM @FilterTable;

Else, set up an extended-events session that you filter for the spid you run the procedure from. In the session, include the event sp_statement_completed. Use Watch Live Data to see the output, and add Duration to the column. That should help you to narrow down which is the slow statement.