SQL Server Lock (Blocking)

Dear SQL Server Experts,

I am basically from Oracle DBA background and I don't have much experience in SQL Server. I hope I will get some experts advise and comments on my SQL Server DB issue :)ert

We have MS CRM DB with the size of 350 GB. We're experiencing DB locks (blocking) issue on production SQL DB. As per MS article "“Blocking is an unavoidable characteristic of any relational database management system (RDBMS) with lock-based concurrency”. Having blockings detected is not by itself enough and sufficient to decide the problem"".

My Questions:
1. what was the reason for blocking?
2.any advice on optimizing that particular cause?
3. Is there any chance of more blocking  if I use more NOLCOK DB hint in update or insert or select operations ?
fatora dbaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HuaMin ChenProblem resolverCommented:
Vitor MontalvãoMSSQL Senior EngineerCommented:
  1. Concurrency queries running slowly on some objects
  2. Improve query performance. If you come from Oracle, you should already know how important is to have the proper indexes created
  3. Locking hints should be used as last resource unless you don't mind to work with dirty data

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fatora dbaAuthor Commented:
In short, I have two questions:
1.      Which way results set would be dirty data: select with NOLOCK hint or select without NOLOCK hint?
2.      Which way one select statement can block other transactions: select with NOLOCK hint or select without NOLOCK hint?

If I understand correctly, I assume select with NOLOCK is the one that might give dirty results, which is something we don’t want to have, and again select with NOLOCK might itself cause more blockings for other transactions, which is also something we don’t want to have.  The downside of select without NOLOCK, which is the default I guess, is that select statement itself might be blocked if another transaction is locking the objects we are trying to read, in such a case, would it be a good/recommended approach of we point the jobs that have heavy reading towards the read-only node of the DB cluster? What would be the expected impacts?
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

HuaMin ChenProblem resolverCommented:
Hi,
You can use Nolock to select statements, like

SELECT * FROM ContactPeople WITH (NOLOCK) WHERE ContactPeopleID < 20

Open in new window


You can also use it to Update statements, like

UPDATE ContactPeople with (NOLOCK) SET Suffix = 'B' WHERE ContactPeopleID < 20

Open in new window

Scott PletcherSenior DBACommented:
I'm going to number your last two qs, on NOLOCK, as 4 and 5 rather than 1 and 2 so that I can address all your qs:

1) Reason for blocking is to preserve data base integrity (accuracy).  It's to make sure that one update doesn't overwrite another update and that you don't read data that is no longer accurate.  For example, if someone updates the value in a row from 7 to 9, but that update has not committed yet, the real value of the column is unsure, so SQL by default blocks access to that row until the transaction is committed (finalized), and the value becomes 9, or the transaction is rolled back, and the value remains at 7.
All relational dbs have this type of mechanism.

2) Would need more details on the indexes and usage stats from SQL to give specific tuning recommendations.

3) NO.  Nolock will always result in less blocking, never more.

4) With NOLOCK can yield "dirty data".  Take my example from #1 above.  NOLOCK would ignore that the value has been changed from 7 to 9 -- but not committed yet -- and return the value 7, since that is the last value that was committed.  NOLOCK does what it sounds like it does: does not do locking, but that means it can read "old" data.

5) A statement with NOLOCK will never block other data transactions, a statement without NOLOCK can.
NOLOCK should never be used on a DELETE or INSERT or UPDATE statements because those statement always do locks, they must do locks.
Vitor MontalvãoMSSQL Senior EngineerCommented:
I just don't agree with Scott's statement #5 and that's because I already saw SELECT with NOLOCK hint blocking other processes. That can really happens when working with a large set of data. I went to search if somebody else had the same issue and found this one: https://bertwagner.com/2017/10/10/how-nolock-will-block-your-queries/

Also a good article to read and it also states the existence of locks in SELECT with the NOLOCK hint: https://www.mssqltips.com/sqlservertip/2470/understanding-the-sql-server-nolock-hint/
Scott PletcherSenior DBACommented:
Please re-read my #5 more closely.  I specified "never block other data transactions".  Of course schema (meta-data) mods are not allowed while the table is being read.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Recommendation to close this question by accepting the above comments as solution.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.