Server memory statistics

Job details

Name:	Server memory statistics
Platform:	Sqlserver
Category:	Performance
Description:	This tasks collects statistics from the sys.dm_os_sys_info performance view about the memory resources available to and consumed by the SQL Server.
Long description:	This tasks collects statistics from the sys.dm_os_sys_info performance view about the memory resources available to and consumed by the SQL Server.
Version:	1.1
Default schedule:	28,58 * * *
Requires engine install:	Yes
Compatibility tag:	.[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2000′ & maj_version < ‘2012′ & hasengine=‘YES’ & (engine_edition = ‘Microsoft SQL Server’	engine_edition = ‘Azure SQL Managed Instance’)]

Parameters

Name	Default value	Description
history threshold	7	Number of days to provide statistics for.

Job Summary

Purpose: The purpose of this job is to collect and monitor statistics on memory usage and availability for SQL Server instances, specifically targeting versions between SQL Server 2000 and SQL Server 2012.

Why: This job is important as it helps in understanding the utilization of physical and virtual memory, CPU usage, and buffer pool statistics of SQL Server instances. These insights are critical for optimizing server performance and ensuring that the SQL Server instance runs efficiently without memory constraints.

Manual checking: You can manually check this information in the database using the following SQL commands:

SELECT cpu_count, physical_memory_in_bytes, virtual_memory_in_bytes FROM master.sys.dm_os_sys_info

Job Description

The “Server Memory Statistics” task is executed periodically to gather memory statistics from the SQL Server instance.
It focuses on multiple aspects of memory usage including the CPU count, physical memory, virtual memory, and buffer pool details.
The job involves storing historical memory statistics in a custom table and also involves cleanup processes if tasks fail.

Details of Execution

This job is scheduled to run every hour, compiling and updating memory usage statistics into the ‘dbw_server_mem_histr’ table for historic reference.
The statistical data is presented in various visualization forms, including tables and charts, which offer insight into memory allocation and usage over time.

Dependencies

Requires the SQL Server instance to have the necessary permissions to view server state data.
Relies on ‘master.sys.dm_os_sys_info’ system function for fetching live memory statistics.
Disposal actions include the deletion of custom procedures and tables involved if the job execution fails.

Implementation

The job operates by creating specific tables and stored procedures to manage and store the memory statistics.
These include procedures to fetch current memory statistics, manipulate them for consistency with historical data, and manage data retention based on a predefined history threshold.

Output Presentation

Parameter	Description
CPU Count	Number of logical CPUs
Physical Memory (MB)	Available physical memory
Virtual Memory (MB)	Available virtual memory
Buffer Pool (MB)	Memory committed to SQL Server buffer pool
Buffer Pool Target (MB)	Target memory for the buffer pool before adjustments
Buffer Pool Visible (MB)	Visible buffer pool memory that can be allocated without further reservation
Date	Date and time the statistics were recorded

Memory statistics are plotted over time to display trends and help in capacity planning and performance analysis.
Each area of focus provides vital information for database administrators to ensure that memory resources are adequately provisioned and managed.

SQL Details

The critical SELECT statement for memory statistics:

SELECT cpu_count, physical_memory_in_bytes/1024/1000 as "Physical memory", virtual_memory_in_bytes/1024/1000 as "Virtual memory" FROM master.sys.dm_os_sys_info

Insertion of collected data into the history table followed by validation checks to ensure data integrity and manage storage by cleaning older entries beyond the specified history threshold.