2172599 - Create temp tables cause high contention on sysobects of tempdb.

When high concurrency, tempdb system table contention occurred. Many spids are executing 'create #temp table' . One spid holds Ex_row-blk on sysobjects and the other spids have been blocked.

The following output is like sp_who, spid 93,129,134,140,142,179,191 are executing 'create #temp table' on tempdb and all of them have been blocked by spid 673.

SPID   Login        Full Name                      Database         Hostname             Blocked By StartTime Duration Blocked (hh:mm:ss) Command                       
 ------ ------------ ------------------------------ ---------------- -------------------- ---------- --------- --------------------------- ------------------------------
     93 xxx         xxx                             tempdb           NULL                        673 16:03:34  00:01:27                    CREATE TABLE                  
    129 xxx         xxx                             tempdb           XXXXXXXXX      673 16:01:56  00:03:05                    CREATE TABLE                  
    134 xxx         xxx                             tempdb           NULL                        673 16:03:54  00:01:07                    CREATE TABLE                  
    140 xxx         xxx                             tempdb           NULL                        673 16:01:13  00:03:48                    CREATE TABLE                  
    142 xxx         xxx                             tempdb           NULL                        673 16:03:41  00:01:20                    CREATE TABLE                  
    179 xxx         xxx                             tempdb           NULL                        673 16:01:37  00:03:25                    CREATE TABLE                  
    191 xxx         xxx                             tempdb           XXXXXXXXX       673 16:04:50  00:00:12                    CREATE TABLE                   
    
The following output is the lock infomation about Spid 673, like sp_lock. Spid 673 is holding Ex_row_blk on sysobjects

 Database                       Table                          Lock Type            
 ------------------------------ ------------------------------ -------------------- 
 tempdb                         syscolumns                     Ex_row                           
 tempdb                         sysindexes                     Ex_row                           
 tempdb                         sysobjects                      Ex_row-blk                           
 tempdb                         systabstats                     Ex_row                           
 tempdb                         sysattributes                   Ex_intent                        
 tempdb                         syscolumns                     Ex_intent                        
 tempdb                         sysindexes                      Ex_intent                        
 tempdb                         syskeys                          Ex_intent                        
 tempdb                         sysobjects                       Ex_intent                        

• Adaptive Server Enterprise (ASE) 15.5
• Adaptive Server Enterprise For Business Suite

tempdb, contention, sysobjects , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , BC-DB-SYB , Business Suite on Adaptive Server Enterprise , Problem