Snapshot Isolation

Question

Snapshot Isolation

SQL Galaxy

One Orange Chip

Points: 29472
More actions
January 25, 2018 at 3:00 am

#402995

I want to change default Isolation in SQL Server 2008 R2 version..
Need to change to Snapshot Isolation from read committed. for too many SELECT and UPDATE queries are concurrently executing through application also users are face issues application hanging due to many SPID blocking.
1. ALTER DATABASE DBNAME SET ALLOW_SNAPSHOT_ISOLATION ON
2. ALTER DATABASE DBNAME SET READ_COMMITTED_SNAPSHOT ON
should i execute both the statements for change Snapshot Isolation? or ALLOW_SNAPSHOT_ISOLATION ON will enough?
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Emil B SSCertifiable Points: 5568 More actions · Answer 1

from top of my head

1st statement will allow you to use snapshot isolation level when specified i.e. SET TRANSACTION ISOLATION....
2nd will make it your default isolation level

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

Thank you..
First statement executed successfully immediately - Will it require restart SQL Server for taking effect?

Second Statement tested Development setup - but it will take too much time for execution..
Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

The first allows you to use SET TRANSACTION ISOLATION LEVEL SNAPSHOT in your queries. SNAPSHOT cannot be made a default isolation level
The second makes changes the default isolation level from read committed to read committed using row versions (similar, but not the same as SNAPSHOT)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 4

Thank you Gail..
For second statement READ_COMMITTED_SNAPSHOT ON - it will take too much time for completion at DB level.
Will it require DB need to bring single_User mode for execution?
Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Yes, it will take a while if there's stuff running. It doesn't need single user, but it can't complete until all requests running at the point it started have finished.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 6

I successfully executed as below command.. how can confirm whether changes done or not?
ALTER DATABASE DBNAME SET ALLOW_SNAPSHOT_ISOLATION ON

How can we review ALLOW_SNAPSHOT_ISOLATION has been changed at database?

DBCC useroptions -command see only instance level ISOLATION only..in my case display Read committed.
Thanks

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

SELECT snapshot_isolation_state_desc FROM sys.databases WHERE name = 'MyDatabase'

John

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 8

SQL Galaxy

One Orange Chip

Points: 29472

January 26, 2018 at 4:04 am

#1977498

Thank you John...🙂

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 9

When either the READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION database options.
update and delete transactions for a particular database must maintain row versions even when there are no transactions using a row versioning-based isolation level.
Both are same effect in database query, am i correct?
I verified sys.database now database properties are ALLOW_SNAPSHOT_ISOLATION = ON state, and READ_COMMITTED_SNAPSHOT = OFF state
Both are same effect in database query, am i correct? or Need to bring ON for READ_COMMITTED_SNAPSHOT.
Thanks

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Keep in mind that enabling that has had only two effects.

1) All data modifications will now be writing their previous versions into the version store in TempDB
2) You can specify SET TRANSACTION ISOLATION LEVEL SNAPSHOT in batches or stored procedures to change them to Snapshot isolation level.

It has done nothing else.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

And no, READ COMMITTED SNAPSHOT and ALLOW SNAPSHOT ISOLATION are not the same thing, they don not have the same effect in the database.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass