StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POIf one avoids READ_COMMITTED_SNAPSHOT and WITH NOLOCK, whats next?
primarykey
Id
6536650
data
AcceptedAnswerId
6540289
AnswerCount
1
ClosedDate
CommentCount
3
CommunityOwnedDate
CreationDate
2011-06-30T14:49:28.700
FavoriteCount
0
LastActivityDate
2011-06-30T19:53:30.507
LastEditDate
2017-05-23T12:04:24.057
LastEditorUserId
-1
OwnerUserId
371637
ParentId
0
PostTypeId
1
Score
0
ViewCount
317
LastEditorDisplayName
text
Body
Wow - so much info on why to avoid both options. <a href="https://stackoverflow.com/questions/4587276/to-nolock-or-not-to-nolock-that-is-the-question">To NOLOCK or NOT to NOLOCK, that is the question</a> <a href="http://www.jimmcleod.net/blog/index.php/2009/08/27/the-potential-dangers-of-the-read-committed-snapshot-isolation-level/" rel="nofollow noreferrer">http://www.jimmcleod.net/blog/index.php/2009/08/27/the-potential-dangers-of-the-read-committed-snapshot-isolation-level/</a> Some say go ahead and use read-committed and use locking hints. Others say you should generally avoid locking hints. Turning read-committed on can cause very unexpected behavior in I think a lot of typical applications in how systems wrap basic transactional code. So - the problem remains for us. We see on a production system many blocking queries after a sybase migration. Transaction time is extremely important to us - some are blocking for 10+ seconds - all over the place. If one is to avoid either techniques - I would like to understand some internals of what is going on here. Can this problem always be solved by better/proper indexes or can index switching in queries still cause locks (for instance, an update can use one non clustered index to search, and the clustered index for the row locking) Lets say we do the following: Select * from Customer Where CreatedDate > '6/30/2011' CreatedDate has an index but is obviously not a key. We see cases like this causing blocks (not deadlocks - but long waits) to statements such as (this is just demo code to show a similar scenario) Update Customer Set OrderReviewed=1 Where CreatedDate Between '6/24/2011' and '7/2/2011' Some say Select * from Customer with (nolock) Where CreatedDate > '6/30/2011' Is the way to go - however since Customer can be deleted, page splits, etc you can suffer from a variety of issues. We cannot turn READ_COMMITTED_SNAPSHOT on now for potential application impact. We have an index on CreatedDate - so... what else can one do? I'm looking for some expert advice here - meaty good technical info - not something like 'turn profiler on' or 'see what is blocking' as we know what is blocking, just not the internals of why or the best way to solve it. 
Tags
<sql-server-2005><tsql><transactions><indexing>
Title
If one avoids READ_COMMITTED_SNAPSHOT and WITH NOLOCK, whats next?
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USCommunity
UserOwnerUserId
1. USAdam Tuliper - MSFT
plurals
PostLinksPostIdRelatedPostId
1. PL
 singulars
 LinkTypeLinkTypeId
 LTLinked
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.