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.