I am looking at a connection to a 32-bit 220.127.116.115 database that is "stuck", and shows the following connection properties...
HeapsLocked 4294967266 HeapsRelocatable 4294967276
Since the largest unsigned integer is 4294967295, those numbers look suspiciously like "hitting a limit".
Here is a snapshot of a few related properties. Does this prove the connection-level values are bogus? Or is it still possible the connection-level values are valid, indicating some serious problem in the engine?
engine... 2010-09-25 03:45:00.092,87,'HeapsLocked','Number of relocatable heaps currently locked in cache','21' 2010-09-25 03:45:00.092,89,'HeapsRelocatable','Number of relocatable heaps','204' 2010-09-25 03:45:00.092,100,'LockedHeapPages','Heap pages locked in cache','2175' connection... 2010-09-25 03:45:00.014,81,87,'HeapsLocked','Number of relocatable heaps currently locked in cache','4294967255' 2010-09-25 03:45:00.014,81,89,'HeapsRelocatable','Number of relocatable heaps','4294967275'
In the past, server/database/connection properties were updated on a "best effort" basis in order to keep the maintenance overhead of those properties low. When we update a property, no memory locking/barriers are used in order to prevent contentions on those locations in memory and hence slow down execution. Two reasons can cause a property to show unrealistic values: concurrency issues or software bugs.
A software bug happens when we mistakenly forget to update (increment or decrement) a property at the correct point in the code.
A concurrency issue happens when one property is being updated simultaneously by two or more CPU cores. Because counters were kept up to date on "best effort" basis, it's more likely that this will happen on multi-core machines. The good news is that as of SA v11, we have eliminated this issue. Without adding any CPU overhead, counter can not show unrealistic values because of CPU concurrency issues. This is only true for Windows and Linux. Other UNIX platforms are still on "best effort" basis.
Since you are already using v11 software, the only reason this can happen is because of a software bug in which we have incremented/decremented a property in one spot in the code, but forgot to do the opposite in a different spot. I will try to track this down but it would be more helpful if you can provide me with a repro.
answered 27 Sep '10, 15:19