How to Analyze SQL Server Memory Usage by Clerk Type using T-SQL

Summary: Use the sys.dm_os_memory_clerks DMV to identify which components—such as the Buffer Pool, Plan Cache, or Columnstore—are consuming the most memory in your SQL Server instance.

Identify Top Memory Consumers via SQL Server Memory Clerks

If your SQL Server is experiencing high memory pressure, the first step is to identify which internal component is "hogging" the RAM. SQL Server uses Memory Clerks to manage allocations for different features like the Buffer Pool, Plan Cache, and CLR.

This query provides a clear breakdown of memory usage by various clerk types, ordered by the highest consumption, helping you pinpoint exactly where the memory is going.

Top 10 Memory Clerk Usage Script

-- Get top 10 memory consumers by Clerk Type
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);
        

Understanding Common Clerk Types

• MEMORYCLERK_SQLBUFFERPOOL: This is usually the largest, representing the data cache.
• CACHESTORE_SQLCP: The SQL Plan Cache (ad-hoc queries).
• CACHESTORE_OBJCP: Object Plans (stored procedures, functions).
• MEMORYCLERK_SQLOPTIMIZER: Memory used during the query optimization process.

This query leverages the sys.dm_os_memory_clerks DMV. For more in-depth exploration of performance tuning, check out these related articles:

---

• Understanding Memory Grants Pending
• Retrieving Hardware Information
• Advanced Memory Clerk Auditing
• Finding SQL Server Memory Dumps

Seeing unexpected memory usage? Large amounts of memory in CACHESTORE_SQLCP might indicate a need to enable "Optimize for Ad-hoc Workloads"!

How to Audit SQL Server NUMA Node Configuration and Scheduler Load

Summary: Use the sys.dm_os_nodes DMV to inspect NUMA node health, verify balanced CPU scheduler distribution, and monitor active worker loads across your hardware architecture.

Monitoring NUMA Node Configuration and Load Balance

This query provides valuable insights into your NUMA (Non-Uniform Memory Access) nodes, showing their composition and how much load they are currently handling. Understanding the distribution of resources and activity across your system's memory architecture is critical for high-performance database environments.

NUMA Node and Scheduler Audit Script

-- Check NUMA node health and scheduler distribution
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);
        

Why This Matters for Performance

After running this, you should verify that an equal number of online 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.

• Balanced Load: An uneven distribution of schedulers can lead to "hot" NUMA nodes where specific CPUs are overworked while others remain idle.
• License Alignment: On standard edition servers with core limits, ensure your limited cores are spread evenly across nodes to maximize memory bandwidth.
• Resource Monitor State: This indicates if the internal thread responsible for memory management is healthy on that specific node.

---

For more detailed information on NUMA tuning and hardware alignment, explore these resources:

• Official Documentation: sys.dm_os_nodes
• SQLskills: Balancing Core Licenses Across NUMA Nodes

Troubleshooting performance bottlenecks? Proper NUMA alignment is often the "hidden" fix for scaling issues on large multi-processor servers!

How to use SERVERPROPERTY to Retrieve SQL Server Instance Details

Summary: Use the SERVERPROPERTY function in T-SQL to extract essential metadata about your SQL Server instance, including versioning, edition, patch levels, and feature support.

Discovering SQL Server Instance Details with SERVERPROPERTY

Ever needed to quickly grab essential information about your SQL Server instance? The SERVERPROPERTY function is a powerful tool for DBAs and developers alike. It allows you to retrieve a wide range of details—from network names and versioning to specific engine configurations like collation and default file paths—all without needing access to the physical host.

The Comprehensive Instance Audit Script

Below is a comprehensive SQL query that leverages SERVERPROPERTY to pull numerous valuable insights about your SQL Server environment into a readable vertical list.

-- Retrieve a detailed snapshot of SQL Server instance properties
SELECT SERVERPROPERTY('MachineName') AS [Value], 'Machine Name' AS [Description]
UNION ALL SELECT SERVERPROPERTY('ServerName'), 'Server Name'
UNION ALL SELECT SERVERPROPERTY('InstanceName'), 'Instance'
UNION ALL SELECT SERVERPROPERTY('IsClustered'), 'IsClustered'
UNION ALL SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS'), 'NetBIOS Physical Name'
UNION ALL SELECT SERVERPROPERTY('Edition'), 'Edition'
UNION ALL SELECT SERVERPROPERTY('ProductLevel'), 'Product Level (RTM/SP/CU)'
UNION ALL SELECT SERVERPROPERTY('ProductUpdateLevel'), 'CU Level'
UNION ALL SELECT SERVERPROPERTY('ProductVersion'), 'Full Product Version'
UNION ALL SELECT SERVERPROPERTY('ProductMajorVersion'), 'Major Version'
UNION ALL SELECT SERVERPROPERTY('ProductMinorVersion'), 'Minor Version'
UNION ALL SELECT SERVERPROPERTY('ProductBuild'), 'Build Number'
UNION ALL SELECT SERVERPROPERTY('ProductBuildType'), 'Build Type (GDR/OD/CU)'
UNION ALL SELECT SERVERPROPERTY('ProductUpdateReference'), 'KB Article Reference'
UNION ALL SELECT SERVERPROPERTY('ProcessID'), 'OS Process ID'
UNION ALL SELECT SERVERPROPERTY('Collation'), 'Server Collation'
UNION ALL SELECT SERVERPROPERTY('IsFullTextInstalled'), 'Full-Text Search Installed'
UNION ALL SELECT SERVERPROPERTY('IsIntegratedSecurityOnly'), 'Windows Auth Only Mode'
UNION ALL SELECT SERVERPROPERTY('FilestreamConfiguredLevel'), 'Filestream Level'
UNION ALL SELECT SERVERPROPERTY('IsHadrEnabled'), 'Always On (HADR) Enabled'
UNION ALL SELECT SERVERPROPERTY('HadrManagerStatus'), 'HADR Manager Status'
UNION ALL SELECT SERVERPROPERTY('InstanceDefaultDataPath'), 'Default Data Path'
UNION ALL SELECT SERVERPROPERTY('InstanceDefaultLogPath'), 'Default Log Path'
UNION ALL SELECT SERVERPROPERTY('BuildClrVersion'), 'CLR Version'
UNION ALL SELECT SERVERPROPERTY('IsXTPSupported'), 'In-Memory OLTP Supported'
UNION ALL SELECT SERVERPROPERTY('IsPolybaseInstalled'), 'Polybase Installed'
UNION ALL SELECT SERVERPROPERTY('IsAdvancedAnalyticsInstalled'), 'Machine Learning / R Services'
OPTION (RECOMPILE);
        

Why Use This?

This query provides a detailed overview of your SQL Server setup, making it incredibly useful for:

• Inventory Audits: Quickly gathering version and edition details across multiple servers.
• Troubleshooting: Verifying if a specific Cumulative Update (CU) or KB patch has been applied.
• Configuration Checks: Ensuring default paths and security modes align with company standards.

---

Further Reading

For a complete and in-depth understanding of all properties available with this function, refer to the official Microsoft documentation.

Need to automate your environment discovery? This script is a perfect starting point for building a dynamic server inventory dashboard!

How to Check SQL Server Disk Space and Volume Stats via T-SQL

Summary: Use sys.dm_os_volume_stats to audit physical drive space, mount points, and file system attributes directly from SQL Server for all database files.

Monitoring Database Volume Stats and Disk Space

Ever wonder about the nitty-gritty details of where your SQL Server databases and files live? There's a powerful dynamic management function (DMF) that spills the beans on the operating system volume where your data resides. It's incredibly useful for auditing physical disk attributes or getting a live view of available free space without leaving SQL Server Management Studio.

Volume Stats and Free Space Query

This script uses a CROSS APPLY to join your master files with their respective volume statistics, providing a consolidated view of every drive currently hosting a SQL Server data or log file.

-- Detailed breakdown of volume stats for all database files
SELECT DISTINCT 
    vs.volume_mount_point AS [Mount Point],
    vs.file_system_type AS [File System],
    vs.logical_volume_name AS [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 [Mount Point]
OPTION (RECOMPILE);
        

Why This Matters

Traditional methods like xp_fixeddrives only show drive letters. However, sys.dm_os_volume_stats is superior because:

• Mount Point Support: It correctly identifies volumes mounted as folders (Mount Points), which are common in enterprise SAN environments.
• Proactive Alerting: You can use the Space Free % logic to build automated alerts before a disk fills up and takes your database offline.
• Feature Awareness: It identifies if a volume supports modern features like Sparse Files (used by Database Snapshots and CheckDB) or Compression.

For more in-depth documentation on this function, refer to the official Microsoft documentation.

---

Is your disk space running low? Identifying the exact volume and its percentage of free space is the first step in effective capacity planning!

How to Find SQL Server Socket and Core Counts for Licensing

Summary: Use the xp_readerrorlog procedure to extract CPU socket, physical core, and logical core counts directly from the SQL Server startup logs for accurate licensing verification.

Find Socket and Core Counts in the SQL Server Error Log

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, especially when moving between physical and virtual environments.

Querying the Error Log for CPU Metadata

During the SQL Server startup sequence, the engine detects and logs the available hardware topology. You can use the following command to filter the current log for these specific hardware entries:

-- Identify CPU hardware topology for licensing
EXEC sys.xp_readerrorlog 0, 1, N'detected', N'socket';
        

Why This Information is Vital

Running the command above helps you quickly confirm your SQL Server's hardware configuration and ensures it aligns with your licensing requirements:

• Physical Core Count: Essential for Core-based licensing (Standard and Enterprise).
• Socket Count: Useful for checking host-level hardware limits.
• Hyperthreading: Confirming the ratio of logical vs. physical cores to ensure SQL Server is utilizing the available hardware efficiently.

---

Performing a Licensing Audit? This simple command is the fastest way to pull hardware stats without needing server-level permissions!