Press "Enter" to skip to content

Category: Internals

Reviewing a SQL Server Backup File

Published 2022-05-16 by Kevin Feasel

Andy Yun digs into a SQL Server backup file:

This first came up during my onboarding with Pure Storage. Anthony Nocentino (b|t) taught me that a SQL Server backup file is a byte-for-byte copy of your data, as materialized in SQL Server MDF files (assuming no backup compression or backup encryption). And that would make sense – how else would SQL Server store a copy of your data in a backup file? It does not make sense for SQL Server to alter your data when it writes it down to a backup file (again, with NO backup compression/encryption) – that’s a waste of compute and effort.

Well, I had a conversation with someone who was unclear about that assertion. I tried some Google-fu to present some supporting materials, but could not actually find any documentation, official or otherwise, to back it up. So here we are.

Click through to dive into a backup file with Andy.

Comments closed

Spools, Plus Memory Grants

Published 2022-03-09 by Kevin Feasel

Erik Darling continues looking at plan operators. Erik starts with spools:

Spools are temporary structures that get stuck over in tempdb. They’re a bit like temp tables, though they don’t have any of the optimizations and enhancements. For example, loading data into a spool is a row-by-row operation.

The structure that spools use varies a bit. Table spools use a “clustered index”, but it’s not built on any of the columns in your data. Index spools use the same thing, but it’s defined on columns in your data that the optimizer thinks would make some facet of the query faster.

Definitely a must-read and a good way of explaining things. In my words, spools aren’t necessarily a problem but if you have a problem, spools are often at the root.

Erik Darling is also Overdrawn at the Memory Bank:

Whoever called memory a “bank” was a smart cookie. Everything you get from RAM is a loan.

In SQL Server, queries can get memory loaned to them while they execute. The most common reasons for memory grants are Sorts and Hashes. You may also see them for an Optimized Nested Loops Join, but whatever.

Memory is such an important aspect of query and overall server performance that it really helps to understand when there’s pressure on it, and where it’s coming from.

Check out both.

Comments closed

Watching and (Not) Messing with Optimization Phases

Published 2022-03-09 by Kevin Feasel

David Alcock giveth:

The full optimisation stage is where the optimiser uses a bag of tricks to optimise our query (surprise, surpise), well technically it has three bags of tricks that are named optimisation phases that each contain a collection of transformation rules (which I cover in this post that you should never do). The optimiser is not limited to using just one of the phases and each has a set criteria which determines if the optimiser can use that particular phase.

In order to see what how the optimiser is using these phases we need to enable Trace Flag 8675 as well as Trace Flag 3604 which will redirect the output to the query messages tab in Management Studio:

And David Alcock taketh away:

Now it has to be said it’s undocumented for a reason, the reason is that it’s really not a good idea to do this. In fact enabling this trace flag is such a bad idea that it will probably cause no end of issues with query performance…so let’s do it, but before we do let me add yet again that please don’t do this! Disabling optimisation features is a really bad idea, just like we did in this post – the purpose for this demo is just to show that we can, and how dangerous it can get.

This is fun to learn and interesting when doing advanced troubleshooting, but maybe not something you want to do very often.

Comments closed

Processing Data: Aggregate and Join Operators

Published 2022-03-07 by Kevin Feasel

Erik Darling continues a series on understanding plains. First up is aggregate operators:

Aggregates can be useful for all sorts of things in a query plan, and can show up in many different forms.

It would be tough to cover all of them in a single post, but what I’d like to do is help all you nice folks out there understand some of their finer points.

Then we have join operators:

Anyone who tells you there are only three types of joins in SQL Server isn’t your friend.

Okay, maybe that’s harsh. Maybe they’re just getting you prepared for the bonne promenade through all the many different faces a join can wear in your query plans.

Maybe they have a great explanation for Grace Hash Joins in their back pocket that they’re waiting to throw in your face like a bunch of glitter.

Do read both.

Comments closed

Tempdb Improvements over Time

Published 2022-03-04 by Kevin Feasel

Melody Zacharias takes us through the history of tempdb improvements:

In my previous blog and this one, there is a lot of talk of trace flags and you can see the life cycle of them.  They are implemented and then sometimes become part of the product over time. For a long time, the trace flag 1118 was a common performance improvement trick known only by industry experts. Over the years as a consultant, I have often been asked by clients if they should use trace flags, and generally speaking, as long as they are documented by Microsoft they are safe to use.  I would certainly not recommend using undocumented trace flags.  They are not supported and therefore not recommended.  So always be sure to check the trace flag list before setting trace flags.  So of course, as I say that, I have to offer another one.  

Read on for more details and also advice on getting the most out of tempdb.

Comments closed

Capturing a Call Stack without a Dump

Published 2022-03-04 by Kevin Feasel

Bob Dorr shows off Arvind’s SQL Call Stack Resolver:

Some outputs, such as the XEvent call stack action output the raw stack frame information and require a rebase to loaded module information in order to symbolize.   The security feature for random address virtualization loads images at different addresses each time the image is loaded.   This requires the module base address and the raw address in order to calculate the relative virtual address for symbolization.

Click through for more information.

Comments closed

Reader/Writer Synchronization in SQL Server

Published 2022-02-25 by Kevin Feasel

Bob Dorr gets synchronized:

This post is not about a specific SQL Server object but instead outlines a technique used in various locations to reduce contention while still providing thread synchronization.  There are hundreds of locations throughout the SQL Server code base that must account for multi-threaded access.   A common technique used in multi-threaded coding is a reader, writer lock.

The idea behind a reader, writer synchronization object is to allow reader parallelization in conjunction with writer synchronization.  Let’s look at a simple pattern of a single path synchronization object.  (Example: spinlock)

Click through for a bit of pseudo-code and explanation.

Comments closed

Learning about Locks in SQL Server

Published 2022-02-22 by Kevin Feasel

Bob Dorr migrates over a couple of posts on locking. The first one describes lock enumeration:

SQL Server has a lock iterator class which is used by Lock Monitor (deadlock detection), DMVs such as dm_tran_locks and other workers.   The iterator is designed to remain lock free whenever possible to avoid contention with active queries.  To accomplish this the iterator uses a marker (bookmark) approach and the lock type of NL.  The NL lock does not block and provides a bookmark (reference count and location) supporting the iterator.

The other post covers lock partitioning:

Shared access blocks exclusive acquire requests. The shared access needs only to acquire on the worker’s local partition. An attempt to acquire exclusive access is blocked by the shared access holders. Conversely to acquire exclusive access the worker must acquire the exclusive access of every partition. The exclusive acquisition path is longer than the shared path because N partitions must be acquired to achieve the exclusive access. Increasing the number of partitions increases the work required to acquire exclusive access. This means that only certain protection paths should use the partitioning approach.

Both get pretty deep into internals.

1 Comment

SQL Server Scheduler Affinity

Published 2022-02-07 by Kevin Feasel

Bob Dorr re-posts an oldie but goodie:

SQL Server uses 3 types of affinity to control where the SQL Server worker threads execute.  Before explaining the different scheduler affinity types let me clarify some terminology.

Click through to see what these three types of affinity are, as well as some of the terms you’ll need to know in order to understand affinity types.

Comments closed