THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

CPU Overhead for Higher Isolation Levels

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

 

 

 

Published Saturday, July 12, 2008 9:19 PM by Alexander Kuznetsov

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on sqlblog.com, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on simple-talk.com and devx.com. Currently he works as an agile developer.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement