Introduction A couple of years a go I wrote an article about monitoring the version store. With SQL Server 2017 a new DMV has been added to make this easier.
Examples
Recently I have had to extract user’s details from Active Directory (AD) for certain security groups. Having looked through a slew of internet resources it is obvious that the work required to do this has changed little over many years – and it looks like is was deliberately designed to make it difficult. I’d like …
Partitioned views have been around since before SQL Server 2005 (whereupon partitioned tables were introduced) but this does not mean that they aren’t without their uses now.
Optimistic locking requires the use of row versioning, where a copy of the data about to be modified is stored in TempDB. The advantage of this approach is that the number of locks are reduced and the opportunities for blocking are also reduced. The downside is that the resources required for data modifications are increased, …
In a previous article (here) I described how the isolation level read committed works, with some examples. There is also an optimistic version of this – read committed snapshot, which uses row versions. The behaviour of read committed and read committed snapshot is similar and repeating the examples used previously will show the similarities and …
The previous articles on isolation levels were using pessimistic locking. Two further isolation levels exist that use optimistic locking and are row-versioning based. Snapshot Isolation With snapshot isolation level no locks are placed upon the data when it is read and transactions that write data don’t block snapshot isolation transactions from reading data. Instead, when …
In the previous article it was shown that phantom reads and skipped rows were possible with the repeatable read isolation level. Serializable is the highest isolation level and will not allow phantom reads or skipped rows.
In my previous article I showed how read committed would allow rows to be updated that it had already processed in a query, because it releases locks as soon as it has processed that row or page (depending upon the type of lock taken). A second query using the same criteria would then show different …
In a previous article I demonstrated how use of READUNCOMMITTED (or the NOLOCK hint) can result in erroneous data, due to page splits. Examples abound of the more common issue with this isolation level, which occur when reading a row that is being updated in another transaction and then rolled back. These so-called ‘dirty reads’ …
Having prepared the Distributor in the previous article it is now possible to create a Subscription. First of all create a test database and table on the Publisher (Publisher-A): A database is also required on the Subscriber (Subscriber-C): Now back to the Publisher. This server needs to be configured as a Publisher. The steps used …
Notes on SQL 