OS Resources

When MS SQL server starts, it's polls the Windows API and stores that information in the table: sys.dm_os_sys_info.

Why should you check it? You know your server, but does MSSQL knows the right information? When you are running a server farm do know all the servers?

To get that information, you must have SELECT privileges on the VIEW SERVER STATE.

GRANT VIEW SERVER STATE TO <Login USER name>

Here are the one that I use most often:

SELECT cpu_count/hyperthread_ratio AS 'Physical CPUs',
       hyperthread_ratio AS 'Cores/CPU',
       max_workers_count AS 'Workers Threads',
       (physical_memory_in_bytes/1024)/1024 AS 'Physical RAM-Mb',
       (virtual_memory_in_bytes/1024)/1024 AS 'Virtual RAM-Mb',
       stack_size_in_bytes/1024 AS 'stack Size-kb'
FROM sys.dm_os_sys_info;
go
Physical CPUs Cores/CPU   Workers Threads Physical RAM-Mb      Virtual RAM-Mb       stack Size-kb
------------- ----------- --------------- -------------------- -------------------- -------------
1             1           256             511                  2047                 508

(1 row(s) affected)