Re: Enqueue Wait Events - TM - SSX

Presence of SSX indicates missing index on the child table of a foreign key relationship.

Running a test case use EMP and DEPT table and turning on event 10704 tracing, I noticed the following when no index was present in the trace (grep'ing trace for TM)
ksqgtl *** TM-0000c7ca-00000000 mode=3 flags=0x401 timeout=21474836 *** ksqgtl *** TM-0000c7cc-00000000 mode=5 flags=0x401 timeout=21474836 ***

ksqcnv: TM-0000c7cc,00000000 mode=3 timeout=21474836
ksqcmi: TM,c7cc,0 mode=3 timeout=21474836
ksqcnv: TM-0000c7cc,00000000 mode=5 timeout=21474836
ksqcmi: TM,c7cc,0 mode=5 timeout=21474836
ksqcnv: TM-0000c7cc,00000000 mode=3 timeout=0

and when index was present
ksqgtl *** TM-0000c7ca-00000000 mode=3 flags=0x401 timeout=21474836 *** ksqgtl *** TM-0000c7cc-00000000 mode=2 flags=0x401 timeout=21474836 *** ksqcnv: TM-0000c7cc,00000000 mode=3 timeout=21474836

c7ca is object_id for "dept" while c7cc for "emp" table.

Regards

Joseph Received on Fri Jan 27 2006 - 10:53:16 CST