keep it fresh. keep it fun
Oh, the things you can schedule,
the games you can play!
If you keep a clear head, you’ll
be well on your way.
You start with a blank sheet,
three nuts and a bolt,
a strong sense of fairness,
a large can of Jolt.
Continue reading “The Thread in the Head: Dr Sqlos explains context switching”
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.
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.
ARTHUR: What?
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)
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…
The piece of code in question is really simple, involving the maintenance of the BUF structure’s breferences member, which notionally is a count of how many times the page has been touched since the page’s counters were last reset.
Continue reading “How SQL Server counts page refs without counting”
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.
Continue reading “The Latch Files 4: Superlatch promotion logic”