December 3, 2015 at 3:03 pm
We had a couple customers who are seeing deadlocks on their databases. It started just about the time of Windows Updates (!!!!). I purged the data and rebuilt the indices, but they're still seeing the issue. How else can I diagnose the problem?
Thank you in advance.
December 3, 2015 at 3:32 pm
Wrong query. sorry
December 3, 2015 at 5:15 pm
There are tons and tons of information (including the official documentation) available online on how to detect deadlocks. To name a few:
Detecting and Ending Deadlocks
Finding SQL Server Deadlocks Using Trace Flag 1222[/url]
Tracing a SQL Server Deadlock[/url]
Capturing Deadlocks in SQL Server [Video][/url]
Capturing Deadlocks in SQL Server[/url]
December 3, 2015 at 5:19 pm
randyomatta (12/3/2015)
Here's a good query to at least get you started.
Blocking is not the same as deadlock. And despite the fact that one can lead to another it would be practically impossible to detect a deadlock using the queries you've provided.
December 4, 2015 at 1:53 am
https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/
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
December 4, 2015 at 8:12 am
Thank you all for your prompt and thorough responses. However, this is my problem: The software is released. It's been working fine for years. To pinpoint the cause and change the code would prompt a lengthy QC process and the customer wants this fixed yesterday.
I have some idea where the problem is. My software pulls a series of readings from a bank of instruments. When the readings are inserted, an insert trigger scans the table for patterns. Because the only first batch of readings are successfully inserted, and the deadlocks present when a second batch of readings are trying to add (while the table is being scanned from the first batch), I presume that's our gremlin because the deadlocks go away when I disconnect the second instrument.
Besides changing code, is there something I can do to address this on the database end? I tried purging the database and rebuilding the operative indices, and DBCC CheckDB returned 0 errors. Should I try to compress the database? Should I reorganize the indices?
Thank you again.
P.S.
The error seems to happen around 15 seconds after the insert. I thought the database waited 30 seconds by default before reporting deadlocks.
December 4, 2015 at 12:20 pm
abright (12/4/2015)
I have some idea where the problem is.
Please start from getting a deadlock graph using one of the techniques described in the above links.
abright (12/4/2015)
Besides changing code, is there something I can do to address this on the database end?
Sometimes a deadlock issue can be resolved by creating some indexes but first you need to find out exactly what's going on. Get a deadlock graph.
abright (12/4/2015)
I tried purging the database and rebuilding the operative indices, and DBCC CheckDB returned 0 errors. Should I try to compress the database? Should I reorganize the indices?
The problem is in the logic within your application, so reorganizing indexes or moving them to another physical disk is not going to help.
abright (12/4/2015)
The error seems to happen around 15 seconds after the insert. I thought the database waited 30 seconds by default before reporting deadlocks.
Where are you getting this numbers?
https://msdn.microsoft.com/en-us/library/ms178104.aspx#Anchor_1
All of the resources listed in the section above participate in the Database Engine deadlock detection scheme. Deadlock detection is performed by a lock monitor thread that periodically initiates a search through all of the tasks in an instance of the Database Engine. The following points describe the search process:The default interval is 5 seconds.
If the lock monitor thread finds deadlocks, the deadlock detection interval will drop from 5 seconds to as low as 100 milliseconds depending on the frequency of deadlocks.
If the lock monitor thread stops finding deadlocks, the Database Engine increases the intervals between searches to 5 seconds..
December 4, 2015 at 12:26 pm
abright (12/4/2015)
To pinpoint the cause and change the code would prompt a lengthy QC process and the customer wants this fixed yesterday.
The earlier you start, the sooner it'll be done
I tried purging the database and rebuilding the operative indices, and DBCC CheckDB returned 0 errors. Should I try to compress the database? Should I reorganize the indices?
No, and no.
Deadlocks are almost always down to either code or inefficient indexes. Yes, you could have had the system working for years, but your data volumes have grown over that time and what were once fast queries on tiny amounts of data are now on larger amounts of data, hence are slower. Slower queries are more likely to deadlock.
Go through my article, identify the cause, put the necessary changes into QA and tell the customer that you're working on it as fast as possible
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply