Sunday, October 18, 2009

SQL performance WITH (NOLOCK)

A few months ago, we started UAT for our application development and found out that some statistical SRS reports were running very slow. After debugging and done performance tuning steps, we noticed that those reports were running very slow when a lot of concurrent users accessing the same tables and/or views that the reports were referencing.

After surfing through Google, we found out a hint called NOLOCK.
SELECT au_lname FROM authors WITH (NOLOCK)
NOLOCK does not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement.
We changed most of the SELECT statements with NOLOCK and performance of statistical reports became very must faster. Query execution time which took around 45 minutes previously became around 6 minutes plus.

But there were some pros and cons of using NOLOCK in SELECT query. Below is the comment made by Lumbago on SQL Team forum.
NOLOCK will cause your selects to read dirty data and I have prevoiusly said that I belive this is perfectly acceptable in over 80% of the queries I have worked with over the years, and I still belive this is true. It will reduce locking in your database and increase performance of your queries.

HOWEVER, it is absolutely essential that you know how it works and you need to think well through where to use it and not. It could cause severe inconsistencies in your database if not used properly and you should never use it in any insert/update operations. The general advice is that if you're not 100% sure it's ok to read the dirty data, then don't use NOLOCK.

Reference:
Lumbago
MSDN: Locking Hints
SQL Server NOLOCK / ROWLOCK Directives To Improve Performance

No comments:

Post a Comment