Dynamic Management Views in SQL Server
Dynamic Management Views in SQL Server exists in SYS schema and their names start with DM_.
To query a DMV, we should prefix the view name with SYS.
Ex: To know total physical memory of the SQL Server we can use:
SELECT
(Physical_memory_in_bytes/1024.0)/1024.0 AS Physical_memory_in_Mb
FROM
sys.dm_os_sys_info
The Mostly used DMV’s to check the current behaviour of SQL Server are:
1. SQL Server related/Hardware Resources DMV
2. Database related DMV
3. Index related DMV
4. Execution related DMV
1. SQL Server Related DMV
This section details the DMV’s associated with SQL Server system.
Below are some of the DMV’s which are related to OS, Disk and Memory.
i.sys.dm_os_sys_info
This view returns the information about the SQL Server machine, available … Read the rest