January 3, 2017 at 5:11 am
Hi All,
My first ever real post I think!
After a late change in career I find myself responsible for all my companies in house and client facing DB systems.
A mix of 2005 through to 2014 at present.
This question is on one of our 2008 R2 instances and you will have to excuse my newbie question / explanation.
I am encountering deadlocks on a legacy system and this is an area where I have spent some time, I have enabled trace flag 1204 to output the data.
I have located the page data on one of the nodes and identified the table.
This table has a column of EventStatus_GUID as the PK and set to uniqueidentifier
It then has a Clustered Index PK_EventStatus on EventStatus_GUID
Then there is a Unique, Non Clustered Index PK_EventStatus_1 as a Constraint on EventStatus_GUID
I have an idea this is bad practice but I'd like some entry level explanation of why please.
Happy New Year to you all also.
Thanks
Stephen
January 3, 2017 at 5:34 am
A uniqueidentifier isn't ideal for a clustered index (search term: fragmentation), but it's not all that likely that it's the root cause of the deadlocks.
Stop using the 1204 traceflag, that's legacy, SQL 2000 and before. The deadlock traceflag for 2005 and later is 1222, it provides a lot more detail than 1204 did.
If you want help with the deadlocks, post the deadlock graph here.
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
January 3, 2017 at 5:50 am
Have followed your advice on the trace flag, as mentioned am new to this and working my way through as much as I can.
Shall post up the deadlock details when gathered.
Thanks for the advice
January 3, 2017 at 6:46 am
stephen.plant2 (1/3/2017)
...This table has a column of EventStatus_GUID as the PK and set to uniqueidentifierIt then has a Clustered Index PK_EventStatus on EventStatus_GUID
Then there is a Unique, Non Clustered Index PK_EventStatus_1 as a Constraint on EventStatus_GUID
...
So the index PK_EventStatus_1 has the same leading column, EventStatus_GUID as the PK_EventStatus clustered index? That seems wasteful to me. Have you analyzed the usage of the indexes on this table? Maybe something like:
SELECT s.name + N'.' + t.name AS table_name, i.name AS index_name, i.type_desc, i.is_unique,
SubString(
(SELECT N', ' + c.name + CASE WHEN ic.is_descending_key = 1 THEN N' DESC' ELSE N'' END
FROM sys.index_columns ic INNER JOIN sys.columns c ON ic.object_id = c.object_id and ic.column_id = c.column_id
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 0
ORDER BY ic.key_ordinal FOR XML PATH('')), 3, 1000) AS columns,
SubString(
(SELECT N', ' + c.name
FROM sys.index_columns ic INNER JOIN sys.columns c ON ic.object_id = c.object_id and ic.column_id = c.column_id
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.is_included_column = 1
ORDER BY ic.index_column_id FOR XML PATH('')), 3, 1000) AS included,
i.filter_definition, ps.size_MB, d.name AS FileGroup, iu.*
FROM sys.indexes i
INNER JOIN sys.tables t ON i.object_id = t.object_id
INNER JOIN sys.schemas s on t.schema_id = s.schema_id
INNER JOIN sys.data_spaces d on i.data_space_id = d.data_space_id
LEFT OUTER JOIN sys.dm_db_index_usage_stats iu ON i.object_id = iu.object_id AND i.index_id = iu.index_id AND iu.database_id = DB_ID()
LEFT OUTER JOIN (SELECT object_id, index_id, SUM(used_page_count) / 128 AS size_MB FROM sys.dm_db_partition_stats GROUP BY object_id, index_id) ps ON i.object_id = ps.object_id AND i.index_id = ps.index_id
WHERE i.name IS NOT NULL
AND i.object_id > 100
AND t.name = 'EventStatus'
ORDER BY s.name, t.name, i.name
Check the user_seeks, user_scans, user_lookups, user_updates. If that index PK_EventStatus_1 has more updates than seeks and scans, then it would seem that it's causing more problems than solving.
January 4, 2017 at 6:40 am
Here are the contents of one of the deadlock entries -
2017-01-04 08:30:20.27 spid21s deadlock-list
2017-01-04 08:30:20.27 spid21s deadlock victim=process80393b88
2017-01-04 08:30:20.27 spid21s process-list
2017-01-04 08:30:20.27 spid21s process id=process80393b88 taskpriority=0 logused=1848 waitresource=PAGE: 6:1:30983 waittime=8989 ownerId=13213192011 transactionname=EventTransaction lasttranstarted=2017-01-04T08:30:11.280
XDES=0x2813a8930 lockMode=IX schedulerid=4 kpid=7656 status=suspended spid=101 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2017-01-04T08:30:11.283 lastbatchcompleted=2017-01-04T08:30:11.283
clientapp=.Net SqlClient Data Provider hostname=CM02 hostpid=3952 loginname=Comms2 isolationlevel=read committed (2) xactid=13213192011 currentdb=5 lockTimeout=4294967295 clientoption1=537002016 clientoption2=128056
2017-01-04 08:30:20.27 spid21s executionStack
2017-01-04 08:30:20.27 spid21s frame procname=CCS_LCM.dbo.EventStatusInsTrg line=2 stmtstart=380 sqlhandle=0x030005009a97d87da2b99000bba200000000000000000000
2017-01-04 08:30:20.27 spid21s INSERT INTO CCS_LCM_AUDIT.dbo.EventStatus (MuId, SequenceNumber, Time, EventCount, AuditOperation, AuditUser, AuditTime) SELECT inserted.MuId, inserted.SequenceNumber, inserted.Time, inserted.EventCount, 0, dbo.Current_User_Context_Info(), getutcdate() FROM inserted
2017-01-04 08:30:20.27 spid21s frame procname=adhoc line=1 stmtstart=78 sqlhandle=0x0200000005b19d246ef539845dbb18b687ab9460f0bdb21a
2017-01-04 08:30:20.27 spid21s INSERT INTO [EventStatus]([MuId],[SequenceNumber],[Time],[EventCount]) values(@1,@2,@3,@4)
2017-01-04 08:30:20.27 spid21s frame procname=adhoc line=1 sqlhandle=0x020000009076a3150ba730c8d69b47a4cef773308e7bee05
2017-01-04 08:30:20.27 spid21s INSERT EventStatus (MuId, SequenceNumber, Time, EventCount) VALUES (64966,3802,'01/04/2017 08:30:07',1)
2017-01-04 08:30:20.27 spid21s inputbuf
2017-01-04 08:30:20.27 spid21s INSERT EventStatus (MuId, SequenceNumber, Time, EventCount) VALUES (64966,3802,'01/04/2017 08:30:07',1)
2017-01-04 08:30:20.27 spid21s process id=processeabe42c8 taskpriority=0 logused=14976976 waitresource=PAGE: 6:1:9758 waittime=75 ownerId=13213187641 transactionname=DELETE lasttranstarted=2017-01-04T08:30:05.247
XDES=0x81ba7970 lockMode=U schedulerid=3 kpid=4868 status=suspended spid=74 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2017-01-04T08:30:00.680 lastbatchcompleted=2017-01-04T08:30:00.680
clientapp=SQLAgent - TSQL JobStep (Job 0x168C41E86D6D5A43AB5B5F94394986DC : Step 1) hostname=SQL01 hostpid=1704 loginname=SQLAgent isolationlevel=read committed (2) xactid=13213187641 currentdb=7 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128024
2017-01-04 08:30:20.27 spid21s executionStack
2017-01-04 08:30:20.27 spid21s frame procname=LCM_ARCHIVE_LOCAL.dbo.CopyToArchive line=83 stmtstart=8500 stmtend=8702 sqlhandle=0x0300070093900e7d57b66401bfa500000100000000000000
2017-01-04 08:30:20.27 spid21s DELETE FROM CCS_LCM_AUDIT.dbo.EventStatus where EventStatus_GUID in (select GUID from @HoldingGUID)
2017-01-04 08:30:20.27 spid21s frame procname=adhoc line=1 sqlhandle=0x01000700a8259129307e82d4000000000000000000000000
2017-01-04 08:30:20.27 spid21s exec copytoarchive
2017-01-04 08:30:20.27 spid21s inputbuf
2017-01-04 08:30:20.27 spid21s exec copytoarchive
2017-01-04 08:30:20.27 spid21s resource-list
2017-01-04 08:30:20.27 spid21s pagelock fileid=1 pageid=30983 dbid=6 objectname=CCS_LCM_AUDIT.dbo.EventStatus id=lock2934b6d00 mode=UIX associatedObjectId=72057595407368192
2017-01-04 08:30:20.27 spid21s owner-list
2017-01-04 08:30:20.27 spid21s owner id=processeabe42c8 mode=UIX
2017-01-04 08:30:20.27 spid21s waiter-list
2017-01-04 08:30:20.27 spid21s waiter id=process80393b88 mode=IX requestType=wait
2017-01-04 08:30:20.27 spid21s pagelock fileid=1 pageid=9758 dbid=6 objectname=CCS_LCM_AUDIT.dbo.EventStatus id=lock11c8b2780 mode=IX associatedObjectId=72057595407368192
2017-01-04 08:30:20.27 spid21s owner-list
2017-01-04 08:30:20.27 spid21s owner id=process80393b88 mode=IX
2017-01-04 08:30:20.27 spid21s waiter-list
2017-01-04 08:30:20.27 spid21s waiter id=processeabe42c8 mode=U requestType=wait
I shall get details on the indexes momentarily.
Thanks for the advice.
January 4, 2017 at 6:48 am
The definitions of EventStatusInsTrg and copytoarchive please?
Also consider seeing whether management would be willing to get a consultant in to help you out (several of the regulars here do that kind of work), as it may well be more complicated than what is solvable here.
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 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply