Press "Enter" to skip to content

Category: Internals

When the Optimizer Can Use Batch Mode on Row Store

Published 2020-02-27 by Kevin Feasel

Erik Darling looks at some internals for us:

Things like Accelerated Database Recovery, Optimize For Sequential Key, and In-Memory Tempdb Metadata are cool, but they’re server tuning. I love’em, but they’re more helpful for tuning an entire workload than a specific query.

The thing with BMOR is that it’s not just one thing. Getting Batch Mode also allows Adaptive Joins and Memory Grant Feedback to kick in.

But they’re all separate heuristics.

Read on to see the extended events around batch mode to help you determine if it’s possible for the optimizer to use it for a given query.

Comments closed

Finding the Physical Location of a Row

Published 2020-02-20 by Kevin Feasel

Max Vernon breaks out the internals toolbag:

Occasionally I’ve needed to determine the physical location of a row stored in SQL Server. The code in this post uses the undocumented feature, %%PHYSLOC%%, which returns a binary representation in hexadecimal of the location of each row returned in a SELECT statement. The system table valued function, fn_PhysLocCracker, is used to decode the binary value returned by %%PHYSLOC%% to provide the file_id, page_id, and slot_id for each row.

Read on for a demo. Unlike most demos of this sort, Max is using a partitioned table, so that’s something new.

Comments closed

2 New Undocumented Items in SQL Server 2019

Published 2020-01-27 by Kevin Feasel

Solomon Rutzky takes us through a couple of undocumented additions to SQL Server 2019:

SQL Server 2019 introduced, among other things, two new filesystem-related items:

1. a system stored procedure, sys.xp_delete_files(), and
2. an instance-level configuration option, 'allow filesystem enumeration'

Both are undocumented, so let’s see if we can figure out what they do.

Click through to see how both work.

Comments closed

TempDB Usage and WhoIsActive

Published 2019-12-30 by Kevin Feasel

Josh Darnell takes us through a weird scenario where WhoIsActive can’t catch the real culprit:

Let’s say you are informed that tempdb is getting hammered on a production SQL Server instance (in the “lots of reads and writes” sense, not the “lots of shots of tequila” sense), and it’s disrupting other workloads on the system. You may have found this out through the power of monitoring (tempdb files are growing or full), or your favorite DMV queries, or just from being really smart.

You spring into action to find the offending query, and run EXEC sp_WhoIsActive but get…nothin’:

I did not successfully guess why this might be, but Josh explains it well.

Comments closed

TDE Encryption Scan Internals

Published 2019-12-11 by Kevin Feasel

On the Microsoft Tech Community blog, goramesh shares with us how the initial encryption process works for Transparent Data Encryption:

Now, once encryption is turned ON for a database, all the existing user data on the data files should be encrypted. To do this, SQL Server starts something called a TDE Encryption Scan. It is basically a scanner, which goes through each page of each data file to ensure its encrypted. When the scanner completes its scan across all the files, that’s when we say that the database is ‘encrypted’. How the TDE Encryption scan works is crucial because of the effects it can have on the user workload. Let me explain. 

Read on for the explanation.

Comments closed

Online and Resumable Operations in SQL Server

Published 2019-12-10 by Kevin Feasel

Kendra Little summarizes which operations in SQL Server have the ability to be run online, which are resumable, and which support the WAIT_AT_LOW_PRIORITY flag:

ONLINE operations in SQL Server were simple to understand for years — we got ONLINE index rebuilds in SQL Server 2005. That was it for a while. Then, things got more complicated: we got more types of indexes. We got ONLINE options for schema changes that don’t involve indexes. We got more options for managing things like blocking, because online operations are really only mostly online — generally there’s going to be at least a short period where an exclusive lock is needed to update metadata. We now have some RESUMABLE operations coming in, too, for those big operations that are tough to handle.

Along the way, I fell behind. Because these features have steadily come out over a period of time, my brain simply didn’t register them all, or possibly I missed seeing them amid other announcements.

It’s not a comprehensive list, but it’s a good starting point for understanding the options you have available.

Comments closed

An Ode to the SQLCallstackResolver

Published 2019-12-05 by Kevin Feasel

Jonathan Kehayias really likes the SQLCallstackResolver project:

Lately, I have been doing a lot of work troubleshooting certain behaviors in SQL Server for workloads that are, to put it simply, designed horribly. As a part of this, I have found it necessary to collect callstacks with Extended Events and to materialize them using the debugger symbols for SQL Server to see where exactly certain types of issues are being encountered to better understand some of the internals of newer features in SQL Server 2017 and 2019. Years ago I blogged about how to use the package0.callstack action in Extended Events for this type of thing, and Paul also has a blog post that talks about how to download the PDB symbols for SQL Server as well as a post that also demonstrates using the package0.callstack action to determine what causes a particular wait type. Using the debugging tools to get the symbols is somewhat clunky and tedious, so when I happened on this amazingly simple method of getting symbol files I had to share it.

The SQLCallstackResolver on Github has to be one of the greatest things since sliced bread if you want to materialize callstacks from SQL Server. 

Arvind Shyamsundar deserves a lot of credit for putting it together; he did a great job with the project.

Comments closed

Eager Spooling Against Indexes

Published 2019-12-04 by Kevin Feasel

Erik Darling finds an eager spool even when there is a good index to use:

But he did write about Eager Index Spools recently, and the post ended with the following statement:

Eager index spools are often a sign that a useful permanent index is missing from the database schema.

I’d like to show you a case where you may see an Eager Index Spool even when you have the index being spooled.

Click through for Erik’s demonstration.

Comments closed

Understanding the Eager Index Spool

Published 2019-12-03 by Kevin Feasel

Paul White gives us a lesson on eager spools:

Index spools do not tell the optimizer they support output ordered by the spool’s index keys. If sorted output from the spool is required, you may see an unnecessary Sort operator. Eager index spools should often be replaced by a permanent index anyway, so this is a minor concern much of the time.

There are five optimizer rules that can generate an Eager Index Spool option (known internally as an index on-the-fly). We will look at three of these in detail to understand where eager index spools come from.

Read on for a detailed discussion of eager spools.

Comments closed

Worker Migration in SQL Server 2019

Published 2019-10-24 by Kevin Feasel

Dong Cao explains an interesting improvement to SQL Server 2019’s internals:

Worker migration (AKA “worker stealing”) allows an idle SOS scheduler to migrate a worker from the runnable queue of another scheduler on the same NUMA node and immediately resume the task of the migrated worker. This enhancement provides more balanced CPU usage and reduces the amount of time long-running tasks spend in the runnable queue.
A long-running task that is enabled for worker migration is no longer bound to a fixed scheduler. Instead, it will frequently move across schedulers within the same NUMA node which naturally results in less loaded schedulers. Together with the existing load factor mechanism, worker migration provides SQL Server with an enriched solution for balanced CPU usage.

Click through to understand where this is particularly useful and what the performance implications are.

Comments closed