If you have been keeping up with this Dynamic Management Object series, you will have noticed that all of the previous DMO’s we have reviewed have come from the ‘exec’ namespace. Well today we are going to begin learning about some of the DMO’s in the ‘os’ namespace. The first one we will review is the sys.dm_os_sys_info DMV. This DMV returns information about the Operating System (OS) that SQL Server is running on and the resources that are available to SQL.
To help illustrate the power of this DMV, I will execute it against my Training instance containing my AdventureWorks2012 database.
SELECT * FROM sys.dm_os_sys_info
As you can see, there is a wealth of OS level information that is returned by this DMV. Since there are so many columns returned, I wont go into detail about each and every one of them, but I will touch on a few of them briefly.
Two columns I find useful when I am trying learn more about my SQL Server systems, maybe after taking over a new system or starting a new job, is the CPU_Count and Hyperthread_Ratio columns. These columns represent the number of logical CPU’s on the system and the ratio of logical/physical cores per processor respectively.
The next columns I find to be very useful are the physical_memory_kb, virtual_memory_kb, committed_kb, and committed_target_kb. These columns are beneficial when you are analyzing your memory consumption or memory requirements for SQL Server.
For those of your that are into the Internals of SQL Server, you may find some benefit in the max_workers_count and scheduler_count columns. I have some DMV’s scheduled for a future day in this series that will deal with these Internals in more detail, so i wont go into detail about these now.
Lastly the sqlserver_start_time column is a quick and easy way to view the last time that SQL Server was started. Have a look at this DMV for yourself against your systems and view the OS information that is returned.
For more information about this Dynamic Management View, please see the below Books Online link:
Follow me on twitter @PrimeTimeDBA