I am using BAM. Recently, I have seen deadlock errors in my SQL server error logs. Sample error log below:

2013-07-08 04:08:05.37 spid12s     Deadlock encountered .... Printing deadlock information
2013-07-08 04:08:05.37 spid12s     Wait-for graph
2013-07-08 04:08:05.37 spid12s    
2013-07-08 04:08:05.37 spid12s     Node:1

2013-07-08 04:08:05.37 spid12s     KEY: 11:72057594040287232 (6fd00cb4df00) CleanCnt:2 Mode:X Flags: 0x1
2013-07-08 04:08:05.37 spid12s      Grant List 2:
2013-07-08 04:08:05.37 spid12s        Owner:0x000000056D246A80 Mode: X        Flg:0x40 Ref:0 Life:02000000 SPID:176 ECID:0 XactLockInfo: 0x00000003C9C84EC0
2013-07-08 04:08:05.37 spid12s        SPID: 176 ECID: 0 Statement Type: CONDITIONAL Line #: 92
2013-07-08 04:08:05.37 spid12s        Input Buf: Language Event:  exec [dbo].[bam_ClaimStatusProcessorActivity_AddRelationship] N'b9ffb4dd-a8e6-4a8c-918d-3bb16855979c', N'MessageId', N'mp-sql-cd02i04\ss04p;BizTalkMgmtDb;Relationship3', N'8ad7aaa0-f7c2-4053-800c-c685ce6d004f', null
 exec [dbo].[bam_ClaimStatusProcessor
2013-07-08 04:08:05.37 spid12s     Requested by:
2013-07-08 04:08:05.37 spid12s       ResType:LockOwner Stype:'OR'Xdes:0x0000000565073950 Mode: X SPID:178 BatchID:0 ECID:0 TaskProxy:(0x0000000550B46538) Value:0xd291f6c0 Cost:(0/1920)
2013-07-08 04:08:05.37 spid12s    
2013-07-08 04:08:05.37 spid12s     Node:2

2013-07-08 04:08:05.37 spid12s     KEY: 11:72057594043432960 (4b3694478394) CleanCnt:2 Mode:X Flags: 0x1
2013-07-08 04:08:05.37 spid12s      Grant List 1:
2013-07-08 04:08:05.37 spid12s        Owner:0x00000000D291F900 Mode: X        Flg:0x40 Ref:0 Life:02000000 SPID:178 ECID:0 XactLockInfo: 0x0000000565073990

In most of the deadlocks, I am seen the key (72057594040287232) for the table "bam_Metadata_RealTimeAggregations" as a deadlock item. what could be causing these deadlocks in the sql database and why there are deadlocks involve table bam_Metadata_RealTimeAggregations ?

Can you share logs from before 2013-07-08 04:08:05.37?

Those have the same deadlock issue in the logs. 

Can you check if you have Host Tracking enabled across MORE THAN ONE Host/Host Instance? Typically a dedicated Tracking Host is configured and enabled.

Regards.

Yes we have two tracking hosts enabled.

Two instances of the same host? or Tracking enabled across two different hosts? Can you try having only ONE to see if that brings down contention? Is the SQL Instance running BAM showing higher memory/processor utilization? It could be because of disk contention and SQL is going into resource wait state.

Do you have a common set of activities across multiple orchestrations?

Regards.

This is definitely the SQL issue and it should be resolved from the SQL side.

Try to optimize the SQL BAM databases. There are a lot of information about it. Check the article about the BizTalk database optimization. 

I'd suggest to turn off the run-time aggregation and try it.