Memory View (SQL Server Performance Analyst)

Go Up to Microsoft SQL Server Performance Analyst Statistics

In addition to a Home page, the Memory category of SQL Server Performance Analyst includes the following tabbed pages:

• Buffer Cache Tab
• Log Cache Tab
• Procedure Cache Tab

Home

The Home page of the Memory category includes the following sections and statistics:

• Key Ratio Analysis Pane
• Bottleneck Analysis Pane
• SQL Analysis Pane
• Memory Analysis Pane
• Workload Analysis Pane

Key Ratio Analysis Pane

Database performance analysts typically use one of two methods for examining the performance levels of a database - ratio-based or wait/bottleneck-based. 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 serve as very good roll-up mechanisms for busy DBA's to use for at-a-glance performance analysis. Many DBAs have large database farms to contend with and cannot spend time checking detailed wait-based analysis outputs for each and every database they oversee. Succinctly presented performance ratios can assist in such situations by giving DBAs a few solid indicators that can be quickly scanned to see if any database needs immediate attention.

While there are certainly many opinions as to what rules to follow, there are some standards that should always be adhered to. To start with, many of the formulas that make up ratio-based analysis must be derived from delta measurements instead of cumulative statistics. Many of the global ratios that a DBA will examine come from the master.sysperfinfo performance table. This table maintains a count of various statistics for the server and particular databases since the server was brought up. For servers that are kept up for long periods of time, these values can grow quite large and will impact how a particular ratio that a DBA might be looking at is interpreted. However, if delta statistics are used (taking, for a specified sampling period, the before and after counts of each statistic that make up a ratio), then an accurate and current portrayal of the various ratios can be had.

A final thing to remember about using ratio-based analysis is that, while there are a number of rules of thumb that can be used as starting points to begin the evaluation of database performance, DBAs must determine each SQL Server's individual 'personality' with respect to the key performance ratios. Some hard and fast rules simply do not apply to every database. The danger in using blanket ratio standards is that they can lead the DBA to haphazardly take action, which can at times contribute nothing to the situation, and sometimes even degrade performance.

The following memory ratios are used on the Performance Analyst Home page to succinctly communicate the general overall memory performance levels of the monitored database:

• Cache Used
• Procedure Cache Used
• Used Cache %
• Used Procedure Cache %

The following statistics, available in this pane, are duplicates of statistics available on the Key Ratio Analysis Pane of the Home View (SQL Server Performance Analyst) page:

• Buffer Cache Hit Ratio
• Procedure Plan Hit Ratio
• Ad Hoc SQL Hit Ratio
• Log Cache Hit Ratio

Cache Used

The total MB of space that SQL Server is using in the buffer cache. Each instance of SQL Server has its own buffer cache where it stores recently used data pages to reduce physical I/O. The goal is to make the buffer cache large enough to maximize the ratio of logical reads to physical reads, but not so large that excessive memory swapping starts generating physical I/O to the pagefile.

Location	Microsoft SQL Server Performance Analyst Statistics > Memory View (SQL Server Performance Analyst) > Home > Key Ratio Analysis Pane
Metrics	A percentage consistently remaining close to 100% indicates a deficient amount of memory available to SQL Server.
Correction	First, ensure SQL Server is configured to use as much physical memory as possible by checking the Max Server Memory configuration option. Also, consider increasing your SQL Server Min Memory parameter to allocate more memory to SQL Server. (Note that to obtain optimal values for these parameters, an option is to install more physical RAM to your server.) Check for any large objects that are pinned in memory that could possibly be removed.

Procedure Cache Used

The total MB of space that SQL Server is using in its SQL/procedure cache. Microsoft has begun to transition from the term “procedure cache” to “SQL cache” to define this area of memory. The reason being that in SQL Server's past, this area was devoted exclusively to holding query plans for stored procedures only.

The SQL Cache (procedure cache) is the part of the SQL Server memory pool that is used to store execution plans for Transact-SQL batches, stored procedures, and triggers. Execution plans record the steps that SQL Server must take to produce the results specified by the Transact-SQL statements contained in the batches, stored procedures, or triggers.

Location

Microsoft SQL Server Performance Analyst Statistics > Memory View (SQL Server Performance Analyst) > Home > Key Ratio Analysis Pane

Used Cache %

The Used Buffer Cache Percent statistic represents the total percentage of memory that is in use in the SQL Server buffer cache. Each instance of SQL Server has its own buffer cache where it stores recently used data pages to reduce physical I/O. The goal is to make the buffer cache large enough to maximize the ratio of logical reads to physical reads, but not so large that excessive memory swapping starts generating physical I/O to the pagefile.

Location	Microsoft SQL Server Performance Analyst Statistics > Memory View (SQL Server Performance Analyst) > Home > Key Ratio Analysis Pane
Metrics	A percentage used consistently remaining close to 100% indicates a deficient amount of memory available to SQL Server.
Correction	First, ensure SQL Server is configured to use as much physical memory as possible by checking the Max Server Memory configuration option. Also, consider increasing your SQL Server Min Memory parameter to allocate more memory to SQL Server. (Note that to obtain optimal values for these parameters, an option is to install more physical RAM to your server.) Check for any large objects that are pinned in memory that could possibly be removed.

Used Procedure Cache %

The total percentage of memory that SQL Server is using in its SQL/procedure cache. Microsoft has begun to transition from the term “procedure cache” to “SQL cache” to define this area of memory. The reason being that in SQL Server's past, this area was devoted exclusively to holding query plans for stored procedures only.

The SQL Cache is the part of the SQL Server memory pool that is used to store execution plans for Transact-SQL batches, stored procedures, and triggers. Execution plans record the steps that SQL Server must take to produce the results specified by the Transact-SQL statements contained in the batches, stored procedures, or triggers.

Location

Microsoft SQL Server Performance Analyst Statistics > Memory View (SQL Server Performance Analyst) > Home > Key Ratio Analysis Pane

Bottleneck Analysis Pane

When SQL Server is up and running, every connected process is either busy doing work or waiting to perform work. A process that is waiting might mean nothing in the overall scheme of things or it can be an indicator that a database bottleneck exists. And this is where wait-based or bottleneck analysis comes into play. DBAs use this form of performance analysis to determine if perceived bottlenecks in a database are contributing to a performance problem.

Bottleneck analysis is a valid method of measuring performance because it helps a DBA track where a database has been spending its time. If lock contention or heavy table scan activity has been dragging down database performance, a DBA can use bottleneck analysis to confirm the actual root cause. Once one or more wait events or other bottlenecks have been pinpointed as possible performance vampires, the DBA can drill down and oftentimes discover a fair amount of detail about which sessions and objects are causing the problem.

Memory bottlenecks can cause performance degradation in an otherwise well-running database. Typically, these bottlenecks center around the SQL Server latches, which are lightweight locks used to protect certain resources in memory. To help you identify such problems, the following statistics are presented on the Performance Analyst Memory home page:

• Latch Waits/Sec
• Total Latch Wait Time
• Top Latch Waits

Latch Waits/Sec

This metric represents the number of latches per second that could not be satisfied immediately by SQL Server. Latches are lightweight, mini-locks that are used to protect actions and resources used inside transactions.

Location	Microsoft SQL Server Performance Analyst Statistics > Memory View (SQL Server Performance Analyst) > Home > Bottleneck Analysis Pane
Metrics	Unless accompanied by long wait times, latch waits should not pose too much of a performance problem in normal SQL Server installations. If you have a high number of latch waits per second, then it is likely that SQL Server is also sporting a low buffer cache hit ratio and is being forced to perform extra physical I/O.

Total Latch Wait Time

Latches are lightweight, mini-locks that are used to protect actions and resources used inside transactions. The total latch wait time statistic represents the sum total of all latch wait time that has occurred on the system.

Location	Microsoft SQL Server Performance Analyst Statistics > Memory View (SQL Server Performance Analyst) > Home > Bottleneck Analysis Pane
Metrics	Unless accompanied by long wait times, latch waits should not pose too much of a performance problem in normal SQL Server installations. If you have a high number of latch waits per second, then it is likely that SQL Server is also sporting a low buffer cache hit ratio and is being forced to perform extra physical I/O.

Top Latch Waits

The Top Latch Waits graph depicts the top latch waits by wait time.

