I got a report from development team that section of BizTalk communicating with SQL Server to update status
is frequently failing as deadlock victim.

As I couldn't see any useful details in errorlog, enabled trace flags 1204,1205, 3605 and -1 and captured
few deadlock cases in errorlog.

but there was no clear indication of tables involved in deadlock.
only useful hint i could see was

Message
PAGE: 17:1:40005               CleanCnt:2 Mode:U Flags: 0x3

this could be a strange message for most of the DBAs but people who knows about storage internals
knows how important hint is this.

this message indicates deadlock occured during update operation(Mode:U) on pagenumber 1:40005 in dbid 17.

it is  not a tough task to find the db name, sp_helpdb can tell the db name of dbid 17.

to identify the table involved in it, I did small reverse engineering.

just printed page content using DBCC page and got object_id (plus allocation-id and partition-id) from page header.

here is the commands used:

dbcc traceon(3604)
dbcc page(17,1,40005,3)
dbcc traceoff(3604)

header details (filterered):

PAGE HEADER:

m_pageId = (1:40005)                 m_headerVersion = 1                  m_type = 1
m_typeFlagBits = 0x4                 m_level = 0                          m_flagBits = 0x8200
m_objId (AllocUnitId.idObj) = 42     m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594040680448                                 Metadata: PartitionId = 0
Metadata: IndexId = -1               Metadata: ObjectId = 0               m_prevPage = (1:71204)
m_nextPage = (1:35985)               pminlen = 21                         m_slotCnt = 2
m_freeCnt = 7458                     m_freeData = 5007                    m_reservedCnt = 0
m_lsn = (20502:273:2)                m_xactReserved = 0                   m_xdesId = (0:10843496)
m_ghostRecCnt = 0                    m_tornBits = 423375875              

sys.objects, sys.partitions and sys.allocation_units system views are fair enough to identify the table involved in deadlock.

query like this can help to identify the procedures which handles update part of deadlock victim table

select OBJECT_NAME(object_id), definition from sys.sql_modules where definition
like '%update%<Tablename>%'

changes done in stored procedure to handle deadlock:

1. uniqueidentifier was given as clustered index which lead to huge page splits.
changed the key to Non clustered index. this bring page split under control. (Paul has written a article on this)
2. added ROWLOCK
3. added MAXDOP option in update statement.

result: I havent hear deadlock occurence for last one week.

conclusion:
there could be some easy/better way to identify deadlock and fix it.(like profiler). but with this i would like to
insist DBAs to learn internal concepts. knowledge of internal concept will surely help DBAs to address issues like this in their day to day life.

happy learning internals!!!