Monitoring Memory Requirement

System Monitor is used to monitor memory usage in the server. In addition to System Monitor Tool you can use new Dynamic Management views also to collect data about SQL Server Memory.

Steps for Analyzing Memory Requirement

(a) Check how much physical memory is installed on the Box
(b) What other application/processes are consuming memory on the server
© Use System Monitor to capture the following counters
• Memory : Available Bytes :- it indicate how many bytes of memory are available
• Memory : Pages/Sec : Specifies how many pages must be read from the disk or written to the disk to resolve page faults
• SQLServer:BufferManager : This is very important counter , which will give you the Cache-hit ratio. This counter identifies the percentage of pages that were found in the buffer pool without read the disk. The value of this counter should be over 90%. High values indicate good cache usage and minimal disk access when searching for data
• SQLServer:MemoryManager:Total Server Memory :- Determines the amount of physical memory used by each instance of SQL Server
• Process:Working Set :- Gives the set of memory pages that have been recently accessed by the threads running in the process and can be used to determine how much memory SQL Server is using
• SQLServer:Buffer Manager:Page Life Expectance : It gives the average time spent by a data page in the data cache. A value of less than 3000 seconds indicates that SQL Server need more memory.

Dynamic Management Views used to collect data about SQL Server Memory Usage

• Sys.dm_exec_query_stats : Provides statistics on memory and CPU usage for a specific query.
• Sys.dm_exec_cached_plans : Gives a list of the query plans that are currently cached in mamory
• Sys.dm_os_memory_objects : Provides information about object types in memory, such as Memobj_Compile_Adhoc and Memobj_statement
• Sys.dm_os_memory_clerks returns the se of all memory clerks that are currently active in the instance of SQL Server.