SAP Knowledge Base Article - Preview

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

Symptom

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                        


Read more...

Environment

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

Product

Sybase Adaptive Server Enterprise 15.5

Keywords

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

About this page

This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP ONE Support launchpad (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.