0

Wow - so much info on why to avoid both options.

To NOLOCK or NOT to NOLOCK, that is the question

http://www.jimmcleod.net/blog/index.php/2009/08/27/the-potential-dangers-of-the-read-committed-snapshot-isolation-level/

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.

Community
  • 1
  • 1
Adam Tuliper
  • 29,982
  • 4
  • 53
  • 71

1 Answers1

0

If you answer "no" to any of these questions then this is most likely your cause:

  • Do you have primary keys on every table?
  • Are these clustered or do you have other clustered indexes?
  • Are statistics on "auto" for create and update
  • Do you have index/statistics maintenance?

And also, stop using SELECT * for one. With this, the index will be ignored or you'll have a key lookup (more likely if there is a few rows). If you don't have a clustered index on the table, you won't even get a key lookup: you can only have a table scan which is expensive

A look at the query plans would also tell you this.

Finally, there is the missing index DMV query which gives where you need indexes the most. This won't override the PK/clustered requirement above

gbn
  • 422,506
  • 82
  • 585
  • 676
  • this is purely demo code - select * isn't actually used - but interesting point though. are you saying if I did use select * - CreatedDate index would be skipped? I'll have to keep that in mind for mngmt studio tests. – Adam Tuliper Jun 30 '11 at 20:07
  • Most likely. yes, it will be skipped – gbn Jun 30 '11 at 20:07
  • The table in question does have a primary key and a clustered index and non clustered indexes for the field in question. I'll check the stats. For maintenance I'll check - note though this db was brought online this past weekend after a full migration (ie many inserts from scripts) and the issue appeared instantly so its 'new' – Adam Tuliper Jun 30 '11 at 20:10
  • any thoughts on the index switching/blocking/waiting? can you see a specific technical reason the wait would occur here? I thought sql server locked a page here and moved on, however the update is searching a range as well. I would almost think a deadlock could occur here but it doesnt - just a long wait. – Adam Tuliper Jun 30 '11 at 20:11
  • 1
    lock esclation maybe locking the table? table scans don't usually cause deadlocks becuase of this – gbn Jun 30 '11 at 20:16
  • I wonder if there is a back and forth lock as they search the data via range. hm... although they should both be a shared lock while searching the NC index I'd imagine until the update aggregates the PKs to update. nice dmv link btw – Adam Tuliper Jun 30 '11 at 20:35
  • As per the "select *" comment above ignoring the index - seems this isn't the case. See: http://www.sqlskills.com/BLOGS/PAUL/post/Which-index-will-SQL-Server-use-to-count-all-rows.aspx – Adam Tuliper Jul 01 '11 at 14:42
  • @Adam Tuliper: that is for SELECT COUNT(*). This is not the same query as SELECT *. – gbn Jul 01 '11 at 14:44
  • true - but simply change it to select * - for anything with 'good' selectivity the index is still used as I would expect. Simple test: to what they have there: Add alter table ctest add c4 datetime; update ctest set c4='7/1/2011' where c1>5000 and c1<5200; CREATE NONCLUSTERED INDEX CTest_2 ON CTest (c4); SELECT * from CTest where c4='7/1/2011'; shows the index is still used. Curious in which cases a select * would change this behavior - everything I've tested shows it works as expected. – Adam Tuliper Jul 01 '11 at 16:59
  • Not saying it doesn't work at times as you mentioned - I just haven't heard this before so want to understand 'when' this could happen (because of select *, and not because of selectivity) – Adam Tuliper Jul 01 '11 at 16:59
  • When the index isn't very selective. And in your plan, does it have a key lookup? How expensive is this in the graphical query plan? – gbn Jul 01 '11 at 17:02
  • FYI, there is a difference between using the index for selection of rows, and using the index for retrieval of data (a coverage index). Select * does not affect using the index to narrow down the rows returned. But during column retrieval, it has to use the PK to access the actual set of rows. If you select col1, col2, and both are in the same index that is used for row selection, then there is no reason to do a table lookup. – Gerard ONeill Jul 12 '17 at 20:12