Tag: DMVs

  • Script to get the CPU and Memory Information

    You can execute the below script to get the processor and memory information.

    -- Scritp to get Processor Info from Registry
EXEC xp_instance_regread
'HKEY_LOCAL_MACHINE',
'HARDWARE\DESCRIPTION\System\CentralProcessor\0',
'ProcessorNameString';

-- Script to get CPU and Memory Info
SELECT 
 cpu_count AS [Number of Logical CPU]
,hyperthread_ratio
,cpu_count/hyperthread_ratio AS [Number of Physical CPU]
,physical_memory_in_bytes/1048576 AS [Total Physical Memory IN MB]
FROM sys.dm_os_sys_info OPTION (RECOMPILE);

  • Script to list out all DMVs and DMFs of SQL Server


    -- To check the diffrent kind of system objects
    SELECT Distinct type_desc
    FROM sys.system_objects

    -- To get the list of DMVs or DMFs
    SELECT name, type, type_desc,SCHEMA_NAME(schema_id) as SNAME
    FROM sys.system_objects
    WHERE name LIKE 'dm[_]%'
    ORDER BY name