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.
as far as i know,this is a database level property.
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
1 Like
[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.
1 Like