Click here to Skip to main content
15,562,015 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
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
Updated 17-Feb-22 7:05am
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.

1 solution

Answered only to remove from unanswered queue: solved in comments.
Share this answer
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

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900