Ewald Cress has a couple of posts about the scheduler. First, fiber mode scheduling:
The title of this post is of course an allusion to Ken Henderson’s classic article The perils of fiber mode, where he hammers home the point that fiber scheduling, a.k.a. lightweight pooling, appears seductive until you realise what you have to give up to use it.
We’ll get to the juicy detail in a moment, but as a reminder, the perils of fibers lie in their promiscuity: many fibers may share one thread, its kernel structures and its thread-local storage. This is no problem for code that was written with fibers in mind, including all of SQLOS, but unfortunately there are bodies of code for which this isn’t true.
Hardware interrupts, which run in kernel mode and return to user mode quickly, should be nothing more than tiny hiccups in a running thread’s quantum. The other 90% of the interrupt iceberg manifests in user mode as Deferred Procedure Calls (DPCs, or “bottom halves” to the Linux crowd) but should still only steal small change in terms of CPU cycles. Context switches to another thread represent a completely different story, because it could be ages before control returns to our thread, meaning that our fiber scheduler is completely out of commission for a while.
This possibility – a SQLOS scheduler losing the CPU for an extended period – is just one of those things we need to live with, but on a sane server, it shouldn’t be something to be too concerned about. Consider that this happens all the time in virtualised environments, where our vCPU can essentially cease to exist while another VM has a ride on the physical CPU.
These are fairly long reads, but we’re getting to levels where you can see these settings in the Database Engine (like Lightweight Pooling).
I like row versioning– see this link for more details:https://technet.microsoft.com/en-us/library/ms189122(v=sql.105).aspx
If your database is enabled for one of the isolation levels that uses row versioning and a row is updated it will have 14 bytes added to it.
Click through for a demo and explanation.
Earlier today someone asked on the #sqlhelp Twitter alias if there is a history of database snapshot creation anywhere, apart from scouring the error logs.
There isn’t, unfortunately, but you can dig around the transaction log of the master database to find some information.
When a database snapshot is created, a bunch of entries are made in the system tables in master and they are all logged, under a transaction named DBMgr::CreateSnapshotDatabase. So that’s where we can begin looking.
Click through for the script and some explanation around it.
Run both code scripts again (Code Script 1 first, and Code Script 2 within 10 seconds). This time, you will see that Code Script 2 completes immediately without being blocked, and when Code Script 1 finishes, it has spawned additional data in its result set. A phantom read in action.
If you want to hide from phantom reads completely, then you’ll need to use either the serializable or snapshot transaction isolation levels. Both of these have the same concurrency effects: No dirty reads, non-repeatable reads, or phantom reads. The difference is in how they are implemented: the serializable transaction isolation level will block all other transactions affecting this data, while the snapshot isolation level utilizes row versions to create connection-specific versions of the table for the transaction – all of these row versions will cause increased activity in the tempdb database. Let’s take a look at how the snapshot isolation level will eradicate the phantom reads. First off, we need to modify the database to accept this isolation level.
After reading Wayne’s post, if you want a more academic (i.e., less fun) read, you can also go back to the Microsoft Research isolation levels paper, which describes most of the isolation levels we have in place today for SQL Server.
1.4) Can I see the data on the page that was locked?
Well, yes. But … do you really need to?
This is slow even on small tables. But it’s kinda fun, so… since you read this far… let’s talk about %%physloc%%!
%%physloc%% is an undocumented piece of magic that will return the physical record locator for every row. You can use %%physloc%% with sys.fn_PhysLocFormatter in SQL Server 2008 and higher.
This was a very interesting read; check it out.
To simplify things initially, we’ll forget about hidden schedulers and assume hard CPU affinity. That gives us an execution environment that looks like this:
Each CPU is physically tied to a scheduler.
Therefore, out of all the workers in the system, there is a subset of workers that will only run on that CPU.
Workers occasionally hand over control of their CPU to a different worker in their scheduler.
At any given moment, each CPU is expected to be running a worker that does something of interest to the middle or upper layers of SQL Server.
Some of this useful work will be done on behalf of the worker’s scheduler siblings.
However, a (hopefully) tiny percentage of a worker’s time is spent within the act of scheduling.
As usual, this is worth the read.
In considering which of these methods is preferred, we need to consider whether page splits impact these methods – especially nasty page splits. Furthermore, how will index maintenance affect each choice? So let’s think this through.
When there are negative values in this column, and the index is rebuilt, there will be a page with both negative and positive values in it. If the identity column is set to (-1, -1), there won’t be a gap (excluding the 0) in the values, and newly added rows will get a new page allocated – a good page split. If the identity column is set to (-2147483648 , 1), then there will be a full page with the records for the most recently used identity value, and with the values starting with 1 – a rather large gap.
This is worth reading in its entirety.
As a quick refresher, a traditional SQLOS spinlock is a 32-bit integer, or of course 64-bit as of 2016, with a value of either zero (lock not acquired) or the 32-bit Windows thread ID of the thread that owns it. All very simple and clean in terms of atomic acquire semantics; the only fun part is the exponential backoff tango that results from a collision.
We have also observed how the 2016 flavour of the SOS_RWLock packs a lot of state into 64 bits, allowing more complicated semantics to be implemented in an atomic compare-and-swap. What seems to be politically incorrect to acknowledge is that these semantics boil down to a simplified version of a storage engine latch, who is the unloved and uncool grandpa nowadays.
Clearly a lot can happen in the middle of 64 bits.
Definitely worth a read, as it seems that this is going to get more play in the years to come.
Proportional fill works by assigning a number to each file in the filegroup, called a ‘skip target’. You can think of this as an inverse weighting, where the higher the value is above 1, the more times that file will be skipped when going round the round robin loop. During the round robin, the skip target for a file is examined, and if it’s equal to 1, an allocation takes place. If the skip target is higher than 1, it’s decremented by 1 (to a minimum value of 1), no allocation takes place, and consideration moves to the next file in the filegroup.
(Note that there’s a further twist to this: when the -E startup parameter is used, each file with a skip target of 1 will be used for 64 consecutive extent allocations before the round robin loop progresses. This is documented in Books Online here and is useful for increasing the contiguity of index leaf levels for very large scans – think data warehouses.)
Read on for some implementation details as well as a good scenario for why it’s important to know about this.
You start with a blank sheet,
three nuts and a bolt,
a strong sense of fairness,
a large can of Jolt.
And you try to imagine,
as best as you’re able
a rulerless kingdom
that won’t grow unstable.
That’s what happens when you dig into internals for too long.