May 21, 2025 at 2:18 am
Hey everyone, please excuse my ignorance, I'm just .NET developer and have used SQL for years, but as someone pulling and manipulating data, but not much in the realm of administration or in this case troubleshooting deadlocks. I've tried to get my higher ups to get a db expert but it seems this task has fallen to me.
Anyways, my first basic question is, can a sql backup cause a deadlock? From some of the logs I've seen this seems to be the case, but I didn't think a backup would do this. Users are still using the application but it's at night. Obviously manual backups do the same thing, but not tons of deadlocks. More like one or two out of many transactions. So I'm not sure if this is something that can or needs to be fixed (I would like to) or if it's a symptom of a bigger problem. Can anyone provide a link to any diagnostic tools or how-tos? I do have a script to show deadlocks, but I'm not really sure how to read it. This isn't something I do everyday. Any help would be appreciated.
We are also getting timeout exceptions and lock errors but I wanted to start with the backup question first as there are many issues.
May 21, 2025 at 6:22 am
No, backups do not cause the deadlocks. As it happens at night, check other jobs, likely you have index rebuild/reorg, update stats etc. These jobs are more likely to cause the trouble. If you don't find anything let me know, there are scripts to get full details about the deadlock paticipants or if you have the details, share the deadlock XML
May 21, 2025 at 1:05 pm
Deadlocks are, at least in part, a performance problem. If all the transactions occur fast enough, the deadly embrace is less likely to occur and deadlocks are avoided. Yes, fundamentally it's about getting your code right, not performance, but still, you get the idea. So, the question could be asked, do backups affect performance? The answer is, yeah, some. Resources are needed, and used, by the backup process. As already said, they won't cause deadlocks. However, while running, you might see an increase in the number of deadlocks because transactions will be running slower, and therefore more likely to hit the deadly embrace.
PLEASE NOTE: I'm 100% not saying, avoid backups. I'm just saying, yeah, you might see an increase in the rate of deadlocks while backups are running. The answer remains, fix the code. Always.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 21, 2025 at 1:44 pm
Thanks for the replies!
I was able to figure out how to run a trace using SQL profiler, so that is running for deadlocks. Nothing yet. I did get a deadlock earlier this morning but that was before I started the trace. It gives me process ids but I'm not sure how to find the culprit. There are a lot of transactions going on this ERP system. There are also multiple developers from different companies hitting this db so that is a challenge as well.
They do have some jobs set up and an indexoptimize job. From the error logs there isn't anything that says error or indicating something is wrong other than the deadlock this morning. I'll post the xml if I get another lock.
May 21, 2025 at 2:36 pm
You may try setting up deadlock monitoring with extended events https://www.mssqltips.com/sqlservertip/5658/capturing-sql-server-deadlocks-using-extended-events/
You may adjust IndexOptimize to only maintain statistics instead of rebuilding/reorganizing indexes ( those are intensive with low gains) (* insert reference here )
May 21, 2025 at 2:46 pm
Strong suggestion, don't use Profiler. The GUI puts considerable load on a server. Trace Events, the underlying technology, aren't all that kind to a server either. Instead, use Extended Events.
Also, if you're running SQL Server 2008 or greater, you can use the system_health extended events session to look at recent deadlocks. It already captures them. Here's how to do it.
Otherwise, you can set up Extended Events same as Profiler to capture deadlocks. It's safer for your systems, I assure you.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 21, 2025 at 3:30 pm
Ok so, I was able to run a query of deadlocks (didn't know you could do that) from the link Grant gave me. I can tell which tables are affected, but how can I interpret this xml? I starred out the database name.
Turns out I got 385 rows from that query.
WITH fxd
AS (SELECT CAST(fx.event_data AS XML) AS Event_Data
FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL) AS fx )
SELECT dl.deadlockgraph
FROM
(
SELECT dl.query('.') AS deadlockgraph
FROM fxd
CROSS APPLY event_data.nodes('(/event/data/value/deadlock)') AS d(dl)
) AS dl;
<deadlock>
<victim-list>
<victimProcess id="process2edc2af44e8" />
</victim-list>
<process-list>
<process id="process2edc2af44e8" taskpriority="0" logused="182392" waitresource="KEY: 14:72057595091943424 (86af9ce6d33b)" waittime="2860" ownerId="4560495065" transactionname="user_transaction" lasttranstarted="2025-05-21T10:36:20.210" XDES="0x2f931288428" lockMode="U" schedulerid="14" kpid="20636" status="suspended" spid="114" sbid="2" ecid="0" priority="0" trancount="1" lastbatchstarted="2025-05-21T10:36:23.013" lastbatchcompleted="2025-05-21T10:36:23.013" lastattention="1900-01-01T00:00:00.013" clientapp="Core .Net SqlClient Data Provider" hostname="" hostpid="18384" loginname="" isolationlevel="read committed (2)" xactid="4560495065" currentdb="14" currentdbname="" lockTimeout="180000" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="174" stmtend="5286" sqlhandle="0x020000007649401c14d86a436a4355a82f1b650c81e9acee0000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000),@p__linq__2 int,@p__linq__3 int)SELECT
[Extent1].[Company] AS [Company],
[Extent1].[PartNum] AS [PartNum],
[Extent1].[WarehouseCode] AS [WarehouseCode],
[Extent1].[BinNum] AS [BinNum],
[Extent1].[SupplyJobNum] AS [SupplyJobNum],
[Extent1].[OrderNum] AS [OrderNum],
[Extent1].[OrderLine] AS [OrderLine],
[Extent1].[OrderRelNum] AS [OrderRelNum],
[Extent1].[JobNum] AS [JobNum],
[Extent1].[AssemblySeq] AS [AssemblySeq],
[Extent1].[MtlSeq] AS [MtlSeq],
[Extent1].[TFOrdNum] AS [TFOrdNum],
[Extent1].[TFOrdLine] AS [TFOrdLine],
[Extent1].[LotNum] AS [LotNum],
[Extent1].[DimCode] AS [DimCode],
[Extent1].[ReservedQty] AS [ReservedQty],
[Extent1].[AllocatedQty] AS [AllocatedQty],
[Extent1].[PickingQty] AS [PickingQty],
[Extent1].[PickedQty] AS [PickedQty],
[Extent1].[DemandType] AS [DemandType],
[Extent1].[DistributionType] AS [DistributionTy </inputbuf>
</process>
<process id="process2f9e8d73848" taskpriority="0" logused="198624" waitresource="KEY: 14:72057832938209280 (61671c8ab14f)" waittime="2825" ownerId="4559923434" transactionname="user_transaction" lasttranstarted="2025-05-21T10:36:09.410" XDES="0x2ea7aa5c428" lockMode="U" schedulerid="16" kpid="4116" status="suspended" spid="989" sbid="2" ecid="0" priority="0" trancount="1" lastbatchstarted="2025-05-21T10:36:22.173" lastbatchcompleted="2025-05-21T10:36:22.173" lastattention="1900-01-01T00:00:00.173" clientapp="Core .Net SqlClient Data Provider" hostname="" hostpid="18384" loginname="" isolationlevel="read committed (2)" xactid="4559923434" currentdb="14" currentdbname="" lockTimeout="180000" clientoption1="671088672" clientoption2="128056">
<executionStack>
<frame procname="adhoc" line="1" stmtstart="88" stmtend="6078" sqlhandle="0x020000000e9750094022802803d2f8a1af0e214326fdfcf20000000000000000000000000000000000000000">
unknown </frame>
<frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000">
unknown </frame>
</executionStack>
<inputbuf>
(@p__linq__0 nvarchar(4000),@p__linq__1 int)SELECT TOP (1)
[Extent1].[Company] AS [Company],
[Extent1].[Type] AS [Type],
[Extent1].[PartNum] AS [PartNum],
[Extent1].[DueDate] AS [DueDate],
[Extent1].[RequirementFlag] AS [RequirementFlag],
[Extent1].[Quantity] AS [Quantity],
[Extent1].[JobNum] AS [JobNum],
[Extent1].[AssemblySeq] AS [AssemblySeq],
[Extent1].[JobSeq] AS [JobSeq],
[Extent1].[OrderNum] AS [OrderNum],
[Extent1].[OrderLine] AS [OrderLine],
[Extent1].[OrderRelNum] AS [OrderRelNum],
[Extent1].[PONum] AS [PONum],
[Extent1].[POLine] AS [POLine],
[Extent1].[PORelNum] AS [PORelNum],
[Extent1].[PartDescription] AS [PartDescription],
[Extent1].[IUM] AS [IUM],
[Extent1].[SourceFile] AS [SourceFile],
[Extent1].[CustNum] AS [CustNum],
[Extent1].[StockTrans] AS [StockTrans],
[Extent1].[FirmRelease] AS [FirmRelease],
[Extent1].[RevisionNum] AS [RevisionNum],
[Extent1].[Targe </inputbuf>
</process>
</process-list>
<resource-list>
<keylock hobtid="72057595091943424" dbid="14" objectname="*.Erp.PartAlloc" indexname="PK_PartAlloc" id="lock2f989acdf00" mode="X" associatedObjectId="72057595091943424">
<owner-list>
<owner id="process2f9e8d73848" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process2edc2af44e8" mode="U" requestType="wait" />
</waiter-list>
</keylock>
<keylock hobtid="72057832938209280" dbid="14" objectname="*.Erp.PartDtl" indexname="IX_PartDtl_PartDtlSeq" id="lock30780319080" mode="X" associatedObjectId="72057832938209280">
<owner-list>
<owner id="process2edc2af44e8" mode="X" />
</owner-list>
<waiter-list>
<waiter id="process2f9e8d73848" mode="U" requestType="wait" />
</waiter-list>
</keylock>
</resource-list>
</deadlock>
May 21, 2025 at 4:34 pm
you have deadlock between 2 similar processes running
(@p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000),@p__linq__2 int,@p__linq__3 int)SELECT ...
and
(@p__linq__0 nvarchar(4000),@p__linq__1 int)SELECT TOP (1)...
from .net on dbid =14
As 2 selects in read committed mode dont lock each other, I assume your processes are running something like
begin tran
some isnerts or updates or deletes...
that select you see in XML
commit tran
so they intersect not with each other, but with previous statements.
locked object is: objectname="*.Erp.PartDtl" indexname="IX_PartDtl_PartDtlSeq"
May 22, 2025 at 12:09 pm
Last question - Is there not a way to go deeper to see the whole transactions that took place? The provided statements give me the tables, but the system gets transactions from all over and I wouldn't know where this is coming from exactly.
May 22, 2025 at 12:30 pm
You can. However, it's work to get it set up well so you get the info you want without putting too much load on the system.
Extended Events has a thing called Causality Tracking. You can, for example, put together three events, rpc_starting, rpc_complete, and the either xml_deadlock_report or database_xml_deadlock_report (lets you narrow down what's captured, a very good thing). Causality tracking will then put them into groups based on a GUID. So, you see rpc_starting, and then a deadlock (won't see complete for obvious reasons), you can figure out exactly which procedure caused it. You can then also look at adding either batch start & complete or individual statements start & stop so you can narrow down further, when and where the deadlock happened. You can also add detail such as the lock_deadlock, to see the locks involved, or lock_deadlock_chain, to see the chain of locks.
A few points worth noting. All this adds load to the system. The more events you capture, the more load. Causality tracking alone adds additional load too. There ain't no such thing as a free lunch. Also, without good filtering, you could be overwhelmed by the amount of data you retrieve, making it less useful. So you'll want to experiment with filtering. Or, you can look at turning it on for short periods, maybe when the deadlocks are most likely to occur, and then turning it off again. Or both.
Hope that helps a little.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 22, 2025 at 12:45 pm
awesome, thanks Grant! I'll look into it. Right now we need to find out what the issues are so the load issue is ok for now while we troubleshoot. I don't envision keeping this on long term. Everyone has been super helpful!
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply