The Effect of NOLOCK on Performance
-
I agree this is a great discussion. I never said I was against using NOLOCK rather the article does make the reader aware enough about what NOLOCK actually means to you application.
The posts here have been great in that they highlight users that are using NOLOCK but know what the implications are.
ps We use NOLOCK all over our read only DB layer that provides the search facility to the web sites. This is generally due to the amount of data searching processes (full text is really bad) and so the impact on concurrency.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons -
I am surprised no one mentioned Oracle's locking model. Oracle, from its very inception, prevented 'dirty reads'. Simply stated, all (and I mean all) Oracle versions were able to do that by separating "Undo" from "Redo". (Undo is the data required to undo changes to a data block or page. Redo is the data required to protect changes - your change log)
There is a major difference in the way Redo and Undo is managed in Oracle as compared to SS2K. Redo information is continually written out to the current online redo log file in a cyclic fashion, with a minimum of at least two redo log files that serve this function for the entire database as compared to the single log file required per database in SS2K. The Undo information, on the other hand is written to structures internal to the database called Undo segments (previously known as Rollback segments). The writes to these segments is again protected via redo, so instance recovery is not an issue. Hence, unlike SS2K, a long running transaction (one that is not implicit but has a BEGIN TRANSACTION and is yet to get to an implicit END TRANSACTION via a COMMIT) does not require extending the Transaction log file. The Undo segment is retained until a COMMIT is performed by the requesting section. This architecture lends itself to providing highly concurrent online system without having to consider the possibility of deadlocks or artificially forcing short transactions.
Another major difference is the way locking is done, again due to the separation of Redo and Undo data. Although both RDBMS lock at the lowest level possible (i.e. row level) and support shared and exclusive locks, Oracle reads are not blocked by writes (and vice versa). In SS2K, writes can block reads unless 'dirty reads' that allow uncommitted data to be read by another process is allowed. In Oracle based applications, the designer does not need to be catered for this problem, thus allowing Oracle to supports a higher concurrency level for online applications.
Whether we like it or not, I believe this is one major reason Oracle does the really big 'uns.
-
Thats why SQL 2005 has 2 new isolation levels to allow for readers to read consistent committed data unblocked.
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons -
Ummm... did you read the discussion threads? There has been quite a bit of discussion of the Read Committed Snapshot (RCS) isolation level that is new with 2005. This new isolation level allows SQL Server to act in much the same way Oracle does.
Now that said, Oracle's locking model, despite the marketing hype, is not the "best" and is not THE reason that "Oracle does the really big 'uns." If you don't believe that there is significant overhead involved in providing that kind of transaction isolation mechanism, think again. However, it does allow for greater concurrency in many cases. Historically both IBM and MS have adopted the more pessimistic locking model, while Oracle's is more optimistic.
"Hence, unlike SS2K, a long running transaction (one that is not implicit but has a BEGIN TRANSACTION and is yet to get to an implicit END TRANSACTION via a COMMIT) does not require extending the Transaction log file." True, but it may require extending the undo segment, either that or you get the famous stale undo segment error. There is, or at least was (I haven't worked extensively with 10g), quite a bit of administratve overhead involved in managing rollback segments. The point being there are tradeoffs involved...
Now MS gives you the choice (something which neither Oracle nor IBM provide). That has been my recommendation to those who want to use NOLOCK, investigate the RCS isolation level and see if that doesn't do what you need.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
The SQLCAT team have also joined in the debate upon the use of NOLOCK
Makes interesting reading and adds some more information.
My view of the "Oracle" locking is that it is not ansi ( I understand ) and can give different users different views of the data, e.g. an inconsistant database view. It was explained that the new locking modes in SQL2005 were to enable the transation from Oracle to SQL Server not encourage the deployment of these modes. But still, you take your pick and live with it I guess!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Sorry for the delayed response, I haven't been getting my email notifications for a few days...
Anyway, that is a great link thanks.
I think you are correct in that the SQL Standard calls for pessimistic locking. However your concerns about inconsistency, I don't think are really founded. Using the Read Committed Snapshot isolation each transaction is presented with its own "picture" of the datbase as it was when the transaction was started. That "picture" lasts only as long as the transaction does. So it is possible for transaction A to undo the changes made by transaction B etc... However, this same situation exists in the Read Committed model, except that transaction A will be put into a waiting state until B finishes.
If you need the more isolated levels such as Repeatable Read or Serializable, then for any transactions that use them, the snapshots are not available.
I have never heard that MS recommended against deploying Snapshot isolation. That would be interesting and puzzling too, do you have any references? There is no reason that I can think of to not use Snapshot isolation where it is appropriate. But like always, you should always understand where the rocks and rapids are before jumping into the river...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
DCPeterson,
I'm having a shedload of issues around locking up of the database when large reports are running on the system - they seem to be causing processes to wait for transactions and they themselves lock up when transactions are active. These reports (CR XI R2) cover the entire DB's tables - are you aware of a way of setting the isolation level of CR low enough so that it never waits or causes to wait for anything. There are a large number of reasons why we don't care if the data in these reports is correct to the second.
Thanks
Richard
-
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff is correct, but doing this carries the same risks as using nolock.
If you are using SQL 2005 then Read Committed Snapshot isolation level might be an appropriate choice.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Richard - sounds like you have parallelism issues, something discussed many times on this site. The most obvious answer to reporting issues is to report from a read only database - turns all locking off and solves most problems. A log shipped database can provide this function unless you really, really , really must have up to the last second data - then a combination of read only and selective replication may be the answer.
When I read so many of the posts about reports causing blocking I just wonder why it doesn't seem obvious to deploy to a reporting server ?
As to the snapshot isolation level - it was introduced to enable easy migration from Oracle, at most of the events I went to during Beta 2005 it was stated that this was the main reason and as it wasn't really ansi you should stick to sql isolation levels - you also need to be aware of the overhead of running snapshot, which can be quite high - just think of how your memory pools and cache handle the snapshots for instance.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
About read-only report database - already underway - as you say very obvious - just thought there might be a quick interim fix. A large number of the reports do have to be bang up to date (about 40%) and due to the nature of the business they do not restict themselves to three table joins with two records - plus the client insists on using CR parameters which seem to have the effect of doing the report query at least one for each dropdown etc
It looks like somewhere under the hood of CR it should be possible buy version 10 is the nearest I can find where it is possible.
I'll let you know when we find a definitive way around this one.
-
Ok... confusion has set in, Richard... in your latest post, you say "A large number of the reports do have to be bang up to date" and in a previous post, you say "There are a large number of reasons why we don't care if the data in these reports is correct to the second"
Which is it, really?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
no idea if the situation would be similar, but...
I recently ran into some problems with an app that used parameterized queries generated by some OO middleware. This middleware was pretty flexible, but it was creating the parameters as nvarchars while all the fields we use were just varchars.
SQL appeared to be creating/holding a bunch of locks while it converted every value in the table or index to unicode, THEN do the comparison. So basically it was having to scan through & process every row vs. doing a simple index seek.
I wonder if you could feed some of those dropdowns via. separate views or indexed views.
-
Iam working on 8 terabytes of data, where i found the vital role of NOLOCK.Definetly it improves the performance
-
By "up to the second" I just meant that the reports are almost always based on data just saved - i.e. the transaction is complete before the report is run so uncommitted data is irrelevant to these outputs.
I began the arduous task of applying the WITH (READUNCOMMITTED) hint to all the views used by reports in the system (all 2500+ of them) and started seeing immediate performance gains. Overall the servers cache seems to be coping better and the CPU and IO have dropped considerably. The system also has an OLE component that talks to and populates word documents which we took another look at, changed the connection it uses to read uncommitted and explicitly made the recordsets read-only and it's now running five times faster and taking up a half of the server resources it used to. We also reviewed a lot of the indexes and found a few missing based on the current usage. The application as a whole uses transactions sparingly but we also discovered a few that had been misplaced around batches of work and changed their granularity - it's been a hell of a week.
Overall things are back to being operational performant but it's still driving me crazy that I can't tell Crippled Reports to configure it's connections as NOLOCK or READUNCOMMITTED - how hard can it be
Viewing 15 posts - 61 through 75 (of 88 total)
You must be logged in to reply to this topic. Login to reply