SQL SERVER – NOLOCK with DMVs

Is it alright to use NOLOCK with DMVs? I was recently asked about this by a client during the Comprehensive Database Performance Health Check.

SQL SERVER - NOLOCK with DMVs NOLOCKwithDMVs-800x450

Well, here is a very simple and straightforward answer. You can use NOLOCK with DMVs and also not use it. I have done many different tests and I have not a much-found difference in the results and usages. If you are the one who finds usage of NOLOCK with DMVs comforting, go ahead and use it, I have nothing against you.

Lots of people think that they have to use the No Lock hint otherwise, the DMVs will be locked all the system operations will stop. Honestly, this does not have DMVs or DMFs work. From my extensive testing, I found that it really does not matter much. Even though I have a long-running query using DMVs and there is a hint provided, I was successfully able to do all the necessary operations.

Bottom Line: From my experience, I found that NOLOCK hints are not required when using DMVs. However, if you find comfort in using hint with DMVs, you can for sure use it and I have absolutely no issue with it.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL DMV, SQL Lock, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Get Last Restore Date
Next Post
Current Date Time in Python and SQL

Related Posts

Leave a Reply