SQL Server NOLOCK Hint & other poor ideas.

Topics about Books » SQL Server NOLOCK Hint & other poor ideas.

Topics about Books » SQL Server NOLOCK Hint & other poor ideas. — Mon, 06 Apr 2009 04:11:55 GMT

PingBack from http://books.linkablez.info/2009/04/05/sql-server-nolock-hint-other-poor-ideas/

re: SQL Server NOLOCK Hint & other poor ideas.

Justin A — Mon, 06 Apr 2009 06:57:11 GMT

Great post David :) NOLOCKS (as a solution) have always been an embarassing way to 'solve' a deadlock problem .. which as you said, is just hiding the real problem. Time to read those links :)

>> Ensure your Many to Many tables use both foreign keys as a the unique composite key (Primary key), ie: an Identity Column as a Primary Key is not appropriate for these tables.

Can u please elaborate on this? maybe make a really quick blog post on this? I've always handled many-to-many tables like this.

Id INT PK Identity NOT NULL

TableA_Id INT FK NOT NULL

TableB_Id INT FK NOT NULL

I'm very curious to the reasons to making the two FK's the composite PK.

Cheers!

re: SQL Server NOLOCK Hint & other poor ideas.

Phaneendra — Tue, 07 Apr 2009 07:09:46 GMT

A very good post Dave. Having said this, I have a scenario as mentioned below:

Consider an order processing system, which has processing of an order as a transaction. The transaction is implemented at DAL layer. Since every transaction will have a status, we need to fetch the status id from status master table and associate it with the transaction records while processing the order. In such case, if we dont place nolock, then the status table gets locked until the transaction is committed and subsequently might lead to deadlock scenario when there is more load on the system.

I am curious to know if there is any alternative for this scenario with out using nolock hint.

Thanks in advance

Phani

re: SQL Server NOLOCK Hint & other poor ideas.

Chris Leonard — Tue, 07 Apr 2009 09:55:12 GMT

Good, solid basics. But the flip side is that telling a good SQL Server programmer not to hint is a lot like telling a C++ or C# programmer not to debug, and certainly to never use directives.

The fact of the matter is that the optimizer makes little mistakes all the time (on purpose, in fact, so it doesn't have to spend a huge amount of time optimizing) but some of its mistakes are HUGE. A good SQL developer, who understands the shape and distribution of their data, can frequently fix things.

One topic I'd like to see more good database blogs write about is plan stability. The single worst thing, other than something that can never be done efficiently in production, is something that occasionally goes inefficient in production. In these cases, when time is tight and the demand to fix something is immediate, a simple hint or two can often straighten out the optimizer and cause a plan to be optimal (or darned close) and absolutely stable. This is infinitely (and I do mean infinitely) preferable to a plan that is absolutely optimal 99% of the time it compiles but terrible 1% of the time. Plan stability should be a top-level priority of any very good SQL developer.

Sometimes I think our hint-phobia is rooted in a belief that we actually write code in the database. We do not; SQL issues specifications that the optimizer turns into code, and sometimes a hint can make that specification more explicit and complete.

I know this kind of thing makes the purists gag, but I'm a purist too. Did all the PhD work, relational algebra, relational calculus - the whole nine yards. And I did great at it. But I still don't see why we would ever want to take a tool, like hints, away from programmers, instead of teaching them to use them well.

So I like your post, but I especially like the energy you put into trying to develop a way to use hints wisely. I'm not so sure we need so many posts, however, telling us to use them "almost never." A lot of high-profile companies would go out of business without them, because the optimizer is not yet good enough - and as we continue to add features to the kernel, the optimizer probably will never be good enough.

Just food for thought / my 0.02.

Cheers,

Chris

re: SQL Server NOLOCK Hint & other poor ideas.

Tony Moe — Mon, 04 May 2009 19:29:29 GMT

Great post Dave.

However, I have to comment on a few points:

1. Starting SQL Server 2005 with the introduction the snapshot isolation, there is no justification whatsoever to use NOLOCK.

However, before that, NOLOCK was the only viable solution. A SELECT without NOLOCK can be blocked forever if the table being read is constantly being updated.

READPAST is worse than NOLOCK because it skips the locked data.

2. Blindly adding NOLOCK to all SELECT statements is a very bad practice. If a result of the SELECT is being used in a sebsequent update, UPDLOCK should be used.

3. My experience with using SQL Server without query hint has been very discouraging. I can hardly imagine a multi-user real world application that can run without ROWLOCK!

Cheers!