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 ««12345»»»

Row Level Versioning Expand / Collapse
Author
Message
Posted Wednesday, April 9, 2008 6:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 16, 2014 7:31 AM
Points: 2,361, Visits: 6,749
Thanks Anirban:). I tried. I did miss couple of things like others have pointed out. I have to keep in mind to cover all bases when I write my next article.


-Roy
Post #482199
Posted Friday, April 25, 2008 1:02 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 3, 2014 8:31 AM
Points: 14, Visits: 66
Did anyone else try this and not get the expected results? When I tried this, and ran tran a without committing, the value in the select statement was 2, not 1212121.
Post #490894
Posted Friday, April 25, 2008 1:06 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 16, 2014 7:31 AM
Points: 2,361, Visits: 6,749
Paul, I tried the set up three times and the three times it worked just fine.

-Roy
Post #490896
Posted Wednesday, July 23, 2008 6:42 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, July 5, 2014 7:04 PM
Points: 95, Visits: 164
Thanks Roy, for explaining that.

We have been running into some deadlocks lately, due to one main sort of Lookup form our app uses. The number of rows generally returned has doubled recently due to some data additions, and this is locking the select statement long enough to cause drama for users who are trying to update individual records, some of which appear in the Select.

I recently changed the Select to use WITH (NOLOCK) much to the chagrin of several DBAs out there who are scared of "dirty reads".

In the situations where we are using our data, switching the database settings to read-committed row-versioning would probably be a much better long-term solution.

Although I am still rather concerned about the hit the TempDB would take as a result of this. At peak times, our SQL Server is already chewing through somewhere around 6Gb of memory, and I'm afraid this would push it over the edge.

Has anyone done any particular load testing on how the TempDB is impacted by such a setting change?
Post #539792
Posted Thursday, July 24, 2008 6:47 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, October 16, 2014 7:31 AM
Points: 2,361, Visits: 6,749
Hi Sharon,

What you have to do or make sure of is to confirm that the TempDB Database files are not on the same drive as that of your Main DB. Also make sure that the TempDB is not in the same drive as the Operating System. 6GB Memory usage is not that drastic. We use way more than that.

Roy


-Roy
Post #540071
Posted Friday, March 6, 2009 1:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 13, 2013 1:42 PM
Points: 2, Visits: 41
Paul,

Executing the select in a different query window does the trick(In my case...)

Regards,

Bart de Vries
Post #669978
Posted Friday, March 6, 2009 1:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 13, 2013 1:42 PM
Points: 2, Visits: 41
Paul,

Executing the select in a different query window does the trick(In my case...)

Regards,

Bart de Vries
Post #669979
Posted Friday, March 6, 2009 1:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 2, 2011 7:15 AM
Points: 1, Visits: 72
Thank you for this great article!

A thought crossed my mind, whether this is similar to the default implementation of READ COMMITTED isolation level in Oracle databases, isn't it?

Post #669981
Posted Friday, March 6, 2009 2:04 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 29, 2013 1:29 AM
Points: 22, Visits: 180
Nice technology, however one will have to consider several things and specifically the impact on temdb carefully.

For understanding SQL Server locking, Erland Sommarskog has a great script: Download now, that you can use to see locking behaviour on SQL Server.

MSDN article "SQL Server 2005 Row Versioning-based Transaction Isolation" by Kimberly Tripp and Neal Graves is a very comprehensive treatise on the subject of row based versioning. In there the different cases under which certain isolation levels are to be considered are discussed with pros and cons.

In my opinion, deciding on an isolation level in 2005 or 2008, you have to know your application's needs very well and also be very up to scratch on how you configure your SQL Server disk array, especially if you operate on a SAN, particularly where to place tempdb. Make a mistake with one of these and your Row versioning based transaction isolation will be an impediment rather than salvation.
Post #670006
Posted Friday, March 6, 2009 2:38 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 19, 2013 8:33 AM
Points: 162, Visits: 362
...or the same functionality INTERBASE introduced a decade ago?

Sorry for being slightly off topic, but however much I like SQL Server, Reporting Services & Integrations services, I think it's really sad that Interbase didn't get more appreciation. Great developers and technology, lousy marketing.

Their version explained here: http://www.dbginc.com/tech_pprs/IB.html



Post #670023
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse