Geek City: 24Hours of PASS Followup

Kalen Delaney — Thu, 16 Sep 2010 18:20:00 GMT

Thank you everyone who attended my 24HOP session yesterday. It was a wonderful event to be part of.

Here are a few of the questions that I was asked, that didn't get completely answered.

Question: Can we set READ_COMMITTED_SNAPSHOT ON for just a table rather than at database level?

I apologize that I answered this incorrectly. I was trying to remember all of the locking hints on the spur of the moment, and remembered that there is a hint READCOMMITTED LOCK to force default locking/blocking behavior when the database has been set to READ_COMMITTED_SNAPSHOT, so I was thinking there should be one to go the other way. But now that the stress of the moment is off, I realize that there could not be such a hint. Enabling snapshot isolation means that SQL has to save the old committed values of any changed data as the change is taking place. For a SELECT to just request to read older committed values, when the database has not already been set to save them, would not be possible. The data you're looking for would not be available.

Question: Is it ever appropriate to use the No Lock hint? I'm thinking of using it in certain Select statements.

What does 'appropriate' mean? Like all the phenomena I discussed in my presentation, allowing dirty reads is a choice. You need to be aware of the costs and the tradeoffs, but you might decide in some situations that the value you get is worth the (potentially high) price. I only hope that people don't just choose to use it arbitrarily, just because it's "faster", but that they are aware of the ramifications of performing dirty reads.

Question: Where can I get more information about the tradeoffs between the different isolation levels and concurrency models?

I'm glad you asked. This presentation was only a PREVIEW of what I'll be talking about for an entire day in a pre-conference session for PASS. You can get more details here:

http://sqlpass.eventpoint.com/topic/details/DBA301P

Question: Is there a way to get access to these slides at the end of the presentation please?

I am attaching them to this blog post, along with a PDF of the presentation, and I will also be adding them to my web site at www.SQLServerInternals.com/conferences by the end of the week.

Question: Can Kalen unprotect her tweets?

I could, but it wouldn't be very interesting since I don't tweet and there would be nothing to read. But lately I have started thinking more about it. Maybe I'll start… and if I do start tweeting on a regular basic, my tweets will be open and available.

Thanks again!

~Kalen

Search results matching tags 'PASS', 'concurrency', and 'locking'

Geek City: 24Hours of PASS Followup