DBCC Tune(DES_BIND) determining the correct objects

[Former Member]

Guys,

Much has been written about the value of DES_BIND...

In regards to relatively current versions of ASE (15.7 sp(60,104,12x,13x) or 16.0sp ???)

Which MDA columns should be evaluated when picking objects for DES_BIND Tuning?

(I realized good testing should be done,too)

I have heard of three columns as candidates in monOpenObjectActivity...

Operations

UsedCount

LogicalReads

I have also heard of a column in monCachedProcedures...

PlanID

What formula should be used for picking objects for DES_BIND Tuning?

Cory Sane

[jayrijnbergen]

Not really an answer, more a comment....

In some situations dbcc tune(des_bind) might be very useful, however, it also limits things.

You cannot run operations that exclusively use an object with a bound des, e.g. reorg rebuild, create index, create trigger, most alter table operations. Also not possible to put the db in single user mode with a bound des.

Of course it's possible to unbind the des before doing anything requiring exclusive access to the object, but that requires there are no users in the db when you run dbcc tune(des_unbind)

IMHO it might be easier to create a dedicated named cache for these objects instead of using dbcc tune(des_bind)

[former_member182259]

Unfortunately, a dedicated named cache won't work - the issue is the DES/IDES/PDES structures and their reuse - there is a description of what is going on in the whitepaper on "Managing Workloads with ASE..." or even an older one by David Wein (which was the source of much of the info in that section in "Managing Workloads with ASE..."  - but briefly, the best ways of controlling it (and so far the only ways of controlling it) are via the number of open objects/open indexes, etc. - which increase the size of the DES/IDES/PDES pool.   However, that still doesn't help when the keep count on the DES hits 0 and the object descriptor is moved from the DES list to the scavengelist ....and then immediately gets used again and has to move back to the keep list.....which impacts the object manager spinlock (which has some controls).    There was a lot of work done there in ASE 16 (see Stefan Karlsson's whitepaper on spinlocks in ASE 16 and the section on Metadata Caches).....but......if not on 16, you still get a bit of spinlock contention.

Soooooo....if wanting to do a dbcc tune(desbind) - what we really want to know is which objects - and that is *anything* in sysobjects in *any* database - that is frequently used.     This includes tables, defaults, rules, triggers, stored procedures, fully prepared statements, cached SQL statements, etc.

monOpenObjectActivity will only help for tables and indexes (for IDES) - but you need to be careful.   For example, the UsedCount reflects how often an object was actually used as the access method.   The table has an IndexID of 0 - which may have a UsedCount=0 if the accesses are always through an index.    So using UsedCount is very unreliable and likely not a good choice.   The number of LogicalReads actually might work as it might reflect a level of activity - but there can be some nuances here with heavy use of covered indexes which may not hit the base table at all - but yet the DES may need to be kept.....so Operations *might* be as good of an indicator as any - I would likely take the two together (Operations + Logical Reads) to do a bit of fuzziness.....

.....but then remember, if you des_bind-ing the table, you also need to des_bind the triggers, defaults & rules bound to table columns (and encryption keys, etc.).    You also need to consider the number of stored procedures (and which ones are most often in cache - monCachedProcedures may be a good table to look for common procs to do des_bind on) - but I would first look an monProcedureCache and monStatementCache and make sure my calculations for 'number of open objects' included the number of procs/statements.   This could help minimize any reuse going on.....or procs being removed from cache to free a DES....

...remember, the kept list --> scavenge list --> kept list doesn't actually mean the DES was scavenged.......which is realllllllllllllllllllllllllyyyyyyyyy  realllllllllllllllllllllllyyyyy bad.    So even if you have a high number of DES, you can get contention - which is why I often suggest lowering the object spinlock ratios to something fairly low along with increasing the number of open objects.   Yes, moving back and forth on the list is a bit of overhead that can be avoided with des_bind(), but sometimes, most of the problems can be eased by simply allocating a LARGE number of open objects and then decreasing the open object spinlock ratio....