What is the best isolation level

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Data Warehousing » Integration Services » What is the best isolation level

What is the best isolation level

Rate Topic

Display Mode

Topic Options

Author

Message

solomon.jernas

Posted Monday, November 19, 2012 12:33 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 1:02 PM
Points: 139, Visits: 633

Could you tell me , Which is best isolation level for SSIS and T- SQL ?

anyone advice me..

Thanks
solomon

Post #1386183

Daniel Bowlin

Posted Monday, November 19, 2012 10:25 AM

SSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:10 PM
Points: 2,673, Visits: 2,418

Best for what? Performance, data integrity, minimal impact on the source system.....the list goes on.

Post #1386490

Sean Lange

Posted Monday, November 19, 2012 10:30 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:33 PM
Points: 8,620, Visits: 8,261

The best isolation level is the one that best fits the needs of your requirements.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1386493

GSquared

Posted Monday, November 19, 2012 10:43 AM

SSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571

Most of the time, Read Committed (the default level) will do what you want and is "the best". If you have specific needs, then pick something else, but, most of the time, the default is the best.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #1386499

solomon.jernas

Posted Monday, November 19, 2012 7:09 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 1:02 PM
Points: 139, Visits: 633

performanace...

Post #1386651

Sean Lange

Posted Tuesday, November 20, 2012 7:27 AM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 2:33 PM
Points: 8,620, Visits: 8,261

solomon.jernas (11/19/2012)

performanace...

Are you saying you want the best performance? We can't tell you what is best. Read what G2 said above, unless there is a definitive reason to do otherwise, the default is probably your best option.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1386901

GSquared

Posted Tuesday, November 20, 2012 7:33 AM

SSCoach

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 1:55 PM
Points: 15,442, Visits: 9,571

solomon.jernas (11/19/2012)

performanace...

You'll have to clarify that.

In most cases, Read Committed will peform just fine. In a few, Read Committed Snapshot, or Snapshot, will perform better. In a few Serializable will actually perform better.

It all depends on what you're doing with the data, how the data is structured, and what you expect applications and users to want from the data and application(s).

If what you're looking for is some sort of recommendation that Read Uncommitted or NoLock will "perform better", you won't get that, because it's false. If you're fishing for that recommendation (I don't know if you are or not), then I'll say what I say ever time that comes up. Yes, it can be faster in some circumstances, so long as you don't mind getting wrong data slightly faster. And it's not faster than other isolation levels (RCSI or SI are often just as fast or faster, so is RC under many circumstances). Sometimes it is, but "fast and wrong" isn't a good solution in the vast majority of cases.

So, performance and locking characteristics really depend on your situation. There is no "Run Faster" option in SQL Server.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Post #1386905

« Prev Topic | Next Topic »

Permissions