Update Query fail sometime in SQL server

Question

I am running a SQL query to update the column,

UPDATE sip

SET

ScheduleID = 123222

FROM Price sip

INNER JOIN

(

SELECT

sip.ItemPriceID

FROM Charge sipc

INNER JOI Price sip ON sip.ItemPriceID = sipc.ItemPriceID

INNER JOIN CtlCycle ctl ON ctl.BillingRunID = @BillingRunID

WHERE sipc.BillingRunID = @BillingRunID

AND CONVERT(DATE, BilledThruDate) >= CONVERT(DATE, DATEADD(DAY, -1, sipc.ItemPriceEndDate))

AND ISNULL(sip.OrderDate, '2000-01-01') <= ctl.OrdersThruDate

GROUP BY

sip.ItemPriceID

)AS t ON t.ItemPriceID = sip.ItemPriceID

It works fine in one environment and failed in another environment with the SQL timeout error.

I check the table charge having lots of null in columns ItemPriceEndDate, and if I add null check for column ItemPriceEndDate it works(AND sipc.ItemPriceEndDate is not null) fine.

The Question :

Why it works in one environment without any change and failed in another environment. Have the same amount of data in both environments.

Answer 1

Unless there is a blocking issue, you apparently have different execution plans in the two environments. This is can be for a quite a few reasons:

1. Different set of indexes.
2. Different statistics information.
3. Different server configuration for parallelism.
4. Different amount of memory available.

And that is not an exhaustive list.

You will need to capture the query plans to work further.

Answer 2

Hi @Sanjay Kumar ,

Welcome to Microsoft Q&A!

Please check whether the failed one had any deadlock or open transactions which might cause timeout error and also check whether both environment had the same indexes, execution plans,settings and so on.

You could also try with below statement:

UPDATE sip  
SET ScheduleID = 123222  
FROM Price sip  
INNER JOIN Charge  sipc ON sip.ItemPriceID = sipc.ItemPriceID  
INNER JOIN CtlCycle ctl ON ctl.BillingRunID = sipc.BillingRunID  
WHERE sipc.BillingRunID = @BillingRunID  
AND CONVERT(DATE, BilledThruDate) >= CONVERT(DATE, DATEADD(DAY, -1, ISNULL(sipc.ItemPriceEndDate,'2000-01-01')))  
AND ISNULL(sip.OrderDate, '2000-01-01') <= ctl.OrdersThruDate  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

failed in another environment with the SQL timeout error

A command timeout isn't raised by SQL Server, it's raised by the client, when query execution elapse more then a defined time out; default is 30 seconds.
Optimize the query or increase the timeout period for the application.

Answer 4

Hi @Sanjay Kumar ,

Thanks for your update.

Since you mentioned that the same query sometimes works in a failing environment, there may be some problems in other aspects than the query itself.

As mentioned by Erland, you could check below while it is running:

1. Find out any Blocking.
2. Display Execution Plans and compare with each other.
3. Find out any memory/CPU/disk related issue by using performance counters.

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.