SQLTeam.com | Weblogs | Forums

Can I use RC snapshot only for specific table or transaction?


#1

Hello guys,
in SQL Server 2008 R2 Standard, can I enable Read Committed Snapshot and use it only for single table ?

My problem is that one update transaction (that I cannot modify) lock two tables and all select query holds until update is finish.

RC snapshot could me help but I don't want enable on all tables and transactions (for potential performance costs) but only for some table or some select transactions.


#2

as far as i know,this is a database level property.


#3

It is enabled at the database level but there are two ways:

ALTER DATABASE MyDatabase
SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE MyDatabase
SET READ_COMMITTED_SNAPSHOT ON

The second means "always use RCSI". The first means "Use RCSI if the transaction asks for it"

See here


#4

[quote="meristema, post:1, topic:4506"]
in SQL Server 2008 R2 Standard, can I enable Read Committed Snapshot and use it only for single table ?[/quote]

No, RCS ON is for the whole db.

That's actually not bad. You can use the ALTER DATABASE command noted above:
ALTER DATABASE <Database_Name> SET ALLOW_SNAPSHOT_ISOLATION ON

and then change the UPDATE statement to specify this command before it runs:
SET TRANSACTION ISOLATION LEVEL SNAPSHOT --active snapshot for this session
UPDATE ...
SET TRANSACTION ISOLATION LEVEL READ COMMITTED --return to not using snapshot

RC snapshot could me help but I don't want enable on all tables and transactions (for potential performance costs) but only for some table or some select transactions.

Excellent insight! Most people ignore -- or are ignorant of -- the overhead, which is indeed considerable. You're doing a great job of combining the best options to produce the best overall results.