Update Query Performance

Question

Update Query Performance

sqlrob

SSChasing Mays

Points: 607
More actions
March 15, 2017 at 11:59 pm

#336066

Hello Experts,
Following update Query takes about 1 Millisecond to update a Product reserve under less Load. But when thousands of users try to update the same product at the same time, it is taking up to 4 minutes. There is no Memory, CPU, Disk IO constaints.Do you have any suggestions to improve this performance of the Query under heavy load ?
UPDATE Inventory
SET reserved = reserved + 1
,Modtime = getdate()
WHERE PKey = 500 AND available + overselling - reserved > 5
go
Attached is the current Query plan . Let me know If you need more information.

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply

andreas.kreuzberg SSCertifiable Points: 6183 More actions · Answer 1

Hi,
did you change your "Lock Modes" on the database?
May the query from different users will lock the table.
Best regards,
Andreas

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

sqlrob - Wednesday, March 15, 2017 11:59 PM
Hello Experts,
Following update Query takes about 1 Millisecond to update a Product reserve under less Load. But when thousands of users try to update the same product at the same time, it is taking up to 4 minutes. There is no Memory, CPU, Disk IO constaints.Do you have any suggestions to improve this performance of the Query under heavy load ?
UPDATE Inventory
SET reserved = reserved + 1
,Modtime = getdate()
WHERE PKey = 500 AND available + overselling - reserved > 5
go
Attached is the current Query plan . Let me know If you need more information.

Please can you attach an actual execution plan as a .sqlplan file attachment? Thanks.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Most likely there is blocking. Can you check sys.dm_exec_requests when there's load and see what the query is waiting for?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sergiy SSC Guru Points: 110206 More actions · Answer 4

Such tables like that [Inventory] must not be tables, they must be views displaying aggregated values from base tables with "atomic" data.

You're being punished for violation normalization rules.

_____________
Code for TallyGenerator

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

Use sp_whoisactive to see what is happening in real time. Review the 30-day blog post series that Adam Machanic has on SQLBlog.com about how to use it. For example, if there is a blocking storm, you can find blocking leaders and how many threads are being blocked. You can see query plans. You can also run it in differential mode to capture work/waits by threads over a period of time such as 30 seconds.

I would also recommend doing differential file IO stall and wait stats analysis during a period of heavy load.

Since you mentioned thousands of concurrent users an obvious question is what is your server and IO configuration (and I need DETAILS on the IO part especially, not just "we have a C, D and E drive of these sizes"). I have had any number of clients that thought they could run huge workloads with crappy infrastructure.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

ScottPletcher SSC Guru Points: 100942 More actions · Answer 6

With thousands of users trying to modify the exact same row, SQL must do them one at a time, pausing the others until those before it complete. Otherwise, of course, the reserved count would be wrong. So some of the delay is due to waiting legitimately for others to finish.

Other things to possibly consider:

Is this UPDATE part of a larger transaction, or is it stand-alone? Naturally the smaller the trans, the less chance for blocking.

Are other tables modified in code before this UPDATE? For example, say orders and shipments tables. If other code updating Inventory also updates those other tables, this could lead to increased blocking (or even deadlocking).

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Sergiy SSC Guru Points: 110206 More actions · Answer 7

ScottPletcher - Thursday, March 16, 2017 9:54 AM
With thousands of users trying to modify the exact same row, SQL must do them one at a time, pausing the others until those before it complete. Otherwise, of course, the reserved count would be wrong. So some of the delay is due to waiting legitimately for others to finish.
Other things to possibly consider:
Is this UPDATE part of a larger transaction, or is it stand-alone? Naturally the smaller the trans, the less chance for blocking.
Are other tables modified in code before this UPDATE? For example, say orders and shipments tables. If other code updating Inventory also updates those other tables, this could lead to increased blocking (or even deadlocking).

Adding to the questions:
How many columns are on the table, how many indexes?
How many of them are affected by that particular UPDATE?
How likely the UPDATE to cause page splits on the indexes?
What is the clustered index on the table?
How many INSERTs are going on at the same time?
Do UPDATEs affect the range of clustered index which is affected by concurrent INSERTs?

_____________
Code for TallyGenerator

sqlrob SSChasing Mays Points: 607 More actions · Answer 8

