Sql-server – Should I use SQL Server DMVs with NOLOCK

dmvperformanceperformance-tuningsql server

I am trying to monitor live performance data and usage, which cannot be tracked with PerformanceMonitor. What are the consequences of reading DMVs in a production live OLTP database? : Eg: sys.dm_tran_locks, sys.dm_os_waiting_tasks, sys.dm_os_performance_counters, sys.dm_exec_connections, sys.dm_io_virtual_file_stats, sys.dm_exec_sql_text,etc

Should I just utilize WITH (NOLOCK) when querying Dmvs? Would this resolve many of the resource issues? Is there anything such as dirty read rollbacks in DMVs. I know it can exist in applications tables eg where someone submits an order then cancels, etc. Additionally, with NOLOCK, is there higher chance query will continue forever in high volume environment since I am not locking rows, pages, and things will continue be added into DMV TableView?

Thank you,

Best Answer

What are the consequences of reading DMVs in a production live OLTP database?

Negligible as far as my experience goes. DMV's as the name suggests are basically views or functions ( like sys.dm_db_index_physical_stats ) which get information from various caches and system tables. DMV's are very light and read data from caches. They do not cause blocking and depending on amount of data you need to fetch they very very quick. The point here is always only query data from caches which you actually need.

You may use NOLOCK hint in your DMV query you would see some people using it but frankly I do not see much of need to use it with DMV's. Its function though still remains same. So when you use NOLOCK hint you say to SQL Server that give me the data I am asking no matter what is state of the data is. It may have been changed and yet not committed so you get "dirty" data. NOLOCK hint avoids select queries taking shared lock while reading data. The drawbacks are many

Additionally, with NOLOCK, is there higher chance query will continue forever in high volume environment since I am not locking rows, pages, and things will continue be added into DMV TableView?

Query will only continue for duration until it finishes its task, unless you run a never ending loop. The NOLOCK only ensures that query is minimally blocked and also note that when using NOLOCK if required other locks like schema stability are taken

Related Solutions

Sql-server – force a user to use WITH NOLOCK

Seems the nearest you can get without using SPs is using SET READ_COMMITTED_SNAPSHOT, which will affect all users of the database.

The READ_COMMITTED_SNAPSHOT database option controls if the isolation level READ_COMMITTED is implemented like their counterparts in PostgreSQL and FirebirdSQL (statement level snapshot of records) or using locking. Read more about that in the documentation:

Locking and Row Versioning

You commented in response to another answer:

There is no control application in the scenario in mind. It's direct SSMS against SQL2008R2 Standard Edition

You have to make sure that all that installs are set to Read Uncommitted in Tools/Options/Query Execution/SQL Server/Advanced. I'm not an network SysAdmin myself, so I don't know if a Policy can solve it.

Sql-server – Why is there a difference between these transaction DMVs in SQL Server 2008 R2

It appears that sys.dm_tran_session_transactions does not include auto-commit transactions:

-- No result
SELECT 
    session_id, 
    transaction_id
FROM sys.dm_tran_session_transactions;

Using an explicit transaction does return results:

BEGIN TRANSACTION;

    -- Row returned    
    SELECT 
        session_id, 
        transaction_id
    FROM sys.dm_tran_session_transactions;

ROLLBACK TRANSACTION;

The DMVs are views on internal structures, and the documentation is not often as comprehensive as in other areas. Some of that may be because it would be inconvenient to go through a complete deprecation cycle each time a change in DMV behaviour occurs, but it's likely just an oversight in this case. You could report the documentation deficiency on Connect.

Adam Machanic found all sorts of odd little behaviours with the DMVs while writing his sp_WhoIsActive tool. If it suits your purposes, you could use that rather than trying to write your own monitoring queries.

Best Answer

Related Solutions

Sql-server – force a user to use WITH NOLOCK

Sql-server – Why is there a difference between these transaction DMVs in SQL Server 2008 R2

Related Question