As we know, higher isolation levels acquire more locks and
as such they use more CPU, but is the increase significant? In some case the
increase is very noticeable, and here are a few benchmarks illustrating my
point.
Prerequisites:
I created the following table:
CREATE TABLE
Data.SomeData(ID
INT NOT NULL
CONSTRAINT
PK_SomeData PRIMARY KEY,
SomeData VARCHAR(10),
MoreData CHAR(1000)
)
GO
and populated it with 1 million rows. (The full repro script
is at the end of the post).
Reports that touch
large groups of rows.
First, I considered a situation when reports are touching
large groups of rows with no concurrency. The following procedures were
imitating this type of activity:
CREATE PROCEDURE
dbo.Scan_CommittedReads
AS
SET TRANSACTION
ISOLATION LEVEL
READ COMMITTED
DECLARE @d INT, @n INT;
SET @d = 0;
WHILE @d < 1000 BEGIN
SELECT
@n = COUNT(*) FROM Data.SomeData WHERE ID BETWEEN 1 AND @d
SET
@d = @d + 1
END
GO
I created a procedure for each isolation level, and I turned
READ_COMMITTED_SNAPSHOT off. I ran the procedures several times, so that the
cache was warm, and the procedures were already parsed and compiled. Typical
CPU consumption in milliseconds when the cache is warm are as follows:
240 – dirty reads,
360 - read committed,
710 –
repeatable read,
720 – serializable,
400 - snapshot
![]()
I know this should be nice graph, but so far I am having problems trying to paste a graph here. I'll fix it later.
As you have seen, in this situation the overhead of CPU
spent on acquiring and releasing locks is very significant. In fact, more CPU
is used on locking under both repeatable read and serializable levels, than is
used on everything else.
Next, I turned READ_COMMITTED_SNAPSHOT on, and reran the
benchmarks. The results were very similar.
Frequent single row
reads.
Also I considered a situation when applications frequently
read a single row via a clustered index. The following procedures were
imitating this type of activity:
CREATE PROCEDURE
dbo.Loop_CommittedReads
AS
SET TRANSACTION
ISOLATION LEVEL
READ COMMITTED
DECLARE @d INT, @n INT;
SET @d = 0;
WHILE @d < 100000 BEGIN
SELECT
@n = COUNT(*) FROM Data.SomeData WHERE ID =@d
SET
@d = @d + 1
END
GO
Again, I created a procedure for each isolation level. This
time I consistently observed a significantly smaller difference. Although the
procedures using both repeatable read and serializable levels were running
consistently slower, they were only about 10% slower. Note that the overhead of
iterating through the loop itself was very low, less than 10% of overall real
execution costs. To figure that out, I ran the following procedure:
CREATE PROCEDURE
dbo.OnlyLoop
AS
DECLARE @d INT;
SET @d = 0;
WHILE @d < 100000 BEGIN
SET @d = @d + 1
END
GO
As you have seen, in some (but not all) cases the CPU
overhead of repeatable read and serializable isolation levels can be very high.
Of course, all these benchmarks were run without any concurrency, which is not
quite realistic. I am going to post more benchmarks for snapshot isolation
involving selects and concurrent modifications.
The complete repro
script:
CREATE TABLE
Data.Numbers(n INT)
GO
SET NOCOUNT
ON
DECLARE @d INT
SET @d = 0
WHILE @d < 1000000 BEGIN
INSERT
INTO Data.Numbers(n) VALUES(@d)
SET
@d = @d + 1
END
GO
CREATE TABLE
Data.SomeData(ID
INT NOT NULL
CONSTRAINT
PK_SomeData PRIMARY KEY,
SomeData VARCHAR(10),
MoreData CHAR(1000)
)
GO
INSERT INTO
Data.SomeData(ID, SomeData, MoreData)
SELECT
n, 'as'+CAST(n AS VARCHAR(10)), 'asdf'
FROM
Data.Numbers
GO
CREATE VIEW
dbo.IsolationLevelProcedures
AS
SELECT ProcedureType, IsolationLevel,
IsolationLevelDescription
FROM(
SELECT 'Loop'
AS ProcedureType UNION
ALL
SELECT 'Scan') ProcedureTypes
CROSS JOIN
(
SELECT 'DirtyReads'
AS IsolationLevelDescription, 'READ UNCOMMITTED' AS IsolationLevel UNION
ALL
SELECT 'CommittedReads'
AS IsolationLevelDescription, 'READ COMMITTED' AS IsolationLevel UNION
ALL
SELECT 'RepeatableReads'
AS IsolationLevelDescription, 'REPEATABLE READ' AS IsolationLevel UNION
ALL
SELECT 'Serializable'
AS IsolationLevelDescription, 'SERIALIZABLE' AS IsolationLevel UNION
ALL
SELECT 'Snapshot'
AS IsolationLevelDescription, 'SNAPSHOT' AS IsolationLevel
) IsolationLevels
GO
SELECT 'CREATE
PROCEDURE dbo.'+ ProcedureType + '_' + IsolationLevelDescription +'
AS
SET TRANSACTION ISOLATION LEVEL ' + IsolationLevel + '
DECLARE @d INT, @n INT;
SET @d = 0;
WHILE @d < '
+ CASE WHEN ProcedureType = 'Scan' THEN '1000' ELSE '100000' END + ' BEGIN
SELECT @n = COUNT(*)
FROM Data.SomeData WHERE ID '
+ CASE
WHEN ProcedureType =
'Scan' THEN 'BETWEEN 1 AND @d' ELSE
'=@d' END + '
SET @d = @d + 1
END
GO'
FROM dbo.IsolationLevelProcedures
SELECT 'EXEC
dbo.'+ ProcedureType + '_' + IsolationLevelDescription +'
GO'
FROM dbo.IsolationLevelProcedures
SELECT 'DROP
PROCEDURE dbo.'+ ProcedureType + '_' + IsolationLevelDescription +'
GO'
FROM dbo.IsolationLevelProcedures
GO
CREATE PROCEDURE
dbo.OnlyLoop
AS
DECLARE @d INT;
SET @d = 0;
WHILE @d < 100000 BEGIN
SET
@d = @d + 1
END
GO
ALTER DATABASE
Test SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE
Test SET ALLOW_SNAPSHOT_ISOLATION ON
GO