Read-Only filegroups and Locking

  • Article

I recently saw a newsgroup post that sort of implied that accessing data residing on read-only filegroups can save you on locking. Well, it could, but it does not. If you really want to save on locks, the whole database needs to be set to read-only.

Back to read-only filegroups. They provide you the following three benefits:

  1. Can be compressed (using NTFS compression)
  2. During recovery you don’t need to apply logs to recover a read-only file group
  3. Protection of data from accidental modifications

But as far as locking is concerned, SQL Server still gets the locks when you access data from read-only filegroup. Here is one simple example:

use general

go

 

 

alter database general add filegroup foo

go

 

alter database general add file (

name = file1,

filename = 'c:tempfile1')

to filegroup foo

 

-- create a table and associate it to a filegroup

create table t_fg (c1 int, c2 int) on foo

insert into t_fg values (1,1)

 

-- mark the filegroup read-only

alter database general modify filegroup foo read_only

 

-- run a transaction with repeatable read isolation

set transaction isolation level repeatable read

begin tran

select * from t_fg where c1 = 1

 

-- no check the locks

sp_lock @@spid

 

-- here is the output

spid dbid ObjId IndId Type Resource Mode Status

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

53 10 1381579960 0 RID 3:8:0 S GRANT

53 10 0 0 DB S GRANT

53 10 1381579960 0 PAG 3:8 IS GRANT

53 10 1381579960 0 TAB IS GRANT

53 1 1115151018 0 TAB IS GRANT