February 3, 2016 at 4:14 am
Hi Experts,
I have observed deadlock graph in one of our servers. It was due to a procedure which is doing an update.
ALTER PROCEDURE [dbo].[MessageLogs]
@TrackID nvarchar(100)=NUll,
@status nvarchar(20)=NUll,
@Description nvarchar(max)=NUll
AS
BEGIN
SET NOCOUNT ON;
Update Logs
set
Status=@Status,
statusDescription =COALESCE(statusDescription,'')+'\\'+ @Description
where Trackid=@TrackID
The trackID is always unique, what can be the reason for deadlock. The page ID is also different for the process involved in Deadlock.
February 3, 2016 at 4:37 am
Please post the deadlock graph (either the text form or attach the XML file)
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
February 3, 2016 at 4:44 am
Thanks Gail for the quick reply. Was going through your old post on Deadlock query.
Attached the xml . please have a look.
February 3, 2016 at 4:49 am
The trackID is always unique
Is there a unique index or unique constraint on the column? Why does the table not have a clustered index? What indexes does it have?
Can you attach the execution plan?
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
February 3, 2016 at 5:05 am
GilaMonster (2/3/2016)
The trackID is always unique
Is there a unique index or unique constraint on the column? Why does the table not have a clustered index? What indexes does it have?
Can you attach the execution plan?
No Constraint or index exists on that table. This was part of BizTalk custom database.
February 3, 2016 at 5:12 am
If there are no constraints or indexes, how is SQL supposed to figure out that trackID is unique? Without indexes, you're going to be getting full table scans, hence the deadlock.
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
February 3, 2016 at 5:21 am
GilaMonster (2/3/2016)
If there are no constraints or indexes, how is SQL supposed to figure out that trackID is unique? Without indexes, you're going to be getting full table scans, hence the deadlock.
Thanks Gail. Included plan.
February 3, 2016 at 5:24 am
Thanks Gail
February 3, 2016 at 5:25 am
Yup, you're getting full table scans each time you run that update, because without any indexes at all, SQL has to scan the table because it's got no other access method and it doesn't know whether 1 row or every row will be affected by the update.
If TrackID is unique, go put a unique constraint on it. And put a useful clustered index on the table while you're at it.
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
February 3, 2016 at 6:06 am
GilaMonster (2/3/2016)
Yup, you're getting full table scans each time you run that update, because without any indexes at all, SQL has to scan the table because it's got no other access method and it doesn't know whether 1 row or every row will be affected by the update.If TrackID is unique, go put a unique constraint on it. And put a useful clustered index on the table while you're at it.
Thanks Gail.
Do need to include column on Index?
February 3, 2016 at 6:08 am
You can't put include columns in clustered indexes or constraints.
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
February 3, 2016 at 6:10 am
GilaMonster (2/3/2016)
You can't put include columns in clustered indexes or constraints.
On Non-Clustered index i mean.
February 3, 2016 at 6:11 am
Well, if you want to go and create extra nonclustered indexes, then whether you put include columns or not would depend on what purpose you're creating them for. I can't answer that one.
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
February 3, 2016 at 6:13 am
GilaMonster (2/3/2016)
Well, if you want to go and create extra nonclustered indexes, then whether you put include columns or not would depend on what purpose you're creating them for. I can't answer that one.
So you meant to say it is better to create a Clustered index on that particular column?
February 3, 2016 at 6:17 am
So you meant to say it is better to create a Clustered index on that particular column?
No. If I'd meant to say that, I'd have said that.
If TrackID is unique, go put a unique constraint on it. And put a useful clustered index on the table while you're at it.
Neither unique constraints nor clustered indexes can get include columns, and I said nothing about other nonclustered indexes. If you judge that other nonclustered indexes may be useful, then go ahead and add them with whatever columns you judge to be useful.
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 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply