SQL Memory Monitoring

Memory Monitoring

In order to ensure that each query has enough memory to execute, it is critical to know the trend of memory usage for a SQL Server.

SQL Server Summary Metrics

SQL Server Memory Usage

SQL Cache memory allows the Server to auto-manage its memory and re-prioritize the queries accordingly. This flexibility enables the SQL Server to decide to hold pages which is often referenced and delete those pages which is rarely used. This reduces the amount of time taken to execute a query thus improving the overall efficiency of the SQL Server. Thus by monitoring this statistic, the DBA can now easily identify if any query is causing performance bottleneck. It also allows him to adjust the values for Cache thus improving the overall performance of the SQL Server.

SQL Memory Usage

SQL Server Buffer Manager Statistics

SQL Server Buffer Manager statistics allow the DBA to view how the SQL Server is storing the data, internal data structures, and how is Cache being utilized. By monitoring this information, the DBA can drastically improve the overall database design and efficiency of the queries being used. For example, by monitoring Pages Read/Min metric, the DBA can now see how much database page reads happens using the cache. If the buffer cache hit ratio is higher, then the database pages read from I/O is reduced thereby increasing the efficiency of the query. This can also help in designing better database design, remove or improve queries which utilize too much time to execute etc.

SQL Buffer Hit Ratio Usage

SQL Server Access Method Details

SQLDBManager Plus allows DBAs to monitor Full Scans/Min, Range Scans/Min, Probe Scans/Min, etc. By doing so, it allows DBAs to view how the SQL Server is gathering information/data from the server. It also tells the DBA how effective a query has been during execution, how it has accessed the data, if any indexes were available to the query, and if so whether where they been useful or not.

SQL Access Method Details