Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » Administering » SNAPSHOT Isolation with (NOLOCK) being used...
Add to briefcase
25 posts, Page 2 of 3
««123»»

SNAPSHOT Isolation with (NOLOCK) being used all over Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
GilaMonster
Posted Friday, November 23, 2012 12:39 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744, Visits: 30,025
You also might mention that getting the wrong result fast is usually not acceptable to users. Most users would rather have the correct results, even if it does take slightly longer.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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
Post #1388238
Perry Whittle
Posted Monday, November 26, 2012 5:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 5,204, Visits: 11,165
SQL_ME_RICH (11/21/2012)
Recently, my applications architect decided that we needed to change the level of the database Isolation from the the default READ COMMITTED to SNAPSHOT ISOLATION (basically Read Committed with Snapshot Isolation) saying that it use to be in place, and that over the course of time (and multiple server leases coming to an end and being moved to newer systems) that it had been set back to the default

This is controlled by a SET option in the database and is therefore transfered with the database. If it's switched off then someone issued a

ALTER DATABASE mydb SET ALLOW_SNAPSHOT_ISOLATION OFF

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1388546
SQL_ME_RICH
Posted Monday, November 26, 2012 11:18 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 2:58 PM
Points: 438, Visits: 1,145
Perry - thank you for confirming this. I knew it was a DBCC command that needed to be issued. The problem is that we have no idea when it happened, and doubtful that they are going to go back in the logs to try and find out when it happened. The DA said he had it put in place over 3 years ago, so...
Post #1388745
GilaMonster
Posted Monday, November 26, 2012 11:21 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744, Visits: 30,025
SQL_ME_RICH (11/26/2012)
Perry - thank you for confirming this. I knew it was a DBCC command that needed to be issued.


It's not a DBCC command. Just the ALTER DATABASE that Perry listed.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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
Post #1388747
SQL_ME_RICH
Posted Monday, November 26, 2012 11:24 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 2:58 PM
Points: 438, Visits: 1,145
This is what happens when I respond to posts before coffee - thanks for that Gail!
Post #1388750
Perry Whittle
Posted Monday, November 26, 2012 11:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 5,204, Visits: 11,165
SQL_ME_RICH (11/26/2012)
This is what happens when I respond to posts before coffee - thanks for that Gail!

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1388753
SQL_ME_RICH
Posted Monday, November 26, 2012 11:46 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 2:58 PM
Points: 438, Visits: 1,145
hehe - thank you too Perry.

:)
Post #1388754
Perry Whittle
Posted Monday, November 26, 2012 12:22 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 5,204, Visits: 11,165
To ensure in the future that the option persists you could use policy management to create a new policy enforcing the snapshot isolation state. You'll need to select the Database facet and check for the @SnapshotIsolationState for snapshot isolation or @IsReadCommittedSnapshotOn for read committed snapshot isolation

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1388782
SQL_ME_RICH
Posted Monday, November 26, 2012 12:52 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 2:58 PM
Points: 438, Visits: 1,145
That is a project I was planning to define once we get out of our 'code freeze' period that the company is in right now. That won't be until Jan.2013, but this gives me an idea on some things I was already working on. Again - thank you both for your tremendous response and support!
Post #1388799
Perry Whittle
Posted Monday, November 26, 2012 1:27 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:44 AM
Points: 5,204, Visits: 11,165
Doesn't stop you creating the policies in your dev system, they'll be ready to deploy in Jan then

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1388821
« Prev Topic | Next Topic »

Add to briefcase
25 posts, Page 2 of 3
««123»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.