Performance impact of querying SQL server DMVs

Question

Is there any performance impact on a busy production SQL Server 2008 when I query DMVs such as sys.dm_exec_query_stats, sys.dm_tran_locks, or sys.dm_db_index_physical_stats ?

Thanks

Answer 1

Each one has a different impact. The impact depends on how many entries are returned (which often implies how many internal data structures have to be traversed) and some DMvs are inherently more performance sensitive than others (eg. sys.dm_tran_locks will have bigger impact that sys.dm_exec_query_stats simply because the lock data structures are so much hotter than the query cache). Some DMvs like sys.dm_db_index_physical_stats imply on-disk data scans and they will be extreamly expensive, comparable to a full table scan.