Sql server 2014: 1 row update is very slow

Question

5.00/5 (1 vote)

See more:

In a SQL Server 2014 database we do a 1 row update by setting 1 bit field to 1 where Id = <value>. This means a seek over the PK.

It takes over 50 seconds for that update.

Inserts fly in the database, selects perform fast, only updates are slow.

Last Thursday evening the service of SQL Service was restarted (so no stop, wait a bit and start).

We have 9 databases run in this server only 1 database has this issue.

What is the problem with the updates since that restart?
Any recognize this behaviour?

What I have tried:

- Reindex all indexes
- run same query from SSMS with same result
- Rebuilding all indexes
- Restart the service broker but we still have suspending jobs

Posted 23-Nov-21 2:53am

Herman<T>.Instance

Updated 17-Feb-22 6:05am

v2

Add a Solution

Comments

CHill60 23-Nov-21 10:40am

That bit field isn't part of any of these indexes is it?

Herman<T>.Instance 23-Nov-21 10:41am

yes, there is 1 index on it

Herman<T>.Instance 23-Nov-21 10:45am

But drop of that index does not solve the issue

CHill60 23-Nov-21 11:17am

How about just dropping the bit field from the index and leaving the id field in?

Herman<T>.Instance 23-Nov-21 11:42am

the id field is in the PK index, the bitfield in a separate index.

Herman<T>.Instance 23-Nov-21 11:43am

Somehow the queryplan turns over into parallelism when doing an update

Maciej Los 23-Nov-21 14:25pm

Try to shrink database. I'd try to checkdb also.

Herman<T>.Instance 24-Nov-21 3:29am

We have done a restart of the DB services and the issue was solved. Thanks for helping out

Maciej Los 24-Nov-21 5:02am

You're very welcome.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2022-02-17T06:04:00

Solution 2

Answered only to remove from unanswered queue: solved in comments.

Posted 17-Feb-22 6:04am

OriginalGriff

Comments

Herman<T>.Instance 20-Feb-22 5:21am

I am waiting from answers from Microsoft. Might have found strange side effects on using SqlDependency in C# and Sql Server Server Broker. When confirmed CP will get a nice article