fatora dba
asked on
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 ?
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 ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
Hi,
You can use Nolock to select statements, like
You can also use it to Update statements, like
You can use Nolock to select statements, like
SELECT * FROM ContactPeople WITH (NOLOCK) WHERE ContactPeopleID < 20You can also use it to Update statements, like
UPDATE ContactPeople with (NOLOCK) SET Suffix = 'B' WHERE ContactPeopleID < 20SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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/
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/
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.
Recommendation to close this question by accepting the above comments as solution.
https://www.mssqltips.com/sqlservertip/3172/avoid-using-nolock-on-sql-server-update-and-delete-statements/