Az SQL DB performance issues

Hemanth Kumar Kotha 20

Query latency differs across environments (Dev vs UAT) – same stored procedure, different performance

I’m facing a performance issue where the same stored procedure and identical query logic behave differently across environments.

Problem Summary

@Erland Sommarskog @Alberto Morillo

Dev environment

Stored procedure executes quickly

No noticeable latency issues

UAT environment

The same stored procedure takes significantly longer

Execution plan differs from Dev

Key observation

When I restore the same BACPAC as a brand-new database in UAT, the stored procedure starts performing normally again

No code changes are required for it to work fast in the newly restored database

Question

Why does the same stored procedure show high latency in UAT, but works fine when the same BACPAC is restored as a new database?

What could be the possible:

Root causes

Workarounds

Reverse-engineering or diagnostic steps to identify and permanently fix this issue in the existing UAT database?

Additional context

Query text and stored procedure definition are identical

Schema is deployed via DACPAC/BACPAC

Issue appears to be environment-specific, not code-specific

Recreating the database resolves the issue temporarily

This is the scenario: we have 2 Azure SQL databases, one across each environment (Dev and Prod). The tier of the Dev database is lower than that of the Prod database.

In Prod, an Azure Data Factory job is scheduled every 5 minutes that syncs data from an on-premise MySQL database into the Azure SQL database (via incremental load and merge operations) whereas the data in Dev is on an adhoc basis.

The amount of data across both Dev and Prod databases are the same.

Issue: the stored procedure in Dev take ~5 seconds, whereas the same stored procedure in Prod take ~2 minutes.

Note: we tried disabling the Prod job of every 5 minutes and test out the stored procedure performance, but the performance was the same - ~2 minutes.

We have already tried these steps :

Compared the execution plans across both the databases and it was different. So try executing the stored procedure with "Option recompile" Restarted the Azure SQL database Republished the DACPAC Rebuilt indexes Updated the statistics But we still have the same issue.

We then restored the .BACPAC of the prod database as another database and the performance improved.

We want to know what is that got resolved by restoring the .BACPAC file.

Because now there is another stored procedure with a similar issue with the only change in the above scope is the execution plan is same still there is performance diff. Database with higher tier is having an issue

Hemanth Kumar Kotha 20 Reputation points

2026-02-03T12:20:02.9033333+00:00

Also did index rebuild, update stats , publish dacpac. But still no change.
Alberto Morillo 35,501 Reputation points MVP Volunteer Moderator

2026-02-03T13:47:58.67+00:00

What are top waits when you execute that store procedure? https://www.sqlcoffee.com/Azure_0017.htm

If you execute the stored procedure using SSMS on Production and generate the actual plan (not the estimated plan, what does the plan tell you (where it hurts)?
Alberto Morillo 35,501 Reputation points MVP Volunteer Moderator

2026-02-03T14:24:05.9733333+00:00

The actual query plan is the best way to know what happen when a query/sp is executed. It shows T-SQL poor choices, bad data structure choices, bad stats, decisions made by the query optimizer, and more.
Erland Sommarskog 133.9K Reputation points MVP Volunteer Moderator

2026-02-03T22:35:55.13+00:00

Can you please explain why you found it appropriate to the post the same question to two different tags, with different content?

I will delete the question you posted with the SQL Server Database Engine tag, since this clearly a violation of code of conduct.

I would also encourage you to edit your post, to make it more readable. There is little reason to have text inside a code block.
Erland Sommarskog 133.9K Reputation points MVP Volunteer Moderator

2026-02-04T21:43:11.8333333+00:00

Thanks for taking the time to clean up your post, that makes it a lot more readable!

In your other post, you posted a long procedure that produced dynamic SQL. But since you say that you have concluded that query plans are different, I assume that you have identified the particular query that is giving you headache? Or did you look at the execution plan for the entire stored procedure?

In any case, would it be possible for you to share the query plans, both the fast and the slow? Note that we want the actual execution plan as XML. If the plan is for the entire batch, it helps a lot if you can tell which statement that is causing you pain.

The reason that comes to mind at first is parameter sniffing. That is, the plan is optimised for a parameter value that the current executions are using. But there can be other reasons as well. I understood that Dev and Prod are not the same service tier. Normally, a higher service tier gives you better performance, but the optimizer takes the amount of memory in regard, and sometimes this can lead it astray.

Hemanth Kumar Kotha 20

@Erland Sommarskog here is the actual sproc and xml plans xml with issues.xml xml without issues.xml

CREATE  PROCEDURE [Query].[sp003PaSoGetSalesOrders]
@FiltersJSON NVARCHAR(MAX) = NULL,
@PageSize INT = 50,
@PageNumber INT = 1,
@SortColumns NVARCHAR(MAX) = NULL,
@IncludeCompleted BIT = 0
AS
BEGIN
SET NOCOUNT ON;
DECLARE @RowStart INT = (@PageNumber - 1) * @PageSize;
DECLARE @ParsedSearchQueries NVARCHAR(MAX) = '';
DECLARE @SortSQL NVARCHAR(MAX) = 'OrderDate DESC';
DECLARE @UserUPN VARCHAR(4000) = ORIGINAL_LOGIN();
DECLARE @sql NVARCHAR(MAX);
DECLARE @IsDraftShow BIT = 0
CREATE TABLE #SalesOrders
(
    SalesOrderId INT,
    StrategixSoId VARCHAR(100),
    DocId VARCHAR(50),
    OrderDate DATETIME,
    action_date DATETIME,
    MidwichCompanyName NVARCHAR(200),
    CustomerName NVARCHAR(200),
    CustomerID NVARCHAR(100),
    ReqDeliveryDate DATETIME,
    due_date_type VARCHAR(20),
    OrderStatus NVARCHAR(100),
    stop_status NVARCHAR(100),
    OutstandingRevenue DECIMAL(18,2),
    OrderNotes NVARCHAR(MAX),
    AssignedTo NVARCHAR(200),
    assigned_to_email NVARCHAR(200),
    PurchaseOrderID NVARCHAR(100),
    SalesManager NVARCHAR(200),
    OrderType NVARCHAR(100),
    TotalSaleValue DECIMAL(18,2),
    TotalCount INT
);
BEGIN TRY
------------------------------------------------------------------
-- Build WHERE clause (supports dynamic group AND/OR logic)
------------------------------------------------------------------
IF @FiltersJSON IS NOT NULL AND LTRIM(RTRIM(@FiltersJSON)) <> '[]'
BEGIN
DECLARE @FilterTable TABLE (
[group] NVARCHAR(50),
[group_logic] NVARCHAR(5),
[key] NVARCHAR(200),
[operator] NVARCHAR(10),
[value] NVARCHAR(MAX)
);
INSERT INTO @FilterTable ([group], [group_logic], [key], [operator], [value])
SELECT 
[group],
ISNULL([group_logic], 'AND'),
[key],
[operator],
[value]
FROM OPENJSON(@FiltersJSON)
WITH (
[group] NVARCHAR(50),
[group_logic] NVARCHAR(5),
[key] NVARCHAR(200),
[operator] NVARCHAR(10),
[value] NVARCHAR(MAX)
);
DECLARE @GroupFilters TABLE ([group] NVARCHAR(50), GroupedFilter NVARCHAR(MAX));
DECLARE @CurrentGroup NVARCHAR(50), @GroupLogic NVARCHAR(5), @Filter NVARCHAR(MAX), @Sep NVARCHAR(10);
DECLARE group_cursor CURSOR FOR 
SELECT DISTINCT [group] FROM @FilterTable;
OPEN group_cursor;
FETCH NEXT FROM group_cursor INTO @CurrentGroup;
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT TOP 1 @GroupLogic = [group_logic] FROM @FilterTable WHERE [group] = @CurrentGroup;
SET @Sep = CASE WHEN @GroupLogic = 'OR' THEN ' OR ' ELSE ' AND ' END;
SELECT @Filter = '(' + ISNULL((
SELECT STRING_AGG(
[key] + ' ' + [operator] + ' ' +
CASE 
WHEN [operator] = 'LIKE' THEN '''%' + REPLACE([value], '''', '''''') + '%'''
WHEN ISNUMERIC([value]) = 1 THEN [value]
ELSE '''' + REPLACE([value], '''', '''''') + ''''
END,
@Sep
)
FROM @FilterTable f
WHERE f.[group] = t.[group]
), '') + ')'
FROM (SELECT TOP 1 [group] FROM @FilterTable WHERE [group] = @CurrentGroup) t;
IF LEN(ISNULL(@Filter, '')) > 2
INSERT INTO @GroupFilters VALUES (@CurrentGroup, @Filter);
FETCH NEXT FROM group_cursor INTO @CurrentGroup;
END
CLOSE group_cursor;
DEALLOCATE group_cursor;
-- Combine all groups with AND (could make this dynamic later)
SELECT @ParsedSearchQueries = STRING_AGG(GroupedFilter, ' AND ')
FROM @GroupFilters;
END
If Exists (Select 1 from @FilterTable where [key] = 'SOH_STATUS' and [value] = 'Draft')
Select @IsDraftShow = 1 
------------------------------------------------------------------
-- Build ORDER BY clause
------------------------------------------------------------------
IF @SortColumns IS NOT NULL
BEGIN
SELECT @SortSQL = STRING_AGG(
[key] + ' ' + 
CASE WHEN [value] IN ('ASC', 'DESC') THEN [value] ELSE 'ASC' END,
', '
)
FROM OPENJSON(@SortColumns)
WITH ([key] NVARCHAR(200), [value] NVARCHAR(10));
END
------------------------------------------------------------------
-- Build dynamic SQL for data
------------------------------------------------------------------
SET @sql = '
WITH InternalLineItemTotals AS (
SELECT 
soi.sales_order_id,
SUM(soi.product_cost * soi.quantity) AS TotalSaleValue
FROM [Confidential].[vwPaSalesOrderLineItems] soi
GROUP BY sales_order_id
),
StxLineItemTotals AS (
SELECT 
soi.SOI_ORDREF,
SUM(soi.SOI_PRICE * soi.SOI_ITQTY) AS TotalSaleValue
FROM [Confidential].[vwStxSOITEM] soi
GROUP BY SOI_ORDREF
),
FilteredData AS (
SELECT 
saa.sales_order_id AS SalesOrderId,
SOH_ORDREF AS StrategixSoId,
OlpEmail_DocId AS DocId,
SOH_ORDDATETIME AS OrderDate,
action_date,
SOH_COMPANY AS MidwichCompanyName,
NDM.NDM_NAME AS CustomerName,
SOH_ACCOUNT AS CustomerID,
SOH_REQDATE AS ReqDeliveryDate,
due_date_type,
SOH_STATUS AS OrderStatus,
SOH_STOPCODE AS stop_status,
SOH_ORDVAL AS OutstandingRevenue,
order_notes AS OrderNotes,
AssignedTo,
assigned_to_email,
SOH_CUSREF AS PurchaseOrderID,
STXUSR.US_NAME AS SalesManager,
OrderType AS OrderType,
COALESCE(slit.TotalSaleValue, ilit.TotalSaleValue, 0) AS TotalSaleValue
FROM [Enterprise].[vwPaSalesOrdersAll] saa
LEFT JOIN [Confidential].[vwStxDLCUST] CUST ON saa.SOH_ACCOUNT = CUST.DLCUS_CUSTOMER
LEFT JOIN [Confidential].[vwStxUSERS] STXUSR ON CUST.DLCUS_REP = STXUSR.US_COID
LEFT JOIN [Confidential].[vwStxNDMAS] NDM ON saa.SOH_ACCOUNT = NDM.NDM_NDCODE
LEFT JOIN InternalLineItemTotals ilit ON saa.sales_order_id = ilit.sales_order_id
LEFT JOIN StxLineItemTotals slit ON saa.SOH_ORDREF = slit.SOI_ORDREF
' +
CASE 
WHEN @ParsedSearchQueries = '' THEN 
'WHERE (' + CASE WHEN @IncludeCompleted = 1 THEN '1=1' ELSE 'ISNULL(SOH_STATUS,'''') not in (''Complete'',''Draft'')' END + ')'
ELSE 
'WHERE (' + @ParsedSearchQueries + ') AND (' + CASE WHEN @IncludeCompleted = 1 THEN '1=1' ELSE CASE WHEN @IsDraftShow = 1 THEN 'ISNULL(SOH_STATUS,'''') <> ''Complete'''
ELSE 'ISNULL(SOH_STATUS,'''') not in (''Complete'',''Draft'')' END
END + ')'
END
+ '
),
Counted AS (
SELECT *, (SELECT COUNT(1) FROM FilteredData) AS TotalCount
FROM FilteredData
)
SELECT *
FROM Counted
ORDER BY ' + @SortSQL;
IF @PageSize > 0
SET @sql += '
OFFSET ' + CAST(@RowStart AS VARCHAR) + ' ROWS
FETCH NEXT ' + CAST(@PageSize AS VARCHAR) + ' ROWS ONLY';
SET @sql += ' OPTION (RECOMPILE);';

----------------------------------------------------------------
-- Execute dynamic SQL and capture paginated results
----------------------------------------------------------------
INSERT INTO #SalesOrders
EXEC sp_executesql @sql;
------------------------------------------------------------------
-- Return data and total
------------------------------------------------------------------
DECLARE @FinalSQL NVARCHAR(MAX);

SET @FinalSQL = '
SELECT *
FROM #SalesOrders
ORDER BY ' + @SortSQL;

EXEC sp_executesql @FinalSQL;

SELECT TOP 1 TotalCount FROM #SalesOrders;
END TRY
BEGIN CATCH
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
DECLARE @ErrorState INT = ERROR_STATE();
DECLARE @ErrorNumber INT = ERROR_NUMBER();
DECLARE @ErrorProcedure NVARCHAR(200) = ERROR_PROCEDURE();
DECLARE @ErrorLine INT = ERROR_LINE();
DECLARE @FullErrorMessage NVARCHAR(MAX) = CONCAT(
'Error ', @ErrorNumber,
' Severity ', @ErrorSeverity,
' State ', @ErrorState,
' Occurred in ', COALESCE(@ErrorProcedure, 'unknown'),
' at line ', @ErrorLine, ': ', @ErrorMessage
);
EXEC [Log].[spInsertLogError] @UPN = @UserUPN, @Detail = @FullErrorMessage;
THROW;
END CATCH
END

GO

Erland Sommarskog 133.9K Reputation points MVP Volunteer Moderator

2026-02-05T21:38:33.5966667+00:00

But as I understand it from you other thread https://learn.microsoft.com/en-us/answers/questions/5760140/azure-sql-performance-issue, the issue has been resolved with help the DISABLE_OPTIMIZER_ROWGOAL_HINT?
Manoj Kumar Boyini 13,440 Reputation points Microsoft External Staff Moderator

2026-02-05T23:26:04.3533333+00:00

Hi Hemanth Kumar Kotha

I hope you had a chance to review the information shared earlier, and I hope this information has been helpful! If you still have questions, please let us know what is needed in the comments so the question can be answered.
Hemanth Kumar Kotha 20 Reputation points

2026-02-06T06:57:33.43+00:00

@Erland Sommarskog Both of them are 2 different aspects. And 2 different query's this one is causing trouble after few days of database is being used. Like when I create a new db with bacpac this query works fine without any issues for few days and later down the line it's having some issues we did index rebuild and all other things mentioned above but still not works we need to understand what's getting resolved when I create a new db with same bacpac and then it starts working fine again.
Erland Sommarskog 133.9K Reputation points MVP Volunteer Moderator

2026-02-06T20:59:49.5433333+00:00

OK, I see that this is a different query. However, just like your other question, this is a very complex query. While I see the full query text, I don't see the definition of the underlying views, so there is no possibility for me to make a full analysis.

First an observation: The good plan uses parallelism, the bad plan does not. The bad plan also has a lower estimated cost, and apparently the estimated cost is below the setting for cost threshold for parallelism. I'm not sure if you can change this setting in Azure SQL Database, but the impression I get of a very quick googling, it does not seem so.

It's hard to say how come it works well with a fresh Bacpac, but not after index rebuilds etc. It could be that statistics are more off with the fresh Bacpac, and with fullscan indexes later, the optimizer goes astray.

For a quick test, you could try the OPTIMIZER_ROW_GOAL_HINT again, but I would not be surprised if you say that it makes matters worse.

Often when you get problems like this, there are issues in the query itself, that needs to be addressed. As I mentioned, breaking up in pieces can help.

When fairly randomly moving around in this complex plan, I noticed one thing related to this JOIN condition:

LEFT JOIN [Confidential].[vwStxDLCUST] CUST ON saa.SOH_ACCOUNT = CUST.DLCUS_CUSTOMER

I get the feeling that SOH_ACCOUNT is nvarchar, while DLCUS_CUSTOMER is varchar, can you confirm or refute this? Furthermore, what is the collation SQL_Latin1_General_CP1_CI_AS?

Since I may be off-track, I will stop there for now, and come back when I have more information from you.
Anonymous

2026-02-09T10:08:02.96+00:00

Hi @Hemanth kumar Kotha

I hope you had a chance to review the information shared earlier, and I hope this information has been helpful! If you still have questions, please let us know what is needed in the comments so the question can be answered.

2 answers

Erland Sommarskog 133.9K Reputation points MVP Volunteer Moderator

2026-02-03T22:36:28.0666667+00:00
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.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Q&A Assist

2026-02-03T12:18:21.66+00:00

AI generated content. This question contains an answer created with AI from Q&A Assist. Learn more
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
Please sign in to rate this answer.

Share via

Az SQL DB performance issues

Query latency differs across environments (Dev vs UAT) – same stored procedure, different performance

Problem Summary

Question

Additional context

2 answers

Root Causes

Workarounds

Diagnostic Steps

Your answer