T-SQL snippets #1

Written by

Posted on

Here’s another little snippet which comes in handy when wanting to know the state of your SQL servers memory. Using sys.dm_os_sys_memory it’s super easy…

SELECT
(total_physical_memory_kb / 1024) / 1024 AS TotalPhysicalMemoryInGB
,(available_physical_memory_kb / 1024) / 1024 AS AvailablePhysicalMemoryInGB
,((total_physical_memory_kb / 1024) / 1024) - ((available_physical_memory_kb / 1024) / 1024) AS UsedPhysicalMemoryInGB
,(total_page_file_kb / 1024) / 1024 AS TotalPageFileInGB
,(available_page_file_kb / 1024) / 1024 AS AvailablePageFileInGB
,system_cache_kb / 1024 AS SystemCacheInMB
,kernel_paged_pool_kb / 1024 AS KernelPagedPoolInMB
,kernel_nonpaged_pool_kb / 1024 AS KernelNonpagedPoolInMB
,system_high_memory_signal_state AS SystemHighMemorySignalState
,system_low_memory_signal_state AS SystemLowMemorySignalState

FROM
master.sys.dm_os_sys_memory

Note the divide by 1024, this just takes the value which is in kilobytes and gives us a value in megabytes since 1024 kilobytes = 1 megabyte. If we divide this figure again by 1024 we’ll get the value in gigabytes since 1024 megabytes = 1 gigabyte.

Note that you’ll find some places where values are given slightly differently and/or are rounded down, for example… 1000 megabytes = 1 gigabyte. Of course this is not mathematically or technically correct as it should be a base 10 number (i.e. decimal) representing a base 2 number (i.e. binary). However, it has become an adopted standard so commonly you’ll find things like hard disk manufacturers listing disk space or drive capacity in this way, whereas some operating systems give the correct figure.

Why has this happened? Who knows, but I suspect it’s probably because its simpler to remember and calculate with 1000 rather than 1024 🙂

%d