If you insist on using the hint at the table level, you need to be very careful about the syntax. Compare the following, both of which are *allowed* syntax, but with markedly different behavior (click to enlarge):
On the left, session 68 just used
FROM dbo.tablename NOLOCK - as you can see from the results, this was actually interpreted as a table alias, not a hint, and as a result, it took out shared and intent shared locks at the page and object level - which is not behaving the same way as the author likely expected. So please, always use the fully explicit form,
FROM dbo.tablename [AS alias] WITH (NOLOCK).
With SQL Sentry, our installed software solution, you can effectively monitor, diagnose, and optimize your entire database environment.
You can use a different isolation level to avoid the issues listed at the beginning of this article, while still preventing readers from blocking writers and vice-versa. Once again, Kendra comes to the rescue with a great post explaining
READ COMMITTED SNAPSHOT:
RCSI in particular is a great way to get the performance of
NOLOCK without sacrificing accuracy. Don't just go turn this feature on, though; there is no such thing as a free lunch. You'll want to note the following, and make sure that you have thoroughly tested all of these scenarios before making the change:
- Sch-S locks still need to be taken even under RCSI.
- Snapshot isolation levels use row versioning in tempdb, so you really need to test the impact there.
- RCSI can't use efficient allocation order scans; you will see range scans instead.
- Paul White (@SQL_Kiwi) has some great posts you should read in his blog series on isolation levels.
Since using the snapshot isolation levels is not, and should not be, a simple "switch and forget it" operation, you may want to look into other alternatives to
NOLOCK. Since this usually involves read-heavy queries, you could consider letting users read a copy of the data, and your choices are limited only by the edition of SQL Server you're using and how stale the data is allowed to be. Some options off the top of my head:
|Solution||Granularity||Currency of data||Edition requirements||Effort / maintenance|
|Database||Near real-time||Enterprise Edition||Medium|
|Database||Snapshot frequency||Enterprise Edition||High|
|Replication||Object||Near real-time||Most editions||High|
|Log shipping||Database||Log backup frequency||Any edition||Low|
|Object||Implementation dependent||Any edition||Medium|
In a previous life, I have implemented something similar to the application-level data caching approach, but I replicated data, on a schedule within to multiple SQL Server Express instances, each sitting on an application server. This way the application had relatively current data, but it was static and local, so no issues with concurrent access, no conflicts between readers and writers, and no network round-trips. You can read more about this solution here and here.
I do have some work invested in a post about a poor man's secondary involving log shipping; I will update this post when that one is published.
I am not vehemently against
NOLOCK - there are definitely use cases where you can "get away with it." I used it quite a bit earlier in my career, but looking back, mostly to avoid solving the real issue(s). Today I am just against it being used in every single query as an implicit rule. If you are using it everywhere, I hope that I have convinced you to reel that in a bit, and use it only in those scenarios where accuracy is not important - or at least where accuracy can be traded for other priorities. Better yet, use one of the alternatives I discuss above, though I do acknowledge that change takes time and sometimes a lot more.