CODEWITHSUNDEEP
sql-server
mechanicum
mechanicum

Reputation: 709

multiple queries running at the same time WITH(NOLOCK)

Considering a highly active remote database in constant use; Would 2 or perhaps more concurrently running queries returning around 30m records but using WITH(NOLOCK) in the query make the system unusable?

Highly Active: Around 200 users with varying queries ranging from 1 record queries to maximum as above.

Constant: 18/7

Unusable: 5-15 minute delays. External apps or users not being able to reach the DB to query.

Upvotes: 0

Views: 1309

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294407

You need to investigate your downtime 'unusable' as appropiate. Guessing will not work. There are many situations that can make a server 'unusable', like:

  • database growth
  • log growth
  • core dump
  • scheduler exhaustion
  • many more

Other times the server can be simply 'slow' and appear unresponsive. 200 queries scanning nilly-willy 30 million rows table can lead to scheduler exhaustion. You will need to properly investigate. Waits and Queues is a good methodology to use (for instance it would correctly identify scheduler exhaustion mentioned above).

As for NOLOCK: there is never a valid reason to use NOLOCK. Your users see incorrect data right now. Consider using row versioning instead.

Upvotes: 1

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58491

If you mean

make the system less unusable for other users while selecting 30m records

no, the WITH(NOLOCK) has no effect whatsoever.

Note that selecting 30m records in by itself is likely going to be a very I/O sensitive operation so I wouldn't go willy nilly in selecting that much records but the WITH(NOLOCK) for all intents and purposes only affects the performance of the one doing the select.

Upvotes: 1

Related Questions