SQL SERVER - NOLOCK with DMVs - SQL Authority with Pinal Dave
source link: https://blog.sqlauthority.com/2021/09/14/sql-server-nolock-with-dmvs/?utm_campaign=sql-server-nolock-with-dmvs
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
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.
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)
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK