Get Memory Clerk Usage

Here's a useful SQL query to help you understand how memory is being utilized by different components within your SQL Server instance. This query provides a clear breakdown of memory usage by various memory clerk types, ordered by the highest consumption.

SELECT TOP (10) mc.[type] AS [Memory Clerk Type]
 ,CAST((SUM(mc.pages_kb) / 1024.0) AS DECIMAL(15, 2)) AS [Memory Usage (MB)]
FROM sys.dm_os_memory_clerks AS mc WITH (NOLOCK)
GROUP BY mc.[type]
ORDER BY SUM(mc.pages_kb) DESC
OPTION (RECOMPILE);

This query leverages the sys.dm_os_memory_clerks Dynamic Management View (DMV), which is an excellent resource for monitoring SQL Server memory. For more in-depth exploration of SQL Server memory and performance, check out these related articles:

• Understanding Memory Grants Pending in SQL Server
• Retrieving Hardware Information from SQL Server
• Another Look at Getting Memory Clerk Usage
• How to Get Memory Dumps from SQL Server

Feel free to leave a comment if you have any questions or further insights on SQL Server memory management!

SQL Server NUMA Node information

This query provides valuable insights into your NUMA (Non-Uniform Memory Access) nodes, showing their composition and how much load they're handling. It helps you understand the distribution of resources and activity across your system's memory architecture.

SELECT node_id
 ,node_state_desc
 ,memory_node_id
 ,processor_group
 ,cpu_count
 ,online_scheduler_count
 ,idle_scheduler_count
 ,active_worker_count
 ,avg_load_balance
 ,resource_monitor_state
FROM sys.dm_os_nodes WITH (NOLOCK)
WHERE node_state_desc <> N'ONLINE DAC'
OPTION (RECOMPILE);

After running this, you'll want to verify that an equal number of schedulers are assigned to each NUMA node. This is especially important for physical or virtual machines with more than four sockets or over 24 physical cores, as balanced scheduler distribution can significantly impact performance.

For more detailed information, check out these resources:

• Microsoft documentation on sys.dm_os_nodes
• Balancing SQL Server Core Licenses Across NUMA Nodes by SQLskills

Get Server properties

Discovering SQL Server Instance Details with SERVERPROPERTY

Ever needed to quickly grab essential information about your SQL Server instance? The `SERVERPROPERTY` function is your best friend! It allows you to retrieve a wide range of details, from server names and versions to specific configurations like collation and default paths. Below is a comprehensive SQL query that leverages `SERVERPROPERTY` to pull numerous valuable insights about your SQL Server environment.

