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.
There is also considerable overhead in terms of memory usage. Every non-empty off-row value adds 50+ bytes of the overhead regardless of its size. Those 50+ bytes consist of three artificial ID values (in-row, off-row in data row and leaf-level of the range index) and off-row data row structure. It is even larger in case of LOB columns where data is stored in LOB Page Allocator.
One of the key points to remember that decision which columns go off-row is made based on the table schema. This is very different from on-disk tables, where such decision is made on per-row basis and depends on the data row size. With on-disk tables, data is stored in row when it fits on the data page.
In-Memory OLTP works in the different way. (Max) columns are always stored off-row. For other columns, if the data row size in the table definition can exceed 8,060 bytes, SQL Server pushes largest variable-length column(s) off-row. Again, it does not depend on amount of the data you store there.
This is a great article getting into the internals of how memory-optimized tables work in SQL Server 2016, as well as a solid reason to avoid LOB types and and very large VARCHAR values on memory-optimized tables if you can. Absolutely worth a read.