andreas.kreuzberg - Thursday, March 16, 2017 4:29 AM
Hi,
did you change your "Lock Modes" on the database?
May the query from different users will lock the table.
Best regards,
Andreas

thanks Andreas

sqlrob SSChasing Mays Points: 607 More actions · Answer 9

ChrisM@Work - Thursday, March 16, 2017 5:19 AM
sqlrob - Wednesday, March 15, 2017 11:59 PM
Hello Experts,
Following update Query takes about 1 Millisecond to update a Product reserve under less Load. But when thousands of users try to update the same product at the same time, it is taking up to 4 minutes. There is no Memory, CPU, Disk IO constaints.Do you have any suggestions to improve this performance of the Query under heavy load ?
UPDATE Inventory
SET reserved = reserved + 1
,Modtime = getdate()
WHERE PKey = 500 AND available + overselling - reserved > 5
go
Attached is the current Query plan . Let me know If you need more information.
Please can you attach an actual execution plan as a .sqlplan file attachment? Thanks.

Hi Chris,
Thanks for looking at my Post. I have attached the SQL plan as requested.

sqlrob SSChasing Mays Points: 607 More actions · Answer 10

ScottPletcher - Thursday, March 16, 2017 9:54 AM
With thousands of users trying to modify the exact same row, SQL must do them one at a time, pausing the others until those before it complete. Otherwise, of course, the reserved count would be wrong. So some of the delay is due to waiting legitimately for others to finish.
Other things to possibly consider:
Is this UPDATE part of a larger transaction, or is it stand-alone? Naturally the smaller the trans, the less chance for blocking.
Are other tables modified in code before this UPDATE? For example, say orders and shipments tables. If other code updating Inventory also updates those other tables, this could lead to increased blocking (or even deadlocking).

Thanks scott.
This Transaction is standalone, not part of another bigger transaction. It does not Alter any other tables part of this Excercise.

sqlrob SSChasing Mays Points: 607 More actions · Answer 11

Sergiy - Thursday, March 16, 2017 2:57 PM
ScottPletcher - Thursday, March 16, 2017 9:54 AM
With thousands of users trying to modify the exact same row, SQL must do them one at a time, pausing the others until those before it complete. Otherwise, of course, the reserved count would be wrong. So some of the delay is due to waiting legitimately for others to finish.
Other things to possibly consider:
Is this UPDATE part of a larger transaction, or is it stand-alone? Naturally the smaller the trans, the less chance for blocking.
Are other tables modified in code before this UPDATE? For example, say orders and shipments tables. If other code updating Inventory also updates those other tables, this could lead to increased blocking (or even deadlocking).
Adding to the questions:
How many columns are on the table, how many indexes?
How many of them are affected by that particular UPDATE?
How likely the UPDATE to cause page splits on the indexes?
What is the clustered index on the table?
How many INSERTs are going on at the same time?
Do UPDATEs affect the range of clustered index which is affected by concurrent INSERTs?

Thanks Sergiy. Here is the answer for some of the question you have raised.

How many columns are on the table, how many indexes? 20 Columns, 1 Clustered and 3 Non-Clustered. But the Index Keys does not get updated during this update.
How many of them are affected by that particular UPDATE? 2 columns get updated due to this update. The product Reserve Value and DateAndTime of change. But these keys are not part of the Index.
What is the clustered index on the table? Primary key is SKU of the Product .
How many INSERTs are going on at the same time? No inserts, only updates. Table get refreshed once a day overnight.
Do UPDATEs affect the range of clustered index which is affected by concurrent INSERTs? No inserts happens during day time. During day time only Update happens and it changes the reserve value.

Sergiy SSC Guru Points: 110206 More actions · Answer 12

I cannot see SKU mentioned anywhere begore.

What is the correlation between SKU and PKey?

_____________
Code for TallyGenerator

sqlrob SSChasing Mays Points: 607 More actions · Answer 13

Sergiy - Friday, March 17, 2017 3:08 AM
I cannot see SKU mentioned anywhere begore.What is the correlation between SKU and PKey?

PKey stores the SKU

Sergiy SSC Guru Points: 110206 More actions · Answer 14

How many records per SKU?
Is it a unique key?
Is the uniqueness enforced through the UNIQUE constraint?

Apart from that - the logic of the query seems troubled.

_____________
Code for TallyGenerator