Location	Microsoft SQL Server Performance Analyst Statistics > Memory View (SQL Server Performance Analyst) > Home > Bottleneck Analysis Pane
Metrics	Latch waits rarely impact performance, however seeing latch waits with high wait time might indicate an area that needs further investigation.
Correction	Wait events can be hard to interpret. If you see a particular event that has caused a lot of wait time, you can review the information in this Microsoft Knowledge Base Article to help understand the cause and potential remedy: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q244455

SQL Analysis Pane

When SQL Server executes a set of SQL (ad hoc, procedure, trigger, etc.), it places the code into its procedure cache. The SQL Analysis section of the Performance Analyst Memory page provides a summary of the total memory pages currently in use by the various SQL types (procedure, ad hoc, etc.)

The SQL Analysis section provides you with a quick overview of the percentage of Reads, Writes, CPU, and Elapsed Time the most expensive SQL statements tracked by Performance Analyst have used. Depending on the page you are on, you might just see information regarding physical I/O activity or DML statements. For example, you might see that a SQL statement has caused 60% of all physical reads on the system, or that a procedure is responsible for 90% of all the CPU usage. To get information regarding the actual SQL text or stored procedure calls, drill down into the Top SQL details views.

Note: SQL Profiling is turned on by default in Performance Analyst so you can automatically collect SQL traffic. If you do not want to collect SQL execution information, use the options inside of Performance Analyst to disable SQL profiling.

Memory Analysis Pane

SQL Server uses various memory regions to accomplish various tasks (like retain frequently used data, procedures, etc.) and improve overall performance. At start up, SQL Server is allocated memory that will fall between the range of two configurable parameters: Min Memory and Max Memory. These statistics show you the amount of SQL Server memory allocated at present. In a normal SQL Server system, this number fluctuates as SQL Server uses more memory and releases unused memory back to the operating system. SQL Server also works in conjunction with the Windows Memory Manager, deallocating space when it detects that the operating system is unable to satisfy memory requests of other operating system processes. Depending on the version of SQL Server you are using, expect to deal with different memory regions. The table below describes some of the SQL Server memory regions:

Memory Scan	Description
Buffer Cache	The buffer cache is a memory pool of buffer pages into which SQL Server reads data pages for quick access.
Procedure Cache	Beginning with SQL Server version 7.0, you can place other SQL-related code objects in memory for reuse. Once SQL Server parses through and places a set of SQL of program code in memory, response time can be increased for subsequent calls to the same set of SQL or SQL code objects.
Connection Memory	The total amount of dynamic memory the server is using for maintaining connections.
Granted Workspace Memory	The total amount of memory granted to executing processes such as sort, hash, bulk copy operations, and so forth.
Lock Memory	The total amount of dynamic memory the server is using for locks.
Optimizer Memory	The total amount of dynamic memory the server is using for query optimization.
SQL Cache Memory	The total amount of memory used to retrieve stored SQL caches.

Workload Analysis Pane

It is not uncommon for one or two users to cause the majority of runtime problems that plague a server. The problem could be a runaway process, an untuned batch procedure, or other user-initiated operation.

If your database server does not have an overabundance of memory, then you should periodically check to see who your heavy memory users are along with the total percentage of memory each takes up. If you see one or two users who have more than 25-50% of the total memory usage, then you should further investigate the sessions to see what activities they are performing.

Buffer Cache Tab

The Buffer Cache Hit Ratio tab includes the following sections:

• Top Twenty Buffer Cache Objects
• Total vs. Target Memory
• Cached Pages By Database
• Page Life Expectancy

The following statistic, available on this page, duplicates a statistic on the Key Ratio Analysis Pane of the Home View (SQL Server Performance Analyst) page:

• Buffer Cache Hit Ratio

Top Twenty Buffer Cache Objects

SQL Server uses various memory regions to accomplish various tasks (like retain frequently used data, procedures, etc.) and improve overall performance. At start up, SQL Server is allocated memory that falls between the range of two configurable parameters: Min Memory and Max Memory. The Top Twenty Buffer Cache Objects chart shows you the amount of SQL Server memory allocated at present. In a normal SQL Server system, this number fluctuates as SQL Server uses more memory and releases unused memory back to the operating system. SQL Server also works in conjunction with the Windows Memory Manager, deallocating space when it detects that the operating system is unable to satisfy memory requests of other operating system processes. Depending on the version of SQL Server you are using, there are several memory regions you should expect to deal with. The table below describes the SQL Server memory regions:

Memory Scan	Description
Default Buffer Cache	The buffer cache is a memory pool of buffer pages into which SQL Server reads data pages for quick access.
Procedure/SQL Cache	Beginning with SQL Server version 7.0, you can place other SQL-related code objects in memory for reuse. Once SQL Server parses through and places a set of SQL of program code in memory, response time can be increased for subsequent calls to the same set of SQL or SQL code objects.
Connection Memory	The total amount of dynamic memory the server is using for maintaining connections.
Lock Memory	The total amount of dynamic memory the server is using for locks.
Optimizer Memory	The total amount of dynamic memory the server is using for query optimization.

The Active and Free Page chart in Performance Analyst displays a count of active memory pages (those being used by SQL Server) and free pages (pages not being used).

Location	Microsoft SQL Server Performance Analyst Statistics > Memory View (SQL Server Performance Analyst) > Buffer Cache Tab
Metrics	Seeing consistent high volumes of free pages might indicate that SQL Server is using more memory than necessary.

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. SQL server dynamically adjusts its memory allocations depending either on workload up to the maximum memory configuration set by the DBA or the amount it thinks it should give other (non-SQL) server operations.

Location	Microsoft SQL Server Performance Analyst Statistics > Memory View (SQL Server Performance Analyst) > Buffer Cache Tab
Metrics	When total memory approaches or reaches the maximum amount of target (defined) memory, then SQL server is experiencing memory pressure. You can try adding more RAM to the machine or adjusting memory ceiling limits imposed at the SQL server configuration level.

Cached Pages By Database

The Cached Pages By Database metric gives the amount of pages in memory devoted to database content compared to all other content.

Location

Microsoft SQL Server Performance Analyst Statistics > Memory View (SQL Server Performance Analyst) > Buffer Cache Tab

Metrics

If you see that a large percentage of memory is devoted to database pages, the server is database page centric and not procedure plan centric. This can indicate that too many table scans are taking place, which might be flooding the buffer cache with infrequently used pages. You can determine if this is the case by looking at the page life expectancy metric. Page life expectancy readings of 300 seconds or less can indicate excessive table scan activity.

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. It’s best if frequently used data is pinned in the buffer cache.

Location	Microsoft SQL Server Performance Analyst Statistics > Memory View (SQL Server Performance Analyst) > Buffer Cache Tab
Metrics	Readings of 300 seconds or less frequently mean that too many table scans are occurring and flooding the buffer cache with pages that are used rarely. Check the I/O access patterns on the I/O home page or the User I/O drill-down to confirm if this is the case.

Procedure Cache Tab

The Procedure Cache tab includes the following sections:

• Procedure Cache Hit Ratio
• Procedure/SQL Cache Detail
• Procedure/SQL Cache Summary

Procedure Cache Hit Ratio

The SQL Server procedure cache holds 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.

The Procedure Cache Hit Ratio statistic is the percentage of query plan requests generated by stored procedures that are found in the procedure cache area. The greater the percentage of times that a statement's plan and definition can be referenced in memory, the better the procedure execution time.

Location	Microsoft SQL Server Performance Analyst Statistics > Memory View (SQL Server Performance Analyst) > Procedure Cache Tab
Metrics	A high procedure cache hit rate is a desirable thing. You should strive for a hit ratio between 95-100%, with 95% being a good performance benchmark for code reference. Note that when a database first starts, the procedure cache hit rate will not be at an optimal level because all code being used will be relatively new, and as such, must be read in from disk and placed into the cache. If, however, after a solid hour or two of steady database time, the procedure cache hit rate does not increase to desirable levels, you should look into the possibility of increasing the amount of memory allocated to the cache.
Correction	First, ensure SQL Server is configured to use as much physical memory as possible by checking the max server memory configuration option. Also, consider increasing your SQL Server min memory parameter to allocate more memory to SQL Server. (Note that to obtain optimal values for these parameters, an option is to install more physical RAM to your server.) Check for any large objects that are pinned in memory that could possibly be removed.

Procedure/SQL Cache Summary

The SQL Server procedure/SQL cache holds often-referenced code object definitions and ad-hoc SQL. The Procedure/SQL Cache Summary lists each type of code object, the amount of memory each code type (trigger, ad-hoc SQL, etc.) consumes, and a total count of each type of code object.

Location	Microsoft SQL Server Performance Analyst Statistics > Memory View (SQL Server Performance Analyst) > Procedure Cache Tab
Metrics	Seeing a lot of memory devoted to ad-hoc SQL plans might indicate an environment where sessions are submitting a lot of SQL requests. Many SQL Server DBAs like to control code submissions through stored procedures.

Procedure/SQL Cache Detail

Beginning with SQL Server version 7.0, other SQL-related code objects might be placed in memory for reuse. Once SQL Server parses through and places a set of SQL of program code in memory, response time can be increased for subsequent calls to the same set of SQL or SQL code object. The Procedure/SQL Cache Details section displays the top 1000 objects in the procedure/SQL cache. The table below describes the information available in the Procedure/SQL Cache Details section on the Procedure Cache tab of the Memory Detail:

Information	Description
Object Type	The type of object (ad hoc SQL, etc.)
Cache Type	The category of SQL code inside the cache (executable plan, etc.)
Object ID	The numeric ID of the object.
Object Name	The name of the object (if applicable).
Database ID	The numeric ID of the database.
Database	The database where the code originated.
User ID	The numeric ID of the user.
User	The owner of the object or code.
Use Count	The number of uses for the object.
SQL Bytes	The amount of SQL bytes used by the code object.
Size (KB)	The size used by the object in kilobytes.
SQL	The actual SQL statement or code being executed.

Location	Microsoft SQL Server Performance Analyst Statistics > Memory View (SQL Server Performance Analyst) > Procedure Cache Tab
Metrics	Seeing many objects of the ad hoc SQL type might indicate an environment where sessions are submitting a lot of SQL requests. Many SQL Server DBAs like to control code submissions through stored procedures.

Log Cache Tab

The Log Cache tab includes the following sections:

• Database Log Cache Ratios
• Log Cache Details

The following statistic, available on this page, duplicates a statistic on the Key Ratio Analysis Pane of the Home View (SQL Server Performance Analyst) page:

• Log Cache Hit Ratio

Database Log Cache Ratios

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. Note that some databases with inactive logs will not display a log cache hit ratio.

Location	Microsoft SQL Server Performance Analyst Statistics > Memory View (SQL Server Performance Analyst) > Log Cache Tab
Correction	A low percentage on this statistic is not necessarily a bad sign, as it is possible that the information needed from the log will not be readily available in memory.

Log Cache Details

Before ever writing transactions to disk, the log manager of SQL Server formats everything in memory. This area of memory is known as the log cache. The log writer of SQL Server moves through the log caches when transactions are committed (as well as other events) and flushes each cache out to disk. SQL Server also reads from the cache when log records are needed. The table below describes the information available in the Log Cache Details section on the Log Cache tab of the Memory Detail:

Information	Description
Database	The name of the SQL Server database.
Log Cache Reads	The number of reads from the log cache.
Log Flushes	The number of times data was flushed from the log to disk.
Log Flush Waits	The number of times the log had to wait before flushing data to disk.
Log Flush Wait Time	The amount of time the log waited before flushing data to disk in milliseconds.
Log Growths	The number of times the log had to physically grow in size to meet the need for more space.
Log Shrinks	The number of times the log contracted in physical size.

Location	Microsoft SQL Server Performance Analyst Statistics > Memory View (SQL Server Performance Analyst) > Log Cache Tab
Metrics	Seeing high amounts of wait time for log flushes could indicate a bottleneck at the disk level. A log that shows high number of growths likely indicates an undersized log. While automatic growth can alleviate out-of-space conditions, many growth operations can slow down overall operations. It is better to have a properly sized transaction log that allows SQL Server to continually enlarge it in size when needed.
Correction	Consider relocating logs showing high amounts of wait time to faster disks. For logs showing high numbers of growths, permanently enlarge the log(s) via an ALTER DATABASE command that will resize the log files.