Dynamic Managment Views life time

Question

Hi I am looking at using the data in the SQL Dynamic Management views for performance tracking but the question I have that I can't find an answer to is how long does that data last?

My assumption is that there is a size limit on the log like with Windows events and that is what governs the amount of data that is available is that correct?

Answer 1

DMVs are reset every SQL Service restart.

If you are going to keep them for time, you need to create a process to extract them to new tables on a schedule.

Answer 2

so if I run sys.dm_exec_query_stats I get 10471 results that cover the last 4 hours and the last reboot would have been on the 21st of August.
so there must be something else that limiting the results?

Answer 3

The question is not really correctly posed. The DMV may look like tables, but they don't get data from any data store like a table, but they get data from internal structures in the memory of SQL Server.

If we take the actual example of sys.dm_exec_query_stats, that DMV reflects the plan cache, which means that it is subject to the rules of the plan cache. That is, if a plan falls out of the cache for some reason, for instance, there is a schema change to a table in the qurey, this means that it falls out of sys.dm_exec_query_stats.

The exact pattern will be different from DMV to DMV, all depending on what they are tracking.

As Tom said, they will always be reset on server restarts, since they do not hold persistent data. Some DMVs can also be cleared with DBCC SQLPERF.

Answer 4

Hi @Mark Dagley ,

Analysis Services Dynamic Management Views (DMVs) are queries that return information about model objects, server operations, and server health. The query, based on SQL, is an interface to schema rowsets. Schema rowsets are predescribed tables that contain information about Analysis Services objects and server state, including database schema, active sessions, connections, commands, and jobs that are executing on the server.
sys.dm_exec_query_stats, this view holds detailed information about all current execution plans, such as how much CPU is used by a particular execution plan, etc. Because this view keeps a detailed record of the number of compilation attempts, total CPU resources used, number of executions, etc., it can be a great tool for optimising the CPU of the DB server.
Since this view is dynamic, it may not always be accurate, or a certain execution plan may have been recompiled at the time of the query to get deviating information, etc. Also, for the ones that take up the most resources in sys.dm_exec_query_stats, it is not necessarily a performance problem, but it is important to observe both the number of executions and IO reads and writes, etc., while for the ones that are executed too frequently, it is important to Consider adding caching to the program; this system should not be used for emergency optimisation, but should be used as an important reference indicator for everyday optimisation.

best regards
Niko

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.