Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

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


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:42 PM
Points: 41,516, Visits: 34,431
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
Posted Monday, November 26, 2012 5:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:15 PM
Points: 5,951, Visits: 12,822
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
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: Today @ 4:55 PM
Points: 461, Visits: 1,263
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
Posted Monday, November 26, 2012 11:21 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:42 PM
Points: 41,516, Visits: 34,431
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
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: Today @ 4:55 PM
Points: 461, Visits: 1,263
This is what happens when I respond to posts before coffee - thanks for that Gail!

Post #1388750
Posted Monday, November 26, 2012 11:44 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:15 PM
Points: 5,951, Visits: 12,822
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
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: Today @ 4:55 PM
Points: 461, Visits: 1,263
hehe - thank you too Perry.

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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:15 PM
Points: 5,951, Visits: 12,822
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
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: Today @ 4:55 PM
Points: 461, Visits: 1,263
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
Posted Monday, November 26, 2012 1:27 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:15 PM
Points: 5,951, Visits: 12,822
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 ««123»»

Permissions Expand / Collapse