While preparing material for my post on latch promotion rules, I found this very interesting Stack Exchange question by Jeremiah Peschka about SQL Server’s LRU-K algorithm and the metrics that support it. It turned out that SQL Server doesn’t expose those in a useful way, but I was really impressed by some experimental evidence provided by Martin Smith, and his excellent deductions. Martin has clearly been on the case for a while and has highlighted that LRU-K (or for that matter Time of Last Access) isn’t well documented at all.
I’m not going to look at the general case of aging out buffers today; instead I’m just confirming and extending Martin’s observations about how DBCC PAGE interacts with buffers.
The return of bUse1
Quick refresher: the lazywriter maintains an internal 16-bit “clock hand” that counts seconds and thus rolls over every eighteen hours or so. Its current value is used as a cheap and easy way to measure the progress of time in places where we don’t care about absolute time, but only need the ability to note how many seconds have passed since a logged event. Continue reading “DBCC PAGE and buffer pool disfavoring”
ARTHUR: Who lives in that castle?
WOMAN: No one lives there.
ARTHUR: Then who is your lord?
WOMAN: We don’t have a lord.
DENNIS: I told you. We’re an anarcho-syndicalist commune. We take it in turns to act as a sort of executive officer for the week.
(from Monty Python and the Holy Grail)
Yeah, whatever. I want to hear about bunnies
Picture a world consisting of forty Energizer bunnies, grouped into four teams of ten. Each team has one battery between them, and the main rule of the game is that each bunny may only use the battery for a little while before transferring it to a teammate. There is no way for a sleeping player to be woken up except by being passed the battery, and each battery strictly stays within one team. Continue reading “King Arthur, Energizer bunnies, and the search for the SQLOS scheduler”
While researching my previous post on latch promotion, I came across an odd piece of magic that made me do a double take. And sleep on it. And tear out my hair. I took it on faith that this is more likely a clever algorithm than a brain fart, but I could not stop asking myself repeatedly…
In Part 3 I explained how this beast called the buffer latch cycle-based latch promotion threshold gets calculated and broadly what it means, but I didn’t tackle the obvious question of “who does what with this information?”. This post will tie some global settings together with per-buffer tracking to unravel the mystery of when a buffer latch is deemed hot enough to deserve a promotion. What I describe applies identically to SQL Server 2014 and SQL Server 2016, and it is likely that it wouldn’t have changed much from preceding versions, although I haven’t confirmed this.
Here’s something odd. If you do an online search for “SQL Server latch promotion”, a number of top hits (e.g. this, this and this) don’t actually concern latch promotion, but rather an obscure informational message that seemed to come out of the woodwork in 2008 R2: Warning: Failure to calculate super-latch promotion threshold.”.
Spinlocks live among us. We see them on duty, in uniform, and greet them by name. When we interact, they show a badge and leave a receipt for the time they eroded from our working day. Or so we’d like to think.
When looking at the 2016 SOS_RWLock, we came across the one-bit spinlock buried within its Count member. Since it protects a very simple wait structure, someone evidently made the decision that it is cheap enough to spin aggressively until acquired, with no backoff logic. This suggests that a low degree of spinlock contention is anticipated, either because few threads are expected to try and acquire the lock simultaneously or because the amount of business to be done while holding the lock is very light and likely to finish quickly. Continue reading “The Latch Files 2: The spinlock that dares not speak its name”
Time to start chipping away at the monster subject of storage engine latches. If you’re anything like me, you were really impressed by the expositions of latches done by James Rowland-Jones (in Professional SQL Server 2008 Internals and Troubleshooting) and Bob Ward (PASS Summit “Inside Latches” session) when this information first started dribbling out. Now we have reached a point in history where latches seem to be used as a swear word. Well, for the record, I am still fascinated by them, and their internals are pretty darn marvellous.
Today I’m going to keep it comparatively focused, looking at nothing other than the Count member of the LatchBase class. Specifically, I’ll only be considering the act of acquiring an uncontended un-promoted latch, based on the SQL Server 2014 and 2016 latch implementation. Continue reading “The Latch Files: Out for the count”
Talk about serendipity. I’ve been working on a progression of blog posts that would include dealing with the SOS_RWLock in both 2014 and 2016 versions, and today is a perfect excuse to align it with the 2016-themed T-SQL Tuesday hosted by Michael J Swart.
Moving along with our bestiary of synchronisation classes, the SOS_RWLock, a reader-writer lock, feels like a logical next stop. It has been in the news recently, it has fairly simple semantics, and it is built upon primitives that we have already explored, namely spinlocks, linked lists and the EventInternal class. Its implementation is quite a leap from the simple SOS_Mutex and there is more scope for alternative implementations providing the same functionality. And, would you believe it, as called out by Bob Dorr, the 2012/2014 implementation has now been found wanting and got rewritten for 2016. Today we’re looking at the “classic” version though, because we then get the chance to understand the 2016 rewrite in terms of concrete design decisions. Continue reading “Unsung SQLOS: the classic SOS_RWLock”