Set up a custom trace to monitor:
Execution Warnings
What it monitors: Before a query can be run it must have memory, If the server has no memory to give for the query then the Execution Warning event will be fired.
What you want to see: No execution warnings happening at all.
Sort Warnings
What it monitors: When a query asks for a ORDER BY query. SQL Will try to run this in memory. If the query results are large the data might be written to TempDB instead of memory, This courses I/O which will slow down the query.
Server Memory change
What it Monitors: When the SQL Server increases or decreases by either 1mb or 5% of the maximum memory (Whichever is larger).
What you want to see: After the server has been running for a period you would hope that the memory does not change much. If it does it indicates that SQL Server is fighting with either another instance or the OS.
Wednesday 6 April 2011
Memory Monitoring with Perfmon
Memory Object: Pages/Sec
What it monitors: Pages per second taken from RAM to Disk, higher the number the more I/O and possible performance issues for SQL .
What you want to see: Avg of 0-20, spikes should be things like backups, restores
Memory Object: Available Bytes
What it monitors: How much memory Is available
What you want to see: Anything greater than 5mb. With default configuration of SQL Memory settings. SQL “should” leave 4-10 mb of memory for the OS. If we see less than 5 then something in SQL is coursing problems
SQL Server: Memory Manager: Total Server Memory
SQL Server: Memory Manager: Target Server Memory
What it monitors: Total Server Memory covers how much memory is currently being used by SQL Server buffer pool. Target Server Memory is how much it would like to use.
What you want to see: If Target Server Memory is higher than Total then we need to upgrade memory
SQL Server Buffer Mgr: Lazy Writes/Sec
What it Monitors: Tracks how many times a second the LazyWriter process moves dirty pages from the buffer to disk in order to free up buffer space.
What you want to see: Should be close to zero. If it is exactly zero, this indicates that SQL Server's buffer cache is sufficiently large and SQL Server does not have to free up dirty pages, and will instead check the status at regular intervals. If the counter value is high more memory is required
Buffer Manager\Page Life Expectancy
What it Monitors: Amount of time in seconds that SQL expects a page to stay in the bufferpool.
What you want to see: MS recommends 300 PLE but large systems can be a lot higher
What it monitors: Pages per second taken from RAM to Disk, higher the number the more I/O and possible performance issues for SQL .
What you want to see: Avg of 0-20, spikes should be things like backups, restores
Memory Object: Available Bytes
What it monitors: How much memory Is available
What you want to see: Anything greater than 5mb. With default configuration of SQL Memory settings. SQL “should” leave 4-10 mb of memory for the OS. If we see less than 5 then something in SQL is coursing problems
SQL Server: Memory Manager: Total Server Memory
SQL Server: Memory Manager: Target Server Memory
What it monitors: Total Server Memory covers how much memory is currently being used by SQL Server buffer pool. Target Server Memory is how much it would like to use.
What you want to see: If Target Server Memory is higher than Total then we need to upgrade memory
SQL Server Buffer Mgr: Lazy Writes/Sec
What it Monitors: Tracks how many times a second the LazyWriter process moves dirty pages from the buffer to disk in order to free up buffer space.
What you want to see: Should be close to zero. If it is exactly zero, this indicates that SQL Server's buffer cache is sufficiently large and SQL Server does not have to free up dirty pages, and will instead check the status at regular intervals. If the counter value is high more memory is required
Buffer Manager\Page Life Expectancy
What it Monitors: Amount of time in seconds that SQL expects a page to stay in the bufferpool.
What you want to see: MS recommends 300 PLE but large systems can be a lot higher
Subscribe to:
Posts (Atom)