2770344 - High SSQLCACHE_SPIN in spinlock contention when insert into a column with numeric datatype - ASE

• Observed very high SSQLCACHE_SPIN spinlock contention as well as slow performance and high CPU.
• dbcc prsqlcache show duplicate update statements in the same hash bucket.
• Each supdate statement has different precision and scale of the numeric column along with a mixture of nulls for some columns.
• Here is a portion of dbcc prsqlcache output:
   
  Bucket# 5655, Entry Count: 4666, address 0x0x2aaec1b821c0
  Empty Bucket Ct: 7810, Maximum Entries In Bucket# 5655: 4666, Avg. Entries Per Bucket: 0.735686, Std. Dev.: 51.829462
   
  The same insert statement looks like the following are all in the same bucket:
  
  
  SQL TEXT: insert stmt_cache_test(pkey, col_01, col_02, col_03, col_04, col_05, col_06, col_07, col_08, col_09, col_10)
  values (@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10)
  
  ssql_obdcl: (@p0 INT output, @p1 NUMERIC(10, 6) output, @p2 NUMERIC(9, 2)
  output, @p3 NUMERIC(9, 1) output, @p4 NUMERIC(10, 4)
  output, @p5 NUMERIC(13, 6) output, @p6 NUMERIC(7, 3)
  output, @p7 NUMERIC(3, 1) output, @p8 NUMERIC(13, 3)
  output, @p9 NUMERIC(19, 10) output, @p10 NUMERIC(17, 8) output) 

• SAP Adaptive Server enterprise 15.7
• SAP Adaptive Server Enterprise 16

CR812823 CR818327 insert update numeric procedure cache dynamic sql DYNAMIC_PREPARE , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , Bug Filed