Category: Internals

Mike Ruthruff discusses changes in checkpoint behavior in SQL Server 2016:

The following are the primary changes which will impact behavior of checkpoint in SQL Server 2016.

1. Indirect checkpoint is the default behavior for new databases created in SQL Server 2016. Databases which were upgraded in place or restored from a previous version of SQL Server will use the previous automatic checkpoint behavior unless explicitly altered to use indirect checkpoint.

2. When performing a checkpoint SQL Server considers the response time of the I/O’s and adjusts the amount of outstanding I/O in response to response times exceeding a certain threshold. In versions prior to SQL Server 2016 this threshold was 20ms. In SQL Server 2016 the threshold is now 50ms. This means that SQL Server 2016 will wait longer before backing off the amount of outstanding I/O it is issuing.

3. The SQL Server engine will consolidate modified pages into a single physical transfer if the data pages are contiguous at the physical level. In prior versions, the max size for a transfer was 256KB. Starting with SQL Server 2016 the max size of a physical transfer has been increased to 1MB potentially making the physical transfers more efficient. Keep in mind these are based on continuity of the pages and hence workload dependent.

Definitely read the whole thing.

Ewald Cress discusses a “secret” spinlock within latches:

No matter how bad contention gets for normal spinlocks, at least we account for cycles spent spinning: this stuff gets exposed in sys.dm_os_spinlock_stats and can allow us to diagnose and quantify contention. However, spinning done on a latch’s spinlock gets no obviously visible accounting. As such, if we did somehow manage to accrue a large number of spins on a very hot latch, it wouldn’t be obvious that the time went into spinning. This is not to say of course that it is necessarily a common problem, just that it would be hard to prove one way or the other.

If I appear to be painting a bleak picture, I apologise. Given the importance of latches, especially buffer latches, one would assume that the SQL Server development teams would be on the constant lookout for opportunities to identify issues and mitigate against them. And this is exactly the kind of scenario where some bright spark comes up with the idea of superlatches.

Read the whole thing.

Ewald Cress looks at the Count member of the LatchBase class:

Here is the bit-level layout of Count to the level that I currently understand it. This has received some airplay by Bob Ward (thanks, Bob!), and I’ll be building on that. Count is a 64-bit integer broken into multiple bit fields; aside from more compact storage, the rationale for the bit packing is that the whole item can be subject to atomic updates without “external” locking, much as in the SOS_RWLock. Regarding the unlabelled bits, I know for a fact that bit 5 is used, but not yet sure of the semantics.

After spending several posts on the foundation structures, Ewald is moving up the layers of internals, getting closer to concepts we think about on a day-to-day basis.

Frank Gill looks at the fn_dblog_xtp function:

Transactions against an in-memory table will return a single row in the result set from fn_dblog, while the result set from fn_dblog_xtp will contain a row for all activity. For example, if you insert 100 rows into an in-memory table, fn_dblog will contain 3 records: a BEGIN TRANSACTION, a single row for the INSERT, and a COMMIT TRANSACTION. fn_dblog_xtp will contain 102 records: a BEGIN TRANSACTION, and a row for each row inserted, and a COMMIT TRANSACTION.

One of the new columns in fn_dblog_xtp is xtp_object_id. I tried to join this to the sys.objects table to return the object name, but the values didn’t match. After banging my head against my monitor for a while, I posed the question to #sqlhelp on Twitter. Andreas Wolter (b|t) responded that a correlation can be made using DMV sys.memory_optimized_tables_internal_attributes.  Excited, I tried to join fn_dblog_xtp to the DMV, but was disappointed.

Read the whole thing.

Ewald Cress looks at the SOS_WaitableAddress class next in his series on internals:

All the synchronisation mechanisms I have discussed so far have one things in common: in order to be globally visible, they involve synchronisation objects embedded within the things they protect. So for instance, if we want to protect the global scheduler list with a spinlock, that spinlock lives within the global scheduler list, and allocating the spinlock’s storage (lightweight as it is) is the responsibility of whoever creates that list. But what if there were millions of things we might occasionally want to lock, and we don’t want to embed a lock within each such item due to the hassle and/or overhead involved? What if we just wanted a publicly visible corkboard upon which we can pin notes describing the items which are currently locked?

This is a rather different locking structure than we’ve seen so far.  Read on for details, including magic within the Signal method.

Ewald Cress looks at SOS_RWLock, a reader-writer lock (at least the pre-2016 version):

This lock class can best be appreciated by comparing it to a mutex. Like the mutex, a reader-writer lock can only be acquired in exclusive mode by one requestor at a time, but instead of only exposing this exclusive-acquire (Writer) option, it alternatively allows acquisition in shared (Reader) mode. This stuff is completely natural to us database folks of course, because the semantics is a subset of the behaviours we get from familiar database locks.

Basic rules of the road:

• Any number of simultaneous “clients” can share ownership of the lock in Read mode.

• Readers block writers.

• Writers block readers and other writers.

• Blocking means that the requesting worker gets suspended (scheduled off the processor) and accrues a wait of a type specified in the lock acquisition request.

There’s a huge amount of detail here, and I for one am glad that there isn’t a quiz later..

Ewald Cress continues his dive into system internals, this time looking at SOS_Mutex:

Put differently, we can build a mutex from an auto-reset EventInternal by tacking on an owner attribute, making a rule that only the owner has the right to signal the event, and adding assignment of ownership as a fringe benefit of a successful wait. A nonsignalled event means an acquired mutex, and a signalled event means that the next acquisition attempt will succeed without waiting, since nobody currently owns the mutex. The end result is that our SOS_Mutex class exposes the underlying event’sSignal() method and its own take on Wait(). From the viewpoint of the mutex consumer, the result of a successful wait is that it owns the mutex, and it should act honourably by calling Signal() as soon as it is done using the resource that the mutex stands guard over.

There’s some deep detail here, so this is definitely one of those “after your first cup of coffee” posts to read.