Read-Only filegroups and Locking

First published on MSDN on Jan 08, 2007

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