SQL Server – Use Of NOLOCK / ROWLOCK

Hi,
While we are working to retrieve huge data or sum(*) data with (NOLOCK) will give better performance. After some quick searching, I found a great page on Sql-Server-Performance.com. Apparently SQL server puts a locking mechanism around all data access and manipulation to prevent things like dirty reads and the reading of uncommitted data. I was totally not aware of this - SQL server just worked and that was good.
Apparently though, these locks (as with most locking) comes with a perforance hit. This is good though as it ensures the integrity of your data. However, there are times when you just don't care. Some tables, especially look up tables, are not updated often (if ever) and locking around these brings on unnecessary overhead.
ROWLOCKUse row-level locks when reading or modifying data.
PAGLOCKUse page-level locks when reading or modifying data.
TABLOCKUse a table lock when reading or modifying data.
DBLOCKUse a database lock when reading or modifying data.
UPDLOCKUPDLOCK reads data without blocking other readers, and update it later with the assurance that the data has not changed since last read.XLOCKUse exclusive locks instead of shared locks while reading a table, and use hold locks until the end of the statement or transaction.
HOLDLOCKUse a hold lock to hold a lock until completion of the transaction, instead of releasing the lock as soon as the required table, row, or data page is no longer required.
NOLOCKThis does not lock any object. This is the default for SELECT operations. It does not apply to INSERT, UPDATE, and DELETE statements.

Using the SQL directives NOLOCK and ROWLOCK can circumvent this SQL Server's locking mechanism and speed up queries. Here, I demonstrate using it on a SQL Join query in which I get Blog entry information:
Examples:SELECT OrderIDFROM Orders (WITH ROWLOCK)WHERE OrderID BETWEEN 100AND 2000
UPDATE Products (WITH NOLOCK)SET ProductCat = 'Machine'WHERE ProductSubCat = 'Mac'

· SELECT
· b.id,
· b.name,
· ( t.id ) AS tag_id,
· ( t.name ) AS tag_name
· FROM
· blog_entry b
· INNER JOIN
· blog_entry_tag_jn btjn (NOLOCK)
· ON
· b.id = btjn.blog_entry_id
· INNER JOIN
· tag t (NOLOCK)
· ON
· btjn.tag_id = t.id
Notice that by using the NOLOCK directive on the blog_entry_tag_jn and the tag Tables. The Tag table pretty much never gets updated and the blog_entry_tag_jn (joining of entries to tags) table gets updated ONLY when add or update a blog entry. Due to the low frequency of updates, the requirement for locking on these tables is (next to) pointless. By using the NOLOCK directive I am asking SQL to ignore all locking mechanism surroundings those tables and proceed directly to data-retrieval. Theoretically, this should provide a small performance gain
Thanks
Rav’s.