Memory objects statistics
Job details
| Name: | Memory objects statistics |
| Platform: | Sqlserver |
| Category: | Performance |
| Description: | This task collects the size of memory objects that are currently allocated by the SQL Server |
| Long description: | This task collects the size of memory objects that are currently allocated by the SQL Server, and is primarily used to analyze memory usage and to identify possible memory leaks. |
| Version: | 1.3 |
| Default schedule: | 7,27,47 * * * |
| Requires engine install: | Yes |
| Compatibility tag: | .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2000′ & maj_version < ‘2012′ & hasengine=‘YES’ & engine_edition = ‘Microsoft SQL Server’] |
Parameters
| Name | Default value | Description |
|---|---|---|
| max memory object | 10 | The maximum number of memory objects which will be inserted into the historic table. |
| history threshold | 3 | Number of days to provide statistics for. |
Job Summary
- Purpose: The purpose of this job is to monitor and collect statistics on memory objects currently allocated by Microsoft SQL Server, especially focusing on instances with specific SQL Server versions (greater than 2000 and less than 2012).
- Why: This job is important because it helps in analyzing memory usage and identifying possible memory leaks within the SQL Server. Given that memory objects use a finer granularity of memory allocation and directly affect the efficiency and performance of the SQL Server, monitoring these can preempt performance degradation and system instability.
- Manual checking: Memory statistics can be checked manually in the SQL Server by executing the following SQL command:
SELECT type, SUM(pages_allocated_count * (page_size_in_bytes/1024)) as "memory in use", SUM(max_pages_allocated_count * (page_size_in_bytes/1024)) as "Max memory used", count(*) "memory object count" FROM master.sys.dm_os_memory_objects GROUP BY type ORDER BY 2 DESCDetailed Analysis
This monitoring job includes several components and dependencies which makes it a comprehensive tool for managing memory object statistics in SQL Server:
Key Components and Their Functionalities
- Memory objects statistics task: Collects detailed data about memory objects such as type, memory in use, maximum memory used, and the count of memory objects.
- Historical data management: Stores historical memory usage statistics and aggregates daily memory usage to help in trend analysis and reporting.
- Dependency Management: Includes dependencies on various objects and tables such as historical tables and system views for memory objects.
Dependencies
- Direct dependencies on:
- dbw_memory_obj_stat (self-referencing)
- Cleanup operations on failure include:
- Dropping of created tables and procedures to ensure environment cleanliness in case of job failure.
Automation and Scheduling
- The task is set to trigger automatically based on a predefined schedule:
- Task Schedule: Every minute at seconds 7, 27, and 47 respectively.
Implementation Details
- SQL Codes used:
- Creation of historical and aggregate tables to store data.
Reporting and Visualization
- Provides detailed textual and tabular reports on current memory usage statistic.
- Visualizations include category charts mapping memory usage history and aggregating daily statistics to present trends and potential anomalies.
Importance
- Helps system administrators and database professionals keep track of SQL Server’s memory usage efficiently.
- Allows proactive measures to be taken before any significant impact due to memory leaks or excessive memory allocation occurs.
Overall, this dbWatch Control Center job is crucial for maintaining optimal performance and stability in SQL Server environments, particularly for specified versions where memory management can be critically linked to system performance.