May 20, 2010 at 8:09 am
My friend advised me to add no lock to statements because I query the database mostly at the same time in 500ms intervals.
But he did not explain why.
And should I use it on insert statements?
Also I only query the last entry of the database, so SQL loads more data than needed in the RAM, is there away round that?
SELECT Max(SMAbanddataBid2) FROM DBVaskVbid
WITH (NOLOCK)
WHERE BarStamp =(SELECT MAX(BarStamp)
FROM DBVaskVbid)
May 20, 2010 at 8:52 am
NOLOCK is what is known as dirty reads. It prevents shared locks from being placed on data. This can speed up access. BUT, it also means that you can get uncommitted data, say "Tulsa" when it used to read "Broken Arrow." AND, it gets worse, because of pages splits, etc., you can get extra rows or miss rows of data because of NOLOCK.
So you need to be very careful abou the use of NOLOCK and understand what the implications of it are.
BTW, you can't apply NOLOCK to an INSERT/UPDATE/DELETE because locking is absolutely a part of performing those operations, no choice.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 20, 2010 at 9:06 am
Thank you for thank simple explanation, I understand it now.
What minimizing the RAM usage?
May 20, 2010 at 9:07 am
See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2010 at 9:29 am
I've a similar follow up question to this. I know that if you use NOLOCK on a query, you will be reading uncommitted data, which also means that your query will not be waiting for data to be committed before it can access it.
If I interpret that correctly, it means that if there is an update happening on a record or batch of records, and my SELECT query is attempting to access those records, then it will have to wait.
However, is the query analyzer smart enough to know that the query it is executing will not need to access the rows being updated? For example, if I have a table with an identity primary key, and I do a query saying "give me the records between ID 50 and 100", and an update statement is running against records 400-500, will my query have to wait, or will it know that those records are not being updated?
What about if the query is not quite as simple as that? If I had an update which was running against all records that had been modified in the past day, for example, and I had a seperate query which was running against all records that were from Canada, would the SELECT query have to wait for the update to run, or would it only wait if it tried to access a record being updated?
And, one more question. I know that the NOLOCK means that if data is locked by another statement, it will not need to wait for the lock to be released before accessing it. And, as far as I know, locks are only acquired in the case of UPDATE/DELETE statements, as well as SELECT statements which explicitly specify to acquire a lock.
If I write a SELECT statement without specifying NOLOCK, will that statement only be delayed if another query is locking the records, or could it be delayed if another SELECT statement without NOLOCK is also running?
May 20, 2010 at 10:59 am
Traditionally it's considered bad form to hijack someone elses question. Also, when you do this, the only people that are aware of the new question are those that are already subscribing. That means you'll get fewer people looking at your question. Just so you know.
kramaswamy (5/20/2010)
I've a similar follow up question to this. I know that if you use NOLOCK on a query, you will be reading uncommitted data, which also means that your query will not be waiting for data to be committed before it can access it.If I interpret that correctly, it means that if there is an update happening on a record or batch of records, and my SELECT query is attempting to access those records, then it will have to wait.
No, it won't wait. It'll take the records it can, even though that data is in an uncommitted transaction.
However, is the query analyzer smart enough to know that the query it is executing will not need to access the rows being updated? For example, if I have a table with an identity primary key, and I do a query saying "give me the records between ID 50 and 100", and an update statement is running against records 400-500, will my query have to wait, or will it know that those records are not being updated?
Query analyzer isn't involved. It's the storage engine that manages transactions.
And no, if you're accessing completely different sets of data, you don't even need no lock. There is no chance for contention there.
What about if the query is not quite as simple as that? If I had an update which was running against all records that had been modified in the past day, for example, and I had a seperate query which was running against all records that were from Canada, would the SELECT query have to wait for the update to run, or would it only wait if it tried to access a record being updated?
Short answer, it depends, but nolock would make it so you could see that data, even though it had exclusive locks on it.
And, one more question. I know that the NOLOCK means that if data is locked by another statement, it will not need to wait for the lock to be released before accessing it. And, as far as I know, locks are only acquired in the case of UPDATE/DELETE statements, as well as SELECT statements which explicitly specify to acquire a lock.
If I write a SELECT statement without specifying NOLOCK, will that statement only be delayed if another query is locking the records, or could it be delayed if another SELECT statement without NOLOCK is also running?
That's a shared lock and any number of shared locks (with limits on memory) can be taken out and they don't affect other shared locks. But, shared locks will prevent exclusive locks and this is frequently why people try to use NOLOCK or READ_UNCOMMITTED connections.
Just understand, in a pretty healthy majority of circumstances, using NOLOCK is a bad idea and can lead to problems.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 20, 2010 at 11:22 am
Grant Fritchey (5/20/2010)
Just understand, in a pretty healthy majority of circumstances, using NOLOCK is a bad idea and can lead to problems.
There are rare occasions where it may be acceptable to use NOLOCK. Just understand that it is bad form as Grant has said. The use of this hint can create some problems with dirty reads - is it worth it?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply