DMV Usage Caution

when SQL Server executes query, the information about the activity is automatically recorded and stored in memory, which is called the DMV. The DMV is saved at the SQL Server instance level. The DMV uses memory as a medium to store information, does not require IO, gets fast data, does not strain the server, and the DMV is stored directly in the server's memory to reflect the latest state of the server in a timely and accurate manner. There are two points to note when using the DMV:

1. Determine when data is saved

Since memory is volatile, once SQL Server restarts, the information stored by the DMV is reset. When using a DMV, you first need to determine how much time is stored in memory to determine the availability of the DMV data. If SQL Server is only running for a short period of time, then it is not appropriate to make statistics and analysis of the DMV data, which is not a sample of the data from the SQL Server instance real workload. The longer SQL Server runs, the more information is stored in the DMV (of course, the DMV is very small and does not stress the memory), and the more accurate the DMV analysis is.

2,DMV use of limited memory

The amount of memory the DMV can use is limited, which allows the DMV to return data that is limited. If SQL Server is running for a long time, SQL Server engine will overwrite some old data from the DMV.

Recommended Documents:

https://www.simple-talk.com/sql/database-administration/baselining-with-sql-server-dynamic-management-views/

DMV Usage Caution