Where SQL Server does store the data of DMVs?

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 storage location of the various DMVs/DMFs

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!

Summary

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,

-Klaus

2 thoughts on “Where SQL Server does store the data of DMVs?”

  1. At least some of the DMFs return data based on physical structures. For instance, principals, index stats, partition properties, database file information, etc. is clearly dependent on persisted physical structures in the database/server. I realize that much of the information is calculated on the fly (e.g. fragmentation) but it is still based on persisted physical objects.

  2. I am not sure we should ever rely upon any DM being physically persisted.

    For example, a cached plan has a dbid. If you drop that db and check sys.databases, you will see it has met the big bit bucket in the sky, but has it? If you check procedure cache (and assuming your plan has not been aged out as the LRU), you should see and entry for that dbid still hanging around. You might even find sp_MSforeachdb still attempting to reference that dropped db, thus raising msg 911.

    I suspect if you run DBCC DBTABLE (i.e. what’s kept in memory) on that dbid, you will still find a ‘valid’ entry for the dropped database (even though sys.databases ‘states’ the opposite).

    I assume the combination of a need for speed and a ref count explains this ‘inconsistent’ behavior.

Leave a Reply to Ray H Cancel Reply

Your email address will not be published. Required fields are marked *