One of the questions that I get over and over again is about where SQL Server stores the data that is returned by the various Dynamic Management Views (DMVs) and Dynamic Management Functions (DMFs).
Many people think that this type of data is stored somewhere within a system database like the master database. But this is not really the case. The various system databases (master & msdb) store configuration data, but that’s it. So the question is now: where is the data of the DMVs/DMFs really stored?
The answer to this question is quite simple: this kind of data is stored nowhere physically within a database! Yes, you read that correctly: the data is stored nowhere physically on disk. The data is read and returned directly from the RAM. When you access a DMV/DMF, SQL Server returns data that is stored directly within the process space of sqlservr.exe.
The majority of SQL Server is written in C++ and with some specific assembly routines (at least I assume so…) and consists of hundreds (or even thousands) of various C++ classes. And when you now access a DMV/DMF, SQL Server just returns the data that is stored in specific C++ classes. The DMVs/DMFs are mainly a front end with which you can query in-process data structures in a SQL Server specific way.
This sounds quite easy and straightforward, but is quite hard to implement. First of all you have to make these data structures as efficient as possible for reading and writing, and you have to implement them in a thread-safe way. And because the data is not persisted physically anywhere, you will lose all this data when you restart SQL Server.
You have already heard very often that you should never ever restart SQL Server on a regular basis, because it introduces a huge number of side-effects. I don’t want to talk about all of these side-effects today, but one side-effect is that you lose the data that is returned from the DMVs/DMFs. This makes sense, because you read this data directly from the process space of sqlservr.exe. You have seen that behavior: you restart SQL Server, and afterwards your Wait Statistics are simply empty!
It’s a myth that SQL Server physically persists the data that is returned by the various DMVs/DMFs. When you query a DMV/DMF within SQL Server, you only get back data that is stored in a specific data structure within the process space of sqlservr.exe. Therefore you also have to keep in mind that you will lose that data for performance troubleshooting as soon as you perform a SQL Server restart (or even a Cluster Failover).
Thanks for your time,