Row Level Versioning

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Roy Ernest » Row Level Versioning

44 posts, Page 2 of 5

««1 2 3 4 5 »»»

Row Level Versioning

Rate Topic

Display Mode

Topic Options

Author

Message

Roy Ernest

Posted Wednesday, April 09, 2008 6:08 AM

Hall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 7:52 AM
Points: 3,280, Visits: 6,622

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

paul.lewis

Posted Friday, April 25, 2008 1:02 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, May 13, 2013 11:47 AM
Points: 14, Visits: 56

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

Roy Ernest

Posted Friday, April 25, 2008 1:06 PM

Hall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 7:52 AM
Points: 3,280, Visits: 6,622

Paul, I tried the set up three times and the three times it worked just fine.

-Roy

Post #490896

sharon.bender

Posted Wednesday, July 23, 2008 6:42 PM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, February 18, 2011 2:05 PM
Points: 95, Visits: 163

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

Roy Ernest

Posted Thursday, July 24, 2008 6:47 AM

Hall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 7:52 AM
Points: 3,280, Visits: 6,622

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

Bart-328753

Posted Friday, March 06, 2009 1:03 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 8:21 AM
Points: 2, Visits: 40

Paul,

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

Regards,

Bart de Vries

Post #669978

Bart-328753

Posted Friday, March 06, 2009 1:07 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, January 16, 2013 8:21 AM
Points: 2, Visits: 40

Paul,

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

Regards,

Bart de Vries

Post #669979

Karsten-159275

Posted Friday, March 06, 2009 1:09 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, June 02, 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

Drikus Roux

Posted Friday, March 06, 2009 2:04 AM

Grasshopper

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 w00t

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. Hehe

Post #670006

leifah

Posted Friday, March 06, 2009 2:38 AM

SSC-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 »

44 posts, Page 2 of 5

««1 2 3 4 5 »»»

Permissions