Sybase 15.0 - Spinlock Contention in Procedue Cache.

[Former Member]

I want to share my experiences dealing with this topic.

According to my research, the problem occurs due to several factors, namely:

- Having 15 Optimizer enabled (not in compatibility mode on in the SP). The Sybase opimizador 15 is much more efficient than that of Sybase 12; solving
all algorithms "hash" of execution plans in the procedure cache (temporary tables that weapon to solve these plans "supports" in the procedure cache).
  Whereupon every execution thereof contributes even more to "break up" the procedure cache.
- Have the option compiliacion defered procedure enabled the engine.
- Have a high turnout of Stored Procedures, which have a huge number of rows with a lot of input parameters, which varies according to their
functional performance, causing them to solve different situations according to the values ​​of these parameters. These together with the compilation defered makes SP of the characteristics cited work more efficiently, arming them plans to "measure" of each of the options you have, arming them at runtime. As
counterpart, this practice means that in times of great amount of mass executions and concurrency of these SP, the "procedure cache" is this continuous loading  plans, producing a high fragmentation of the cache, leading to the processors wander by the "spin" or "turns", instead of finding and implementing
  plans.
- A lot of processors (engines), this problem increases.

All these factors together can lead to the processors spend more time doing the spin that solving the SP plans as a result is
a system processor is almost 100% and slowly solving tasks.

The visualization of the percentage of spinlock can be achieved through the commands:
dbcc monitor ("clear", "spinlock_s", "on")
go
waitfor delay "00:02:00" / * The time you want to check * /
go
dbcc monitor ("sample", "spinlock_s", "off")
go
dbcc monitor ("select", "spinlock_s", "on")
go
dbcc traceon (8399)
go
dbcc traceoff (-1)
go
dbcc traceon (8399)
go
And then at the table sysmonitors

If I get problem in procedure cache (more than 10% in wait_percent), and also the total is significant vs sping. the total_spins of different partitions
default data cache, and shows processors sysmon sustained use over 90%, it should clear the cache with the following procedure:
dbcc traceon (3604)
go
dbcc proc_cache (free_unused)
go
dbcc traceoff (3604)
go
-> This procedure cleans the cache of all plans except those that are in use when ejecuctarlo.

To verify that spin and the problem was solved with the above:
- Comparing sysmon the problem and normal sysmon about Disk I / O Checks - Task Context Switches Due To -

Committed Xacts - Total Rows Affected - Total Lock Requests and Total Cache Searches; and, if these values ​​at times that are 100% engines are not higher than lulls is very likely that the engines spinlock are doing.
- Should the engines down immediately to 10% or 20% occupancy, and stabilize after five / ten minutes (to continue the transactional load)
values ​​in normal operation.

Another solution that can be applied to reduce the fragmentation, is to lift the motor with Trace Flag 753, which tells the engine to find BD
the procedure cache fills not take long to re-record storage blocks in it, but to take "a product", thus decreasing the generation of fragmentation,
  (This was the behavior as I have understood that I had in the ASE version 12).

I hope it will be useful.

Regards,

Flavio.