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:
-
Can be compressed (using NTFS compression)
-
During recovery you don’t need to apply logs to recover a read-only file group
-
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
