Press "Enter" to skip to content

Category: Internals

Performance-Killing Pre-Emptive Waits

Published 2022-11-28 by Kevin Feasel

Sean Gallardy finds the real killer:

If you haven’t already read up on cooperative and preemptive scheduling or aren’t sure what those are, please read the intro to that first, otherwise you’ll be lost.

Much as I’ve discussed before, SQL Server uses a cooperative scheduling model. This doesn’t mean that Windows does, nor does it mean Windows will scheduler whatever SQL Server schedules, in fact much of the time there are many other threads that run before the ones from SQL Server, that’s the job of the operating system to figure out. Due to SQL Server using cooperative scheduling there needs to be a mechanism that exists such that when a resource not under SQL Server’s control needs interaction the scheduler can keep on scheduling and threads can switch in and out (in SQL Server, Windows does what Windows wants). Enter preemptive status and associated waits.

Click through for a deep dive on the topic.

Comments closed

Query Cost Normalizing

Published 2022-11-17 by Kevin Feasel

Tibor Karaszi does the math:

There are plenty of articles out there recommending you to up the “cost threshold for parallelism” configuration option. Most of you already know this, if not fire up your favorite search engine and spend a few minutes reading about it.

My aim here is to do a totally non-scientific test if I can translate these fictitious seconds to what they correspond to on a reasonably modern hardware – which is my laptop from 2020.

This is one interesting approach to back into a cost threshold for parallelism.

Comments closed

Find When a Table was Dropped

Published 2022-10-26 by Kevin Feasel

Andrea Allred does some sleuthing:

Say you have a user come to you and they dropped a table sometime yesterday, but they don’t remember when and now they need it back. You could start the restore process and roll through logs until you see the drop and then restore to the hour before or you could run this super cool query to get the time the table was dropped.

Click through to see when and even which user did it—assuming you don’t have everybody going through a connection pooled account.

Comments closed

Lock Escalation outside of Repeatable Read

Published 2022-10-21 by Kevin Feasel

Paul White continues a series on lock escalation:

When SQL Server reads data under locking read committed isolation (the default for non-cloud offerings) and chooses row-level locking granularity, it normally only locks one row at a time. The engine processes the row through parent operators before releasing the shared lock prior to locking and reading the next row.

When the engine chooses page-level locking granularity, it takes and holds a shared page lock while processing rows on the current page and then releases the lock right before acquiring a lock on the next page.

In general, SQL Server is careful to take and hold locks only when needed and only long enough to guarantee correct results, taking account of the current isolation level. When locking at row granularity, the engine can skip row locks entirely when it’s safe to do.

Read on to understand when SQL Server either gets it wrong or when exigent factors alter the story.

Comments closed

Lock Escalation Thresholds and Parallelism

Published 2022-10-06 by Kevin Feasel

Paul White complicates matters:

In part one of this series, I explained how the lock manager maintains a count of held locks per transaction, and individual access methods keep a separate lock count per HoBt for each plan operator performing data access.

Parallelism adds an extra layer of complexity because both lock counts are maintained per thread.

There are two underlying reasons for this:

Paul explains the reasons and shows off plenty of tests along the way.

Comments closed

Finding the Resource Database

Published 2022-10-05 by Kevin Feasel

Chad Callihan confirms that you can see the resource database but only in your peripheral vision:

Can you name each of the system databases in SQL Server?

You might be able to name master, msdb, model, and tempdb. What about the fifth system database? Don’t feel bad if you’re drawing a blank.

The fifth system database is the Resource database. Even if you’ve never heard of the Resource database, chances are that you’ve used it. Let’s discuss how that can be.

Click through to learn what the resource DB is and why you typically won’t see it.

Comments closed

SQL Server and the Slow Registry

Published 2022-09-15 by Kevin Feasel

Michael J. Swart diagnoses issues when the Windows registry slows down operations:

I want to describe some symptoms that SQL Server may display when its Windows Registry is non-responsive or slow. From the symptoms, it’s hard to know that it’s a slow registry and so if a web search brought you here, hopefully this helps.

Read on for some of the various operations which request data from the registry, as well as thoughts from Michael on some of the effects of a slow registry. It sounds like there’s not a whole lot we can do about it and this is rare.

1 Comment

Lock Escalation Thresholds

Published 2022-09-08 by Kevin Feasel

Paul White gets into the weeds:

This article isn’t about the act of lock escalation itself, which is already well documented and generally well understood. Some myths (like row locks escalating to page locks) persist, but challenging those yet again probably wouldn’t change much.

Instead, the question I’ll address here is exactly how and when lock escalation is triggered. Much of the documentation is incorrect or at least imprecise about this and I’ve been unable to find a correct description in other writings.

There are good reasons you haven’t seen a simple demo of lock escalation taking place at 5000 locks. I’ve seen suggestions such as lock escalation isn’t deterministic, or some types of locks don’t count toward the 5000 lock threshold. Neither of those assertions is true, but the details are interesting, as I’ll explain.

As always, Paul brings clarity to a difficult topic.

Comments closed

Understanding Write-Ahead Logging

Published 2022-08-30 by Kevin Feasel

Kevin Sookocheff explains how write-ahead logging protects data in databases:

A central tenet of databases is that any committed data survives a crash or a failure. Write-ahead logging is a fundamental primitive that ensures all changes to data are first written safely to stable storage before being applied. Coupling that with some careful use of sequence numbers and we can guarantee that changes made to a database can survive system crashes.

This is a core feature in pretty much every relational database and Kevin dives into how one of the key algorithms behind it works.

Comments closed

NESTING_TRANSACTION_FULL Latches

Published 2022-08-30 by Kevin Feasel

Paul White dives into latch contention:

This design has its roots in SQL Server 7, where read-only query parallelism was introduced. SQL Server 2000 built on this with parallel index builds, which for the first time allowed multiple threads to cooperate to change a persistent database structure. Many improvements have followed since then, but the fundamental parent-child transaction design remains today.

Though lightweight, a latch can become a point of contention when requested sufficiently frequently in incompatible modes by many different threads. Some contention on shared resources is to be expected; it becomes a problem when latch waits start to affect CPU utilisation and throughput.

Read the whole thing, as Paul dives into the latch design, provides an alternative design, and tests the alternative.

Comments closed