Skip to Content

We have all our core dataservers running in ASE157 SP63. We just embarked on a project to upgrade to ASE16 SP02 PL03. We built a test server and did some basic testing of some of the features of importance to us and which were NOT working well in ASE157 SP63.

1. Threaded Model – Applications wandering away from the bound execution class

APP1 –> GRP1_POOL_EXECLASS

APP2 –> GRP2 POOL EXECLASS

APP3 –> GRP3 POOL EXECLASS

If the binding is as shown above then APP1 should always be running on GRP1_POOL_EXECLASS. However, we have noticed that it wanders to other execution classes if we run for a longer duration of time. This prevented us from strict ring fencing of thread pools to better manage our load. This wayward behavior forced us to go back to Process Kernel.

We tested for this problem in ASE16 SP02 PL03 and I am happy to inform that it is working in the right way. We did NOT see execlass hopping. This will go a long way for us to make use the Threaded Kernel.

2. update index stats with hashing – bugs

While in ASE157 we have realized the advantages provided by update index stats with hashing. The amount of time it takes to perform update index stats with hashing is way shorter than the traditional method. Also this does fill tempdb data segment.

However there were couple of bugs which prevented us from using this feature.

1) Statistics were corrupted under certain circumstances (especially with varchar or char data columns). Any query that runs against this table was stack tracing.

2) Sometimes the sum of the weights was greater than 100% and it was also causing some issues

We tested for these bugs and I am glad to inform that they no longer seems to be an issue. This will go a long way in reducing our maintenance windows

3. Spinlock contention – DOL Tables

In our dataservers we have 50% tables which are APL and the rest are DOL (datarows). It is all for legacy reasons. We undertook an ambitious project to convert all APL tables to DOL.

When we did our first trance of table conversion the spinlock contention (on the data cache) spiked through the roof (especially with one of our core tables. Not too big but used very frequently). We had to rollback the locking scheme for these tables. 

Then we created a reproducible SQL wherein with DOL the GRABS were way higher than the GRABS with APL. The higher the GRABS are the higher the WAITS and subsequently the higher the SPINS which directly translated into higher CPU utilization.

Here are the results from our testing. We are happy about this. We think we can knockdown few percentage points of our utilization because of this. Also we can kickstart our conversion of APL tables to DOL once again.

  

APL

DOL

ASE157

1,064

20,943

ASE16

1,096

      494

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply