I’ve been reading some blogs and editorials regarding the use of query hints; primarily NOLOCK. Many of the opinions written state that query hints should be used much less as query optimization improves, or other methods are developed replacing them.
The primary exception for using NOLOCK was in those situations where you have a reporting database, and there is no potential for corrupted data.
The key reason for not using NOLOCK was that you can get data that doesn’t really exist. That doesn’t just mean you can retrieve data that has not been committed. You can also get duplicate instance of a record during a page split.
One query hint that was received more friendly acceptance was READPAST. The READPAST query hint ignores records having a lock. Unlike NOLOCK, READPAST cannot return data that does not exist. However, it does not guarantee a complete set of data. There are cases when gathering only unlocked records is acceptable, and READPAST is a better option.
Instead of using query hints, most authors recommend putting efforts toward resolving the root problem instead of hiding it behind query hints. They suggest assuring appropriate indexes, normalization based upon data usage, statistics management, and like processes which allow the query optimizer to perform more effectively.
I remember one time designing a stored procedure in a development environment. It had a solid query plan. After deployment to production, the query plan changed dramatically. Using query hints I forced the plan I knew to be efficient. Later I found that updating statistics resolved the query plan selected by the optimizer. So, efforts made toward tuning the database would be superior, because it can be used for more than one query or procedure.
Cheers,
Ben