June 1, 2010 at 8:12 am
I am trying to fix a dev issue before it becomes a prod issue. we have a small working table that is used by a vendor application. it keeps track of current "requests" and had many deletes and updates. <300 rows. SQL escalates the locks to a page lock (the vendor wrote them with the ROWLOCK hint - so they had to know it would be an issue) but SQL escalates and another delete hits. boom. deadlock. The table had about 7 indexes on it,so in dev I took it down to 1. It was deadlocking before on the clustered index so I made it into a heap. thoughts?
June 1, 2010 at 8:32 am
Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.
DBCC TRACEON(1222,-1)
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
June 1, 2010 at 8:36 am
I have those flags and can see the deadlock. The issue is the lock escalation. If it would just lock the row when update or delete all would be well. I am looking to find out what would be a way to index this table properly for a small table with high deletes/updates.
June 1, 2010 at 8:43 am
It sounds to me that you're going about this the wrong way. You're focused on a single solution to the problem (i.e., that it was indexes) without actually checking the query and the query plan over. If I'm wrong about this, I apologize. But here's what I suggest.
Start up Profiler, include the exection plan in the trace, and then run the query that's causing your problem. This combination might give you a hint as to why the query is causing the deadlock issue.
June 1, 2010 at 8:44 am
Post the deadlock graph please, along with the definition of the table with all indexes, and the exact queries that are deadlocking
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
June 1, 2010 at 8:58 am
here is from the log...
Same query. A simple one at that.
2010-06-01 04:04:52.03 spid4s Deadlock encountered .... Printing deadlock information
2010-06-01 04:04:52.03 spid4s Wait-for graph
2010-06-01 04:04:52.03 spid4s
2010-06-01 04:04:52.03 spid4s Node:1
2010-06-01 04:04:52.03 spid4s RID: 5:1:58949:9 CleanCnt:3 Mode:X Flags: 0x2
2010-06-01 04:04:52.03 spid4s Grant List 0:
2010-06-01 04:04:52.03 spid4s Owner:0x45EA22C0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:119 ECID:0 XactLockInfo: 0x35D918AC
2010-06-01 04:04:52.03 spid4s SPID: 119 ECID: 0 Statement Type: DELETE Line #: 1
2010-06-01 04:04:52.03 spid4s Input Buf: Language Event: DELETE FROM FYI_MONITOR WITH(ROWLOCK) WHERE FYI_DKEY = 213355 AND FYI_VER_MAJOR = -1 AND FYI_VER_MINOR = -1 AND FYI_SERVICE_CUSTOM = '1.241'
2010-06-01 04:04:52.03 spid4s Requested By:
2010-06-01 04:04:52.03 spid4s ResType:LockOwner Stype:'OR'Xdes:0x167FA250 Mode: U SPID:120 BatchID:0 ECID:0 TaskProxy:(0x2CDD6378) Value:0x27ea1f80 Cost:(0/6856)
2010-06-01 04:04:52.03 spid4s
2010-06-01 04:04:52.03 spid4s Node:2
2010-06-01 04:04:52.03 spid4s RID: 5:1:58949:8 CleanCnt:2 Mode:X Flags: 0x2
2010-06-01 04:04:52.03 spid4s Grant List 0:
2010-06-01 04:04:52.03 spid4s Owner:0x27EA07E0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:120 ECID:0 XactLockInfo: 0x167FA274
2010-06-01 04:04:52.03 spid4s SPID: 120 ECID: 0 Statement Type: DELETE Line #: 1
2010-06-01 04:04:52.03 spid4s Input Buf: Language Event: DELETE FROM FYI_MONITOR WITH(ROWLOCK) WHERE FYI_DKEY = 212951 AND FYI_VER_MAJOR = -1 AND FYI_VER_MINOR = -1 AND FYI_SERVICE_CUSTOM = '1.416'
2010-06-01 04:04:52.03 spid4s Requested By:
2010-06-01 04:04:52.03 spid4s ResType:LockOwner Stype:'OR'Xdes:0x35D91888 Mode: U SPID:119 BatchID:0 ECID:0 TaskProxy:(0x2DF80378) Value:0x45ea3e20 Cost:(0/6856)
2010-06-01 04:04:52.03 spid4s
2010-06-01 04:04:52.03 spid4s Victim Resource Owner:
2010-06-01 04:04:52.03 spid4s ResType:LockOwner Stype:'OR'Xdes:0x35D91888 Mode: U SPID:119 BatchID:0 ECID:0 TaskProxy:(0x2DF80378) Value:0x45ea3e20 Cost:(0/6856)
2010-06-01 04:04:52.06 spid21s deadlock-list
2010-06-01 04:04:52.06 spid21s deadlock victim=process83be28
2010-06-01 04:04:52.06 spid21s process-list
2010-06-01 04:04:52.06 spid21s process id=process83b2e8 taskpriority=0 logused=6856 waitresource=RID: 5:1:58949:9 waittime=4234 ownerId=672875447 transactionname=implicit_transaction lasttranstarted=2010-06-01T04:04:47.710 XDES=0x167fa250 lockMode=U schedulerid=1 kpid=48676 status=suspended spid=120 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2010-06-01T04:04:47.793 lastbatchcompleted=2010-06-01T04:04:47.783 clientapp=FYIJAS hostname=VMD-PERSIMGAUT hostpid=1304 loginname=FYIADM isolationlevel=read committed (2) xactid=672875447 currentdb=5 lockTimeout=20000 clientoption1=671088672 clientoption2=128058
2010-06-01 04:04:52.06 spid21s executionStack
2010-06-01 04:04:52.06 spid21s frame procname=adhoc line=1 sqlhandle=0x020000005b82603a390947fe0f36294e377b0aac93336198
2010-06-01 04:04:52.06 spid21s (@1 int,@2 smallint,@3 smallint,@4 varchar(8000))DELETE [FYI_MONITOR] WITH(rowlock) WHERE [FYI_DKEY]=@1 AND [FYI_VER_MAJOR]=@2 AND [FYI_VER_MINOR]=@3 AND [FYI_SERVICE_CUSTOM]=@4
2010-06-01 04:04:52.06 spid21s frame procname=adhoc line=1 sqlhandle=0x02000000d4d42338ebb42681a93e81f373306ec20079946d
2010-06-01 04:04:52.06 spid21s DELETE FROM FYI_MONITOR WITH(ROWLOCK) WHERE FYI_DKEY = 212951 AND FYI_VER_MAJOR = -1 AND FYI_VER_MINOR = -1 AND FYI_SERVICE_CUSTOM = '1.416'
2010-06-01 04:04:52.06 spid21s frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
2010-06-01 04:04:52.06 spid21s unknown
2010-06-01 04:04:52.06 spid21s inputbuf
2010-06-01 04:04:52.06 spid21s DELETE FROM FYI_MONITOR WITH(ROWLOCK) WHERE FYI_DKEY = 212951 AND FYI_VER_MAJOR = -1 AND FYI_VER_MINOR = -1 AND FYI_SERVICE_CUSTOM = '1.416'
2010-06-01 04:04:52.06 spid21s process id=process83be28 taskpriority=0 logused=6856 waitresource=RID: 5:1:58949:8 waittime=4234 ownerId=672875439 transactionname=implicit_transaction lasttranstarted=2010-06-01T04:04:47.620 XDES=0x35d91888 lockMode=U schedulerid=1 kpid=53252 status=suspended spid=119 sbid=0 ecid=0 priority=0 transcount=2 lastbatchstarted=2010-06-01T04:04:47.797 lastbatchcompleted=2010-06-01T04:04:47.797 clientapp=FYIJAS hostname=VMD-PERSIMGAUT hostpid=1304 loginname=FYIADM isolationlevel=read committed (2) xactid=672875439 currentdb=5 lockTimeout=20000 clientoption1=671088672 clientoption2=128058
2010-06-01 04:04:52.06 spid21s executionStack
2010-06-01 04:04:52.06 spid21s frame procname=adhoc line=1 sqlhandle=0x020000005b82603a390947fe0f36294e377b0aac93336198
2010-06-01 04:04:52.06 spid21s (@1 int,@2 smallint,@3 smallint,@4 varchar(8000))DELETE [FYI_MONITOR] WITH(rowlock) WHERE [FYI_DKEY]=@1 AND [FYI_VER_MAJOR]=@2 AND [FYI_VER_MINOR]=@3 AND [FYI_SERVICE_CUSTOM]=@4
2010-06-01 04:04:52.06 spid21s frame procname=adhoc line=1 sqlhandle=0x02000000e0e0391d04dad99b0fbe820bad03d57a89f1b53e
2010-06-01 04:04:52.06 spid21s DELETE FROM FYI_MONITOR WITH(ROWLOCK) WHERE FYI_DKEY = 213355 AND FYI_VER_MAJOR = -1 AND FYI_VER_MINOR = -1 AND FYI_SERVICE_CUSTOM = '1.241'
2010-06-01 04:04:52.06 spid21s frame procname=unknown line=1 sqlhandle=0x000000000000000000000000000000000000000000000000
2010-06-01 04:04:52.06 spid21s unknown
2010-06-01 04:04:52.06 spid21s inputbuf
2010-06-01 04:04:52.06 spid21s DELETE FROM FYI_MONITOR WITH(ROWLOCK) WHERE FYI_DKEY = 213355 AND FYI_VER_MAJOR = -1 AND FYI_VER_MINOR = -1 AND FYI_SERVICE_CUSTOM = '1.241'
2010-06-01 04:04:52.06 spid21s resource-list
2010-06-01 04:04:52.06 spid21s ridlock fileid=1 pageid=58949 dbid=5 objectname=IMGDBUAT.FYIADM.FYI_MONITOR id=lock37e125c0 mode=X associatedObjectId=72057594543276032
2010-06-01 04:04:52.06 spid21s owner-list
2010-06-01 04:04:52.06 spid21s owner id=process83b2e8 mode=X
2010-06-01 04:04:52.06 spid21s waiter-list
2010-06-01 04:04:52.06 spid21s waiter id=process83be28 mode=U requestType=wait
2010-06-01 04:04:52.06 spid21s ridlock fileid=1 pageid=58949 dbid=5 objectname=IMGDBUAT.FYIADM.FYI_MONITOR id=lock37e12d40 mode=X associatedObjectId=72057594543276032
2010-06-01 04:04:52.06 spid21s owner-list
2010-06-01 04:04:52.06 spid21s owner id=process83be28 mode=X
2010-06-01 04:04:52.06 spid21s waiter-list
2010-06-01 04:04:52.06 spid21s waiter id=process83b2e8 mode=U requestType=wait
June 1, 2010 at 9:00 am
also currently the table has 78 rows.
June 1, 2010 at 9:01 am
She's not asking for the log file. Do you know how to do a deadlock graph?
If not, let us know and we can walk you through it. If so, please do as Gila asked, otherwise, we'll be unable to help you further.
June 1, 2010 at 9:04 am
USE [IMGDBUAT]
GO
/****** Object: Table [FYIADM].[FYI_MONITOR] Script Date: 06/01/2010 11:01:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [FYIADM].[FYI_MONITOR](
[FYI_RKEY] [int] NOT NULL,
[FYI_SERVICE_TYPE] [varchar](32) NOT NULL,
[FYI_SERVICE_SUBTYP] [varchar](255) NULL,
[FYI_PRIORITY] [smallint] NULL,
[FYI_DATE_CREATED] [varchar](10) NOT NULL,
[FYI_TIME_CREATED] [varchar](8) NOT NULL,
[FYI_CUSTOM] [varchar](2000) NULL,
[FYI_FKEY] [int] NOT NULL,
[FYI_DKEY] [int] NOT NULL,
[FYI_VER_MAJOR] [int] NOT NULL,
[FYI_VER_MINOR] [int] NOT NULL,
[FYI_SERVICE_STATUS] [varchar](32) NULL,
[FYI_SERVICE_OWNER] [varchar](32) NULL,
[FYI_SERVICE_DATE] [varchar](10) NULL,
[FYI_SERVICE_TIME] [varchar](8) NULL,
[FYI_SERVICE_CUSTOM] [varchar](2000) NULL,
[FYI_CUSTOM_WORKER_DATA] [text] NULL,
[FYI_RETRY] [smallint] NULL,
[FYI_PROCESS_TIME] [varchar](16) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
--currently the index is
USE [IMGDBUAT]
GO
/****** Object: Index [NDX_MONITOR_KEY] Script Date: 06/01/2010 11:02:23 ******/
CREATE UNIQUE NONCLUSTERED INDEX [NDX_MONITOR_KEY] ON [FYIADM].[FYI_MONITOR]
(
[FYI_RKEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
--we can change the index, that was a test one. The indexes it had previously and were deadlocking also were:
/****** Object: Index [NDX_MINITOR_CST] Script Date: 06/01/2010 11:04:05 ******/
CREATE NONCLUSTERED INDEX [NDX_MINITOR_CST] ON [FYIADM].[FYI_MONITOR]
(
[FYI_SERVICE_CUSTOM] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [NDX_MINITOR_TIM] Script Date: 06/01/2010 11:04:05 ******/
CREATE NONCLUSTERED INDEX [NDX_MINITOR_TIM] ON [FYIADM].[FYI_MONITOR]
(
[FYI_PROCESS_TIME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [NDX_MONITOR_KEY] Script Date: 06/01/2010 11:04:05 ******/
CREATE UNIQUE NONCLUSTERED INDEX [NDX_MONITOR_KEY] ON [FYIADM].[FYI_MONITOR]
(
[FYI_RKEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [NDX_MONITOR_OWN] Script Date: 06/01/2010 11:04:05 ******/
CREATE NONCLUSTERED INDEX [NDX_MONITOR_OWN] ON [FYIADM].[FYI_MONITOR]
(
[FYI_SERVICE_OWNER] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [NDX_MONITOR_TYP] Script Date: 06/01/2010 11:04:05 ******/
CREATE NONCLUSTERED INDEX [NDX_MONITOR_TYP] ON [FYIADM].[FYI_MONITOR]
(
[FYI_SERVICE_TYPE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [OPERS_IX_FYI_MONITOR] Script Date: 06/01/2010 11:04:05 ******/
CREATE NONCLUSTERED INDEX [OPERS_IX_FYI_MONITOR] ON [FYIADM].[FYI_MONITOR]
(
[FYI_DKEY] ASC
)
INCLUDE ( [FYI_SERVICE_CUSTOM]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO
/****** Object: Index [PK_FYI_MONITOR] Script Date: 06/01/2010 11:04:05 ******/
ALTER TABLE [FYIADM].[FYI_MONITOR] ADD CONSTRAINT [PK_FYI_MONITOR] PRIMARY KEY CLUSTERED
(
[FYI_RKEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
June 1, 2010 at 9:13 am
Brandie Tarvin (6/1/2010)
She's not asking for the log file. Do you know how to do a deadlock graph?If not, let us know and we can walk you through it. If so, please do as Gila asked, otherwise, we'll be unable to help you further.
I know how to do a deadlock graph. I also read what she said:
Switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here
so umm.. thats what I output from the log.. as she requests.
Its a trace flag.
oh by the way
MCDBA, MCITP(DBA) 2005, MCITP(2008)
June 1, 2010 at 9:21 am
Where was the clustered index before you removed it?
Most of those nonclustered indexes are useless, they're too narrow. Other than the queries referenced in the deadlock graph, what other queries execute against this table?
Why are there dates and times stored in separate columns with the varchar data type?
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
June 1, 2010 at 9:35 am
GilaMonster (6/1/2010)
Where was the clustered index before you removed it?Most of those nonclustered indexes are useless, they're too narrow. Other than the queries referenced in the deadlock graph, what other queries execute against this table?
Why are there dates and times stored in separate columns with the varchar data type?
There was a clustered index on the field [FYI_RKEY]
The queries are pretty limited. I have listed examples from a trace I ran during atime when we were having issues. 90% of the time a query similar to the first one (the update) is being run
The seperation of the dates and times is how the product comes. We are allow to change indexes to the root product but not the structure. It is a shirkwrap item.
UPDATE FYI_MONITOR WITH (ROWLOCK)
SET FYI_SERVICE_OWNER = '1EF1D4678E3349E48940950E7C7F7247',
FYI_SERVICE_STATUS = 'Processing'
WHERE FYI_RKEY IN (
SELECT TOP 20 FYI_RKEY
FROM FYI_MONITOR WITH (ROWLOCK)
WHERE FYI_SERVICE_TYPE = 'NOTIFY' AND
UPPER(FYI_SERVICE_SUBTYP) IN ('DOA') AND
(FYI_SERVICE_OWNER IS NULL OR
FYI_SERVICE_OWNER = '') AND
(FYI_PROCESS_TIME IS NULL OR
CONVERT(DECIMAL(16,0),
FYI_PROCESS_TIME) <= 1274889601)
ORDER BY CONVERT(DECIMAL(16,0),
FYI_PROCESS_TIME))
DELETE FROM FYI_MONITOR_HIST WHERE (FYI_SERVICE_STATUS = 'COMPLETE')AND (FYI_SERVICE_DATE <= @P1)
DELETE FROM FYI_MONITOR WITH(ROWLOCK) WHERE FYI_DKEY = 219267 AND FYI_VER_MAJOR = -1 AND FYI_VER_MINOR = -1 AND FYI_SERVICE_CUSTOM = '1.5'AND NOT (FYI_SERVICE_TYPE = 'ESCALATIONWORKER' AND FYI_CUSTOM = 1337329)
SELECT * FROM FYI_MONITOR WITH (ROWLOCK) WHERE FYI_RKEY = @P1
June 1, 2010 at 9:47 am
Not much I can do about that update. Those functions in the where clause are going to kill it, not matter what indexes are there. Is the DB case-sensitive?
What's the data in the FYI_RKEY column like?
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
June 1, 2010 at 10:52 am
Nope it is SQL_Latin1_General_CP1_CI_AS
June 1, 2010 at 10:53 am
They are are an int with a current length of 6
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply