Memory Page StatisticsMemory Analysis is very crucial parameter to a DBA.By analysing the memory resource usage,the DBAs can find out if any bottlenecks have arisen due to insufficient memory.For example,due to inadequate memory,the SQL Server cannot store the frequently accessed data in the cache.By adding physical memory,the performance of the server can be improved. The Memory includes the following sections and statistics:
- Bottleneck Analysis :- DBAs can use bottleneck analysis to determine the performance of the database.The DBA can track how the database and user sessions are spending their time.If a heavy table scan activity or latch contention is taking a long time,the bottleneck analysis can be used to find the actual root cause.Once these wait events have been identified as the cause for performance degradation,DBA can drill down further to find which sessions and objects are causing these problems.Memory bottlenecks can be caused due to SQLServer latches.Latches are light weight locks which are used to protect certain resources of memory.To identify the memory bottlenecks,”Latch wait per second” and the “Total Latch wait Time” is presented on the memory page.
- Key Ratio Analysis :- Ratio-based analysis involves examining a number of key database ratios that can be used to indicate how well a database is running. Performance ratios can be used by busy DBA’s for at-a-glance performance analysis.These ratios are taken from the master.sysperfinfo table.This table maintains statistics about the server and the databases from the instant server is up and running.The following are some of the memory ratios which are used on the Performance Analyst Home page.These ratios will help the DBA to obtain information about the general overall memory performance levels of the monitored database:
- Ad Hoc SQL Hit Ratio :- When an ad hoc SQL statement is issued, the query plan is then stored in the SQL Server procedure cache area. If the identical ad hoc statement is executed again, SQL Server uses the query plan already stored in the procedure cache if it is still there. This statistic defines the percentage of times that a query plan for an ad hoc SQL statement is found in the procedure cache. Ad hoc SQL Hit Ratio should be high.
- Buffer Cache Hit Ratio :- Data read from memory will produce faster response time than when that same data is read from disk. Keeping physical I/Os to an absolute minimum is one of the purposes of the SQL Server buffer/data cache. Buffer Cache hit ratio must be around 90% or higher for excellent performance.
- Procedure Plan Hit Ratio :- The SQL Server procedure cache is used to hold the execution plans for all Transact-SQL statements currently executing in the server. When a user executes a Transact-SQL statement, SQL Server looks in the procedure cache for a query plan to use. This statistic is the percentage of query plan requests generated by stored procedures that are found in the procedure cache area. The percentage of times that a statement’s plan and definition can be referenced in memory, the better the procedure execution time. The Procedure Plan hit ratio should be around 95-100%.
- SQL Analysis :- This section provides a summary of the total memory pages currently in use by the various SQL types (procedure, ad hoc sql,prep sql statements,system tables )
- Memory Analysis :- SQL Server uses various memory regions to accomplish various tasks (like retain frequently used data, procedures, etc.) and improve overall performance.Some of the memory regions are:-
- Buffer cache :- The buffer cache is a memory pool of buffer pages into which SQL Server reads data pages for quick access.
- Procedure Cache:-This is the memory used by SQL Server to store the execution plans for triggers,stored procedures,TSQL batch statements.
- Workload Analysis – Top Memory Hogs :- This gives an idea of the percentage of memory used by different processes in the SQL Server.
Memory DetailThe Memory Detail includes the following tabbed pages:
- Buffer Cache:-
The Buffer Cache tab includes the following sections:
- Buffer Cache Hit Ratio:- This has been explained in the Key Ratio Analysis.
- Page Activity:- It shows the count of active memory pages(pages which are currently used by SQL Server) and free pages(pages which are not used).
- Total vs. Target Memory :-Total memory is the amount of memory that’s currently in use by SQL server. Target memory is the amount of memory that could be used.The DBA should take care that the total memory doesn’t approach the target meory.If it does,the DBA can troubleshoot by adding more RAM.
- Database Pages:-The database pages metric gives the amount of pages in memory devoted to database content compared to all other content.
- Page Life Expectancy:-The page life expectancy metric indicates how long SQL server believes a page will stay in the buffer cache. Pages served from memory result in much shorter response times than pages read from disk into the cache.
- Log Cache:-
The Log Cache tab includes the following sections:
- Database Log Cache Ratios & Log Cache Hit Ratio:-The Log Cache Hit Ratio statistic represents the percentage of log cache reads satisfied from the log cache. The database log cache ratios display breaks down log cache hit ratios by database.
- Log Cache Details:-These details give information about the number of reads from log cache,number of times data was flushed from log to disk,log growths,log shrinks etc.
- Procedure Cache:-
The Procedure Cache tab includes the following sections:
- Procedure Cache Hit Ratio
- Procedure/SQL Cache Details:-The Procedure/SQL Cache Details section displays the top 1000 objects in the procedure/SQL cache.
- Procedure/SQL Cache Hit Summary