Internals – Curated SQL

Performance Tuning a Dedicated SQL Pool

Published 2023-03-17 by Kevin Feasel

Sarath Sasidharan has some guidance for us:

Synapse Dedicated pools have been battle tested at enterprise customers across the globe. We deal with data in the magnitude of PetaBytes. Synapse can provide you with the scale of the cloud and the high performance required for your enterprise-grade requirements. The key to maximizing your performance is to follow best practices, check out best practices for dedicated SQL pools in Azure Synapse Analytics
Failure to do so causes performance issues. In such scenarios, is it important to understand where the bottlenecks are. This blog focuses on the different steps a query goes through; from the time the query is fired from the client until it returns back. Delay caused in any of the steps would impact the overall run-time of the query and hence indicate degraded performance.

Click through for a walkthrough of each step along the way, potential problems you could run into, and remediations for those problems. Much of the advice is similar to what you’d get with SQL Server, though there are differences interspersed throughout each level.

Disabling Inline Database Log Flushes

Published 2022-12-13 by Kevin Feasel

Bob Dorr talks about a trace flag:

SQL Server optimizes database log file flush requests, performing these flush requests inline on the active worker. Certain patterns of log record activity may encounter increased spinlock contention while performing the log cache block flush activities.
Trace flag -T8904 (startup only trace flag) disables inline log flush, limiting the contention possibility from many workers to the subset of background LogWriter workers. When the trace flag is enabled, the worker adding log records mark the log cache block to be flushed and a background LogWriter worker performs the flush activity.

Read on to learn more, including a call-out to Curated SQL favorite Lonny Niederstadt.

Comments closed

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

Category: Internals