SELECT SERVERPROPERTY('MachineName') AS val, 'Machine Name' AS Descr
UNION ALL SELECT SERVERPROPERTY('ServerName'), 'Server Name'
UNION ALL SELECT SERVERPROPERTY('InstanceName'), 'Instance'
UNION ALL SELECT SERVERPROPERTY('IsClustered'), 'IsClustered'
UNION ALL SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS'), 'Computer Name Physical NetBIOS'
UNION ALL SELECT SERVERPROPERTY('Edition'), 'Edition'
UNION ALL SELECT SERVERPROPERTY('ProductLevel'), 'Product Level (What servicing branch (RTM/SP/CU))'
UNION ALL SELECT SERVERPROPERTY('ProductUpdateLevel'), 'Product Update Level (Within a servicing branch, what CU# is applied)'
UNION ALL SELECT SERVERPROPERTY('ProductVersion'), 'Product Version'
UNION ALL SELECT SERVERPROPERTY('ProductMajorVersion'), 'Product Major Version'
UNION ALL SELECT SERVERPROPERTY('ProductMinorVersion'), 'Product Minor Version'
UNION ALL SELECT SERVERPROPERTY('ProductBuild'), 'Product Build'
UNION ALL SELECT SERVERPROPERTY('ProductBuildType'), 'Product Build Type ( Is this a GDR or OD hotfix (NULL if on a CU build))'
UNION ALL SELECT SERVERPROPERTY('ProductUpdateReference'), 'Product Update Reference (KB article number that is applicable for this build)'
UNION ALL SELECT SERVERPROPERTY('ProcessID'), 'ProcessID'
UNION ALL SELECT SERVERPROPERTY('Collation'), 'Collation'
UNION ALL SELECT SERVERPROPERTY('IsFullTextInstalled'), 'IsFullTextInstalled'
UNION ALL SELECT SERVERPROPERTY('IsIntegratedSecurityOnly'), 'IsIntegratedSecurityOnly'
UNION ALL SELECT SERVERPROPERTY('FilestreamConfiguredLevel'), 'FilestreamConfiguredLevel'
UNION ALL SELECT SERVERPROPERTY('IsHadrEnabled'), 'IsHadrEnabled'
UNION ALL SELECT SERVERPROPERTY('HadrManagerStatus'), 'HadrManagerStatus'
UNION ALL SELECT SERVERPROPERTY('InstanceDefaultDataPath'), 'InstanceDefaultDataPath'
UNION ALL SELECT SERVERPROPERTY('InstanceDefaultLogPath'), 'InstanceDefaultLogPath'
UNION ALL SELECT SERVERPROPERTY('BuildClrVersion'), 'Build CLR Version'
UNION ALL SELECT SERVERPROPERTY('IsXTPSupported'), 'IsXTPSupported'
UNION ALL SELECT SERVERPROPERTY('IsPolybaseInstalled'), 'IsPolybaseInstalled'
UNION ALL SELECT SERVERPROPERTY('IsAdvancedAnalyticsInstalled'), 'IsRServicesInstalled'
  

This query provides a detailed overview of your SQL Server setup, making it incredibly useful for inventory, troubleshooting, or just getting a quick snapshot of your environment.

Further Reading

For a complete and in-depth understanding of all the properties available with `SERVERPROPERTY`, check out the official Microsoft documentation.

Get Volume statistics (Storage Partition)

Ever wonder about the nitty-gritty details of where your SQL Server databases and files live? There's a handy dynamic management function in SQL Server that spills the beans on the operating system volume (directory) where your data resides. It's incredibly useful for checking out the attributes of your physical disk drives or getting a quick peek at the available free space in a specific directory.

SELECT DISTINCT vs.volume_mount_point
 ,vs.file_system_type
 ,vs.logical_volume_name
 ,CONVERT(DECIMAL(18, 2), vs.total_bytes / 1073741824.0) AS [Total Size (GB)]
 ,CONVERT(DECIMAL(18, 2), vs.available_bytes / 1073741824.0) AS [Available Size (GB)]
 ,CONVERT(DECIMAL(18, 2), vs.available_bytes * 1. / vs.total_bytes * 100.) AS [Space Free %]
 ,vs.supports_compression
 ,vs.is_compressed
 ,vs.supports_sparse_files
 ,vs.supports_alternate_streams
FROM sys.master_files AS f WITH (NOLOCK)
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.[file_id]) AS vs
ORDER BY vs.volume_mount_point
OPTION (RECOMPILE);

This SQL query leverages the sys.dm_os_volume_stats function to give you a comprehensive overview, including the mount point, file system type, logical volume name, total size, available space, and even the percentage of free space. It also provides information on whether the volume supports compression, is compressed, or supports sparse files and alternate streams.

For more in-depth documentation on this powerful function, you can refer to the official Microsoft documentation: sys.dm_os_volume_stats (Transact-SQL)

SQL Socket & Core information

This post will show you how to find out the **socket, physical core, and logical core counts** directly from your **SQL Server Error log**. This information is crucial for verifying your SQL Server licensing model.

EXEC sys.xp_readerrorlog 0, 1, N'detected', N'socket';

Running the command above helps you quickly confirm your SQL Server's configuration and ensure it aligns with your licensing.