Generated by All in One SEO v4.9.10, this is an llms.txt file, used by LLMs to index the site. # SQL on ice keep it fresh. keep it fun ## Sitemaps - [XML Sitemap](https://sqlonice.com/sitemap.xml): Contains all public & indexable URLs for this website. ## Posts - [TSQL Tuesday #96 Round-up](https://sqlonice.com/tsql-tuesday-96-round-up/) - A round-up of the fantastic contributions to T-SQL Tuesday 96, on the subject people who have made a difference in your life. - [TSQL Tuesday #96: Three People Who Made a Difference](https://sqlonice.com/tsql-tuesday-96-three-people-who-made-a-difference/) - For #tsql2sday, I pay tribute to three people who helped me grow in three different sets of circumstances. - [TSQL Tuesday #96: Folks Who Have Made a Difference](https://sqlonice.com/tsql-tuesday-96-folks-who-have-made-a-difference/) - This month's T-SQL Tuesday is an opportunity to give a shout-out to people who have helped you in your IT career. - [PASS Summit 2017 - a personal angle](https://sqlonice.com/pass-summit-2017/) - Some personal thoughts on a week in Seattle. - [Fishing for wait types revisited - QUERY_TASK_ENQUEUE_MUTEX](https://sqlonice.com/fishing-for-wait-types-revisited_query_task_enqueue_mutex/) - I accept a friendly poke and go fishing for an obscure wait type, discovering that my documented fishing methodology needed some tweaking. - [Unsung SQLOS: the SOS_Mutex](https://sqlonice.com/unsung-sqlos-sos_mutex/) - A tour of the SOS_Mutex class, a direct descendant of EventInternal, and the simplest waitable SQLOS synchronisation object. - [Fishing for wait types in WinDbg](https://sqlonice.com/fishing-for-wait-types-in-windbg/) - A quick post on a dirty trick - how to find SQL Server functions that might use specific wait types using Windbg - [The DMV Diaries: sys.dm_os_workers](https://sqlonice.com/dmv-diaries-sys-dm_os_workers/) - Internals of how sys.dm_os_workers operates, with some extra background about what it does and does not reveal. - [The DMV Diaries: sys.dm_os_threads](https://sqlonice.com/dmv-diaries-sys-dm_os_threads/) - We lift the lid on how sys.dm_os_threads works, where it gets its data from, and how tricky it can be to avoid blocking in low-level code. - [TSQL Tuesday #93: The buzzword arms race](https://sqlonice.com/tsql-tuesday-93-the-buzzword-arms-race/) - A tale of an interview that was probably equally uncomfortable on both sides of the table. - [In the footsteps of a cooperative wait](https://sqlonice.com/footsteps-of-cooperative-wait/) - Having looked at thread creation and work assignment, we scratch an old itch: how does a worker enter a wait, and what does it do there? - [Where do SQL Server tasks come from?](https://sqlonice.com/where-do-sqlos-tasks-come-from/) - We see how SOS_Tasks are specified through SOS_Task::Param and passed from specifying to target threads through the WorkDispatcher. - [The early life of a SQLOS thread](https://sqlonice.com/the-early-life-of-a-sqlos-thread/) - We look into a neglected area of the SQLOS threading model: what happens between a thread's birth and the moment it starts running a task? - [Speaking at SQL Saturday Manchester](https://sqlonice.com/speaking-at-sql-saturday-manchester/) - What I'll be doing on 15 July. And what I won't be doing on 15 July - [Anatomy and psychology of a SQLOS spinlock](https://sqlonice.com/anatomy-and-psychology-of-a-sqlos_spinlock/) - A deep dive into the internals of the SQLOS spinlock implementation - [Scheduler stories: OLEDB, the external wait that isn't preemptive](https://sqlonice.com/scheduler-stories-oledb-the-external-wait-that-isnt-preemptive/) - In SQL Server, all preemptive waits are external. But not all external waits are preemptive, as we'll see for OLEDB - [Unsung SQLOS: The SOS_UnfairMutexPair behind CMEMTHREAD waits](https://sqlonice.com/unsung-sqlos-the-sos_unfairmutexpair-behind-cmemthread-waits/) - Have you ever wondered exactly what happens during a CMEMTHREAD wait? Meet the sullen but efficient SOS_UnfairMutexPair guarding the CMemObj - [SQL Agent and the hairiest Dateadd in town](https://sqlonice.com/sql-agent-and-the-hairiest-dateadd-in-town/) - A subtle change in behaviour for "next run time" calculations in SQL Agent 2016, plus some date manipulation trivia. - [SQL Server doubly linked lists revisited](https://sqlonice.com/sql-server-doubly-linked-lists-revisited/) - Another visit to linked lists, this time to pay our respects to a couple of tricky bugs that can come about when traversing them. - [Context in perspective 6: Taking Sessions to Task](https://sqlonice.com/context-in-perspective-6-taking-sessions-to-task/) - An undocumented DBCC command, a roadmap of some classes that define SQL Server session state, and another visit to thread-local storage. - [Context in perspective 5: Living next door TLS](https://sqlonice.com/context-in-perspective-5-living-next-door-tls/) - Find out how SQLOS local storage relates to SystemThreads, and how to walk a path from TLS to interesting SQL Server objects like CSession - [Context in perspective 3: Seven vftables for seven interfaces](https://sqlonice.com/context-in-perspective-3-seven-vftables-for-seven-interfaces/) - In this episode, I accidentally discover the implementation joys of multiple virtual interfaces in a SQL Server class. - [Context in perspective 4: On the street where you live](https://sqlonice.com/context-in-perspective-4-on-the-street-where-you-live/) - Context riddle: if we know nothing more than the address of a SQLOS object, how can we find its memory allocator to dispose of it when needed? - [#TSQL2SDAY: The string length server](https://sqlonice.com/tsql2sday-the-string-length-server/) - How many layers of the OSI model can you exercise to answer a simple question? Discover the joys of macroservices. - [Context in perspective 1: What the CPU sees in you](https://sqlonice.com/context-in-perspective-1-what-the-cpu-sees-in-you/) - The start of a three-part trip into mechanisms by which the CPU, Windows and SQL Server squirrel away the implicit context around your code and queries. - [Context in perspective 2: A pretty stack frame is like a melody](https://sqlonice.com/context-in-perspective-2-a-pretty-stack-frame-is-like-a-melody/) - Part 2 of a series exploring how a bunch of CPU instructions within SQL Server gains context. This one digs into x64 stack conventions. - [#TSQL2SDAY: Musings on SESSION_CONTEXT()](https://sqlonice.com/musings-on-session_context/) - For T-SQL Tuesday, I glance over Aaron Bertrand's shoulder as he sells us some SESSION_CONTEXT(). And of course I try and contextualise. - [#TSQL2SDAY: Sing a song of unsigned ints](https://sqlonice.com/tsql2sday-sing-a-song-of-unsigned-ints/) - A look at the arguments for unsigned integers in SQL Server, and a spotlight on an obscure little bug that happens to suit the discussion. - [Indirection indigestion, virtual function calls and SQLOS](https://sqlonice.com/indirection-indigestion-and-sqlos/) - We look at virtual function calls and their relationship with SQLOS, taking a detour into other similar indirections. - [Scheduler stories: The myth of the waiter list](https://sqlonice.com/scheduler-stories-the-myth-of-the-waiter-list/) - A good-natured rant about the idea that a SQLOS scheduler might care about waiter lists. And an illustration of when it actually does. - [The DMV diaries: Worker, task, request and session state](https://sqlonice.com/the-dmv-diaries-worker-task-request-and-session-state/) - Different DMVs return different statuses for a "SPID". We take the lid off some DMVs to uncover the source of the discrepancies. - [Scheduler stories: Going Preemptive](https://sqlonice.com/scheduler-stories-going-preemptive/) - Switching between cooperative and preemptive scheduling within SQLOS uncovers some surprises in terms of mechanisms and so-called preemptive waits. - [Scheduler stories: Interacting with the Windows scheduler](https://sqlonice.com/scheduler-stories-interacting-with-the-windows-scheduler/) - Building on user-mode scheduling with fibers, we look at how SQLOS leverages event objects to take control of scheduling, plus some other Windows interactions. - [Scheduler stories: The joy of fiber mode](https://sqlonice.com/scheduler-stories-the-joy-of-fiber-mode/) - We continue our look at SOS_Scheduler internals by considering fiber mode context switching and looking inside a fiber context switch. - [The Latch Files 3: Superlatch promotion threshold](https://sqlonice.com/latch-files-superlatch-promotion-threshold/) - The common route to a latch promotion uses a cycle-based threshold. Here we look at how the superlatch promotion threshold is calculated. - [Scheduler Stories: When does your scheduler run?](https://sqlonice.com/scheduler-stories-when-does-your-scheduler-run/) - We ease into a deep dive on SQLOS scheduling, starting with some orientation around the SOS_Scheduler class and its place in the world. - [A spanking new ReaderWriterSpinlock](https://sqlonice.com/spanking-new-readerwriterspinlock/) - The introduction of a reader-writer spinlock into SQL Server 2016 gives us a chance to try a new flavour of this synchronisation mechanism. - [Unsung SQLOS: the classic SOS_RWLock](https://sqlonice.com/unsung-sqlos-classic-sos_rwlock/) - The SOS_RWLock class is SQLOS's reader-writer lock implementation. We look in rather a lot of detail at what makes the pre-2016 version tick. - [The Thread in the Head: Dr Sqlos explains context switching](https://sqlonice.com/the-thread-in-the-head-dr-sqlos-explains-context-switching/) - Dr Seuss meets Bob Ward, and the results aren't pretty, but they're anatomically correct. - [DBCC PAGE and buffer pool disfavoring](https://sqlonice.com/dbcc-page-and-buffer-pool-disfavoring/) - Another visit to the BUF structure, looking at how buffer pool disfavoring is implemented and confirming some DBCC PAGE internals. - [King Arthur, Energizer bunnies, and the search for the SQLOS scheduler](https://sqlonice.com/king-arthur-energizer-bunnies-sqlos-scheduler/) - Cooperative SQLOS scheduling explained with the help of unstoppable rabbits. - [How SQL Server counts page refs without counting](https://sqlonice.com/count-without-counting/) - The breferences member of SQL Server's BUF struct keeps track of page access counts. But it does so using very clever, cheap approximation. - [The Latch Files 4: Superlatch promotion logic](https://sqlonice.com/latch-files-superlatch-promotion-logic/) - Have you ever wondered what exactly triggers the promotion of a SQL Server buffer latch to a superlatch? We look at this obscure area in gory detail. - [The Latch Files 2: The spinlock that dares not speak its name](https://sqlonice.com/latch-files-spinlock-dares-not-speak-its-name/) - SQL Server latches include a 1-bit spinlock within the Count member. We look at how its acquisition logic compares with normal spinlocks. - [The Latch Files: Out for the count](https://sqlonice.com/the-latch-files-out-for-the-count/) - We look into the storage engine latch's Count member, demonstrating how much "business logic" can be crammed into a single compare-and-swap operation. - [Unsung SQLOS: SOS_WaitableAddress](https://sqlonice.com/unsung-sqlos-sos_waitableaddress/) - This class uses a hash table of resource description "cookies" rather than eagerly allocated SQLOS objects. A step towards understanding the lock manager. - [Unsung SQLOS: the EventInternal](https://sqlonice.com/unsung-sqlos-eventinternal/) - An introduction to EventInternal, the granddaddy of SQLOS synchronisation objects, upon which much of SQLOS threading is based. - [Unsung SQLOS: the 2016 SOS_RWLock](https://sqlonice.com/usung-sqlos-2016-sos_rwlock/) - An exploration of what has changed for the SOS_RWLock between 2014 and 2016, including a walkthrough of its more evolved use of compare-and-swap . - [Unsung SQLOS: the SystemThread](https://sqlonice.com/unsung-sqlos-the-systemthread-class/) - A look into the SystemThread class, which wraps an OS thread into a SQLOS object, allowing the thread to become a first-class citizen in SQLOS scheduling. - [Windows, mirrors and a sense of self](https://sqlonice.com/windows-mirrors-and-a-sense-of-self/) - Why is thread identity a big deal? We look at the importance of the Thread Environment Block (covering thread-local storage) and what it means for SQLOS. - [One (binary sensation)](https://sqlonice.com/one-binary-sensation/) - It's not often that bit-twiddling and SQL sit well together. Different sides of the railway line and all that. However, I recently came across an interesting expression which fits the bill: Or in something closer to English: This had me scratching my head, because while it was recognisable as something involving bit patterns, I couldn't - [Milliseconds 10, ticks 3](https://sqlonice.com/milliseconds-10-ticks-3/) - Exploring how SQL Server keeps time, and how the 1/300 s resolution of the Datetime datatype and getdate() is implemented - [The road to UTC is paved with DatetimeOffset](https://sqlonice.com/the-road-to-utc-datetimeoffset/) - Here is a dirty little secret: most of us who live in single-time-zone countries are not comfortable with time zones. Hardly surprising given that we tend to treat wall clock time as a law of nature that only get adjusted twice a year. Now relating one's day-to-day life to local time is all very well, but - [Unsung SQLOS: linked lists](https://sqlonice.com/unsung-sqlos-linked-lists/) - An exploration of how SQLOS and SQL Server implement linked lists, as a precursor to seeing where and how higher-level code builds upon them. - [Threading for humans](https://sqlonice.com/threading-for-humans/) - A bit of navel-gazing about just what the heck a thread really is, as prequel to talking about SQLOS implementation ## Pages - [About SQL on ice](https://sqlonice.com/about/) - Ewald Cress's personal blog, mostly focusing on SQL Server internals. ## Categories - [Uncategorized](https://sqlonice.com/category/uncategorized/) - [Fun with numbers](https://sqlonice.com/category/fun-with-numbers/) - [Date and time](https://sqlonice.com/category/date-and-time/) - [SQLOS](https://sqlonice.com/category/sqlos/) - [Unsung SQLOS](https://sqlonice.com/category/unsung-sqlos/) - [General OS stuff](https://sqlonice.com/category/general-os-stuff/) - [Synchronisation](https://sqlonice.com/category/synchronisation/) - [Latches](https://sqlonice.com/category/latches/) - [Lock-free operations](https://sqlonice.com/category/lock-free-operations/) - [Buffer pool](https://sqlonice.com/category/buffer-pool/) - [Scheduling](https://sqlonice.com/category/scheduling/) - [DMVs](https://sqlonice.com/category/dmvs/) - [Waits](https://sqlonice.com/category/waits/) - [Context](https://sqlonice.com/category/context/) - [Memory](https://sqlonice.com/category/memory/) - [Silliness](https://sqlonice.com/category/silliness/) - [Data structures](https://sqlonice.com/category/data-structures/) - [SQL Agent](https://sqlonice.com/category/sql-agent/) - [Professional development](https://sqlonice.com/category/professional-development/) - [Community](https://sqlonice.com/category/community/) ## Tags - [datetime](https://sqlonice.com/tag/datetime/) - [ticks](https://sqlonice.com/tag/ticks/) - [SQL Server internals](https://sqlonice.com/tag/sql-server-internals/) - [bit manipulation](https://sqlonice.com/tag/bit-manipulation/) - [utgettime](https://sqlonice.com/tag/utgettime/) - [DatetimeOffset](https://sqlonice.com/tag/datetimeoffset/) - [UTC](https://sqlonice.com/tag/utc/) - [time zones](https://sqlonice.com/tag/time-zones/) - [data types](https://sqlonice.com/tag/data-types/) - [spinlocks](https://sqlonice.com/tag/spinlocks/) - [synchronization](https://sqlonice.com/tag/synchronization/) - [SpinToAcquireWithExponentialBackoff](https://sqlonice.com/tag/spintoacquirewithexponentialbackoff/) - [linked lists](https://sqlonice.com/tag/linked-lists/) - [SQLOS](https://sqlonice.com/tag/sqlos/) - [Threading](https://sqlonice.com/tag/threading/) - [Windows internals](https://sqlonice.com/tag/windows-internals/) - [TLS](https://sqlonice.com/tag/tls/) - [Thread-local storage](https://sqlonice.com/tag/thread-local-storage/) - [TEB](https://sqlonice.com/tag/teb/) - [Thread Environment Block](https://sqlonice.com/tag/thread-environment-block/) - [SystemThread](https://sqlonice.com/tag/systemthread/) - [Scheduling](https://sqlonice.com/tag/scheduling/) - [SQL Server](https://sqlonice.com/tag/sql-server/) - [EventInternal](https://sqlonice.com/tag/eventinternal/) - [Synchronisation](https://sqlonice.com/tag/synchronisation/) - [SOS_RWLock](https://sqlonice.com/tag/sos_rwlock/) - [hash tables](https://sqlonice.com/tag/hash-tables/) - [SOS_WaitableAddress](https://sqlonice.com/tag/sos_waitableaddress/) - [Latches](https://sqlonice.com/tag/latches/) - [LatchBase](https://sqlonice.com/tag/latchbase/) - [Count](https://sqlonice.com/tag/count/) - [compare and swap](https://sqlonice.com/tag/compare-and-swap/) - [cmpxchg](https://sqlonice.com/tag/cmpxchg/) - [Latch promotion](https://sqlonice.com/tag/latch-promotion/) - [BUF](https://sqlonice.com/tag/buf/) - [BPool](https://sqlonice.com/tag/bpool/) - [silliness](https://sqlonice.com/tag/silliness/) - [DBCC](https://sqlonice.com/tag/dbcc/) - [SOS_Scheduler](https://sqlonice.com/tag/sos_scheduler/) - [fibers](https://sqlonice.com/tag/fibers/) - [Worker](https://sqlonice.com/tag/worker/) - [DMVs](https://sqlonice.com/tag/dmvs/) - [Windbg](https://sqlonice.com/tag/windbg/) - [Wait types](https://sqlonice.com/tag/wait-types/) - [Abstraction](https://sqlonice.com/tag/abstraction/) - [Context](https://sqlonice.com/tag/context/) - [Stack](https://sqlonice.com/tag/stack/) - [x64](https://sqlonice.com/tag/x64/) - [C++](https://sqlonice.com/tag/c/) - [memory objects](https://sqlonice.com/tag/memory-objects/) - [pmo](https://sqlonice.com/tag/pmo/) - [T-SQL Tuesday](https://sqlonice.com/tag/t-sql-tuesday/) - [bugs](https://sqlonice.com/tag/bugs/) - [SQL Agent](https://sqlonice.com/tag/sql-agent/) - [date and time](https://sqlonice.com/tag/date-and-time/) - [Waits](https://sqlonice.com/tag/waits/) - [SQL Saturday](https://sqlonice.com/tag/sql-saturday/) - [SOS_Node](https://sqlonice.com/tag/sos_node/) - [WorkDispatcher](https://sqlonice.com/tag/workdispatcher/) - [SystemThreadDispatcher](https://sqlonice.com/tag/systemthreaddispatcher/) - [SOS_Task](https://sqlonice.com/tag/sos_task/) - [Runnable queue](https://sqlonice.com/tag/runnable-queue/) - [Professional development](https://sqlonice.com/tag/professional-development/) - [PASS Summit](https://sqlonice.com/tag/pass-summit/) - [Community](https://sqlonice.com/tag/community/) - [#tsql2sday](https://sqlonice.com/tag/tsql2sday/)