This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Are you SQL Developer or SQL DBA? Whatever you are, you must know about the SQL Server DMVs (Dynamic Management Views).
Last week, I was taking interview for SQL DBAs. I had asked few questions like:
What do you know about the SQL DMVs?
How can we effectively use it?
Do you know, Where does SQL Server stores the DMVs data?
I had found most of the SQL DBA given the wrong answer for the third question, that is about the storage of DMVs.
Most of the time, I got one type of answer like SQL Server stores DMVs data in master & msdb database.
This is an absolutely wrong answer.
The correct answer is: SQL Server doesn’t store DMV/DMF related data into the physical disk, but it stores directly into the memory. You can read this data directly from the process space of sqlservr.exe.
Because of this reason, most of the DBAs are suggesting that you should not restart the SQL Server. It will drive a SQL Server to poor performance.
Because it clears all query cache, buffer cache and wait type statistics.
