Are deletes more costly when you allow snapshot isolation?

Question

In his blog post implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide Brent Ozar explains how to turn on snapshot isolation for a database but still allow sessions to use the regular read committed isolation level. I did do this on one of my test servers and did some measurements while still only using read committed isolation level, just to see what the impact of the 14 extra bytes per record would be. I thougt that maybe inserts and selects would be slightly costlier, because the pages fill up more quickly. What I saw was that deletes were 25% more costly.

I used SQL Server 2019, Standard Edition.
I created two databases, one with ALLOW SNAPSHOT ISOLATION ON and one without.
In both deatabases, I created two tables.
tblMain (k int NOT NULL IDENTITY(1,1) PRIMARY KEY, vc varchar(10));
tblDetail (i int NOT NULL IDENTITY(1,1) PRIMARY KEY, k INT NOT NULL FOREIGN KEY REFERENCES tblMain (k), vc varchar(30) NULL, c char(100) NOT NULL DEFAULT N'')
I inserted 3 rows into tblMain and about a 1M rows in tblDetail the value for k being 1, 2, 3, 1, 2, 3... and a copy of the vc field of the referenced record in tblMain.
In step 2 I updated all records in tblDetail with the k = 2 and updating the field vc to vc + vc.
Finally, I deleted all records in tblDetail with k = 3.

I did this test 5 times and took the average of the 5 tests. Here are the results.

                            |   snapshot isolation
Action                      |   without |     with
----------------------------+-----------+----------
Insert of 1,046,529 records | 11,111 ms | 11,977 ms
Update of   348,843 records |  4,378 ms |  5,055 ms
Select of   348,843 records |  4,337 ms |  4,506 ms
Delete of   348,843 records |  7,309 ms |  9,431 ms

I do not trust these numbers. The slight difference in update performance, I did expect, but why should the deletes be so costly? What did I do wrong making these measurements?

Code used (notice you have to play with the comments to do the entire test).

USE master;
CREATE DATABASE [read committed];
CREATE DATABASE [snapshot isolation];
ALTER DATABASE  [snapshot isolation] SET ALLOW_SNAPSHOT_ISOLATION ON;

USE [read committed];
--USE [snapshot isolation];
GO
CREATE TABLE tblMain
( k INT NOT NULL PRIMARY KEY
, vc varchar(10)
);
GO
CREATE TABLE tblDetail
( i INT NOT NULL PRIMARY KEY
, k INT NOT NULL FOREIGN KEY REFERENCES tblMain (k)
, vc VARCHAR(30) NULL
, c CHAR(100) NOT NULL DEFAULT N''
);
CREATE INDEX ix_tblDetail_k ON tblDetail(k);
GO

INSERT INTO tblMain (k, vc) VALUES (1, 'aap'), (2, 'noot'), (3, 'mies');

SET NOCOUNT OFF;
PRINT 'INSERT'
SET STATISTICS TIME ON;
;WITH Nr AS
(
  SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS i
  FROM sys.columns c1 CROSS JOIN sys.columns c2
)
, Nr_k AS
(
  SELECT Nr.i, ((Nr.i - 1) % 3 + 1) AS k
  FROM Nr
)
INSERT INTO tblDetail (i, k, vc)
SELECT Nr_k.i, Nr_k.k, m.vc
FROM Nr_k
JOIN tblMain m ON m.k = Nr_k.k;
SET STATISTICS TIME OFF;

PRINT 'UPDATE'
SET STATISTICS TIME ON;
UPDATE tblDetail SET vc = vc + vc WHERE k = 2;
SET STATISTICS TIME OFF;

PRINT 'SELECT'
SET STATISTICS TIME ON;
SELECT * FROM tblDetail WHERE k = 1;
SET STATISTICS TIME OFF;

PRINT 'DELETE'
SET STATISTICS TIME ON;
DELETE FROM tblDetail WHERE k = 3;
SET STATISTICS TIME OFF;
GO

USE master;
-- DROP DATABASE [read committed];
-- DROP DATABASE [snapshot isolation];

Answer 1

Composing a good performance test is a big challenge. There are so many ways you can go wrong. Not the least you may be so focused on the test, that you loose the connection to your actual workload, so that you test something which is not relevant. I know; I've been there myself many times.

I played with your script, and I was largely able to repeat your findings, although I did see a decent difference for the UPDATE operation as well.

I tried some variations, and most of them did not change much, but one did: I switched the order of the UPDATE and the DELETE operation. After this, the UPDATE operation in the Snapshot database runs 3-4 times. longer than in the ReadCommitted database. But not because things are now slower in the Snapshot databsae, but there is a drastic speedup in the ReadCommitted database!

I have some data from tests where my main focus was to try different chunking solutions. That is, break up big operations in chunks to gain speed, and hold down the transaction log. I have main run these tests in plain read committed, but I also have a set of data with the database with READ_COMMITTED_SNAPSHOT. I looked at that data and compared the results for the same operation with plain RC and RCSI, and there is quite a bit of variation. But generally, UPDATE and DELETE operations suffers quite a bit from the snapshot handling, while the INSERT operations not so much, at most 25%. As compared to over 300% for the most affected UPDATE operation and 140% for the most affected DELETE operation. (In my tests, I restored the source database for every test run, so the starting point is always the same.)

All my tests were on a laptop - not really production-grade hardware!

Answer 2

Hi @Chris Sijtsma

Our ERP system is deployed at close to three hundred factories worldwide and I impose READ_COMMITTED_SNAPSHOT ON on all of these installations.
ERP is OLTP, so it's a small percentage of INSERT/UPDATE/DELETE DML and the rest is mostly reads.

READ_COMMITTED_SNAPSHOT gives great tradeoff, minor performance penalty for non-blocking reads. it's SO WELL WORTH it in my experience
Sometimes I see people use the infamous 'nolock/READUNCOMMITTED'. With READ_COMMITTED_SNAPSHOT one does not need to know of what 'nolock' is. I have no code that uses it.

Tempdb is carrying the brunt of performance hit due to row versioning, as well as other activities like sorting, hashing and lob operations.
Our recommendation to our customers is get their tempdb multi-file, and place these on the best I/O drive/SAN system they can afford.
The faster is the I/O system where tempdb is on, the better it is for rowversioning and performance.

It works for them and therefore I.

The drawback is more diligent coding and accounting for false positives, in my "check for existence" code, and at least require these to be WITH (READCOMMITTEDLOCK).
I usually start with READCOMMITTEDLOCK, only when needed, and sometimes have to step up to REPEATABLE READS or SERIALIZABLE in rare cases. But it is no different than with generic READCOMMITTED mode, with no snapshots.

Therefore, I highly recommend it.

Thanks
Vladimir

Answer 3

Hi @Chris Sijtsma ,

Maybe it has something to do with FK. When deleting from the parent table, SQL Server must check for the existence of any FK child rows that refer to that row, when there is no suitable child index, this check performs a full scan of the child table

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

@Vladimir Moldovanenko: Thank you for sharing your thoughts and your data. I will certainly look into it.
@Erland Sommarskog: I second Vladimir's 'THANK YOU'. I also am I huge fan of your articles, rightly dubbed legendary by Vladimir. I also use your ideas on TSQL error handling.