Chat now with support
Chat mit Support

Foglight for SQL Server (Cartridge) 5.7.5.30 - User Guide

Managing SQL Server Database Systems Introduction to this Guide Using Foglight for SQL Server Monitoring Business Intelligence Services Administering Foglight for SQL Server Glossary Reference

Monitoring SQL Performance without SQL PI configured

Viewing Metrics
The History section view is divided into two sections that are correlated to each other:
•
Resource consumption charts — This section displays data in five different charts:
•
Workload chart — Displays the instance resource activity over the selected time frame by emphasizing the resources by colors.
•
Baseline chart — Displays the instance workload compared to the baseline over time.
•
Breakdown chart — Activity of the instance by second.
•
Resource Breakdown Pie chart — Displays the resource breakdown usage by % of the total instance activity.
•
All wait events pop up — Displays details of the wait events that the instance is waiting on during the selected time range.
•
Overview section- Displays a graphical representation of the metrics highlighted in the Workload related Metrics table below.
•
Workload related Metrics - A table that displays a variety of resource consumption metrics which can give an in-depth of the instance activity, each resource holds its default metrics.
* 
NOTE: By using the metric selector other metrics can be added to the table.
Blocking History
The Lock Analysis displays all locks that took place within the selected time range.
This pane comprises the following sections:
•
Locks Activity — a chart that displays all of the lock wait events, plotted over time. This chart can be viewed in either of the following views:
•
Baseline — displaying only lock wait events
•
Breakdown — displaying both lock and non-lock wait events
•
Blocks exceeding a predefined threshold — displays blocks that reached or exceeded the minimal duration, in seconds, which a block should reach or exceed in order to be collected. By selecting a row from the table a pop-up with a lock map of this specific time will be presented.
* 
NOTE: The minimal duration's length is defined using the Locks view in the Databases Administration dashboard. To access this view, click the In-context actions button at the upper right side of the screen and then select Agent settings.
Viewing SQL Statement Details
The SQL Statements table displays the top SQL statements that experienced the longest elapsed time during the selected time range. The number of SQL statements displayed is shown at the table's title, together with the field used for ordering the SQL statements. Selecting a row displays the workload and executions of the selected SQL statement on the SQL Activity section below. For details, see SQL Activity section.
Selecting the SQL Text in the SQL Statements tree panel displays a page that allows viewing detailed information about the selected SQL statement. For details, see SQL Activity section.
* 
NOTE: SQL Statement settings can be changed through Administration > Top SQL Statements.
To view SQL statement details:
1
In the Instance view, expand the Instance View.
2
Click SQL Statements.
 
Table 5. The Top SQL Statements table contains the following columns:
Column
Description
SQL Text
The SQL text for the selected SQL statement.
Object Name
The name of the object owning the SQL. For example: view, procedure or function.
% CPU Workload
The relative share, in percent, which the selected SQL statement occupies within the instance’s total CPU consumption.
Executions Started
The number of the selected SQL statement’s executions that started during the selected time range.
SQL % Hit Rate
The hit rate, in percent, of the selected SQL statement.
Elapsed Time
The total time consumed for carrying out the SQL statement executions.
CPU Time
The total CPU time consumed for carrying out the SQL statement executions.
Total I/O Operations
The total number of reads, both physical and logical, carried out by the selected SQL statement.
SQL Activity section
Located below the table of Top SQL Statements by Elapsed Time, the SQL Activity section of the pane displays the workload and executions of the SQL statement you selected in the TOP SQL Statements table.
These metrics are visually represented in two graphs:
•
CPU Workload (Average Active Sessions) — Displays the activity (system workload), plotted over the specified time range. This graph compares the following metrics:
•
Overall CPU Workload — the workload incurred by the total top SQL statements displayed in the Top SQL Statements by Elapsed Time table.
•
Selected SQL CPU Workload — the average number of active sessions incurred by the selected SQL statement.
•
Executions — Displays the number of times the selected SQL statement was executed during the selected time range. Alternatively, it is possible to display the number of executions carried out by the total SQL statements in the Top SQL statements table.
Viewing specific SQL statement details
To view detailed graphic and textual data about a specific SQL statement, select the SQL statement from the statements listed in the Instance View.
This page contains the following panes:
SQL activity
•
The SQL Activity pane displays the CPU workload and executions of the selected SQL statement, as well as all SQL statements displayed on the Top SQL Statements table. This pane is identical to the SQL Activity section on the Top SQL Statements panel.
SQL text
•
The SQL Text pane displays the short text of the selected SQL handle, and allows viewing the wider context of this text, namely: the SQL handle's full text, the entire batch and the SQL Statement's execution plan.
•
This pane displays the short SQL statement, whose maximum size is by default 256 characters. In addition, the pane includes the following buttons:
•
View Full Text — Displays a pop-up with the SQL handle's full text
•
View Batch — Displays a pop-up with the full text of the entire batch to which the SQL handle belongs
•
View Plan — Displays a pop-up that shows the SQL statement's execution plan and allows downloading the execution plan (.sqlplan) file.
SQL metrics
 
Table 6. The SQL Metrics pane contains a table with the selected SQL statement's metrics:
Column
Description
Object Name
The name of the object from which the SQL statement was executed.
Executions
The number of executions that took place on this object since it was brought into the library cache.
NOTE: The Executions parameter does not provide any informational value unless the Total option is selected. When selecting the option Perf Execution, the value of this parameter is always one (1).
Elapsed Time
The total time consumed for carrying out the SQL statement executions.
CPU Time
The total CPU time consumed for carrying out the SQL statement executions.
CLR Time
If the statement executed one or more CLR objects, the time consumed for these executions.
Total I/O Operations
The total number of reads, both physical and logical, carried out by the selected SQL statement.
Pie Charts
The Pie Charts pane includes the pie charts listed below, which display the activity during the selected time range for the selected metrics.
•
Total CPU Time — The total CPU time consumed for executing the selected SQL statement, compared with the CPU time consumed for executing all of the SQL statements
•
Executions — The number of times the SQL script executed for a the selected SQL handle, compared with the total SQL statements
•
Logical Reads — The total number of logical reads for the selected SQL statement, compared with the total SQL statements
•
Physical Reads — The total number of physical reads for the selected SQL statement, compared with the total SQL statements

Monitoring SQL Performance with SQL PI Configured

The SQL Performance Investigator provides the ability of a more in-depth analysis and investigation of the Instance activity and resource consumption.
SQL PI provides the ability to investigate and analyze the resource consumption of the instance by using:
•
Performance tree. See Performance Tree
•
History. See Viewing Historical Metrics
•
Change Tracking. See Viewing Change Tracking
•
Execution plan. See Viewing Execution Plans
Performance Tree
The performance tree provides iterative (up to three levels) access to any of the key dimensions associated with SQL Server database activity, based on the OLAP multidimensional model and an instance view of the instance activity. Domain nodes offer a hierarchical view of all types of SQL Server activity characteristics.
Selecting a dimension from the tree determines what subset of activity is displayed. Iterative drill-down into domains of interest provides increasingly refined focus and diagnosis.
For example, to begin the investigation by first identifying the most active DB User, follow the steps described below:
1
Select the DB Users node, to display the most active database users in the selected time range. That is, the database users who consumed the highest amount of the selected resource.
2
Select the first user, to focus the entire window on that user's activity.
3
Identify the most demanding SQL statement that this specific user has executed, by expanding the user node and then selecting the SQL statement dimension node.
This displays the most active SQL statements executed by this user.
4
Select a specific SQL statement to focus the entire window on the selected statement's activity.
5
Select Client Machines under the selected SQL Statement, to view the computers on which the statement was run.
In a similar manner, such iterative drilldowns can be carried out into any SQL Server dimension of interest, to gain a complete understanding of the causes of its behavior.
The default SQL Server dimensions are as follows:
•
SQL Statements — The executed SQL commands.
•
TSQL Batches — T-SQL Batch is the set of T-SQL (Transact-SQL) commands that are sent to execution together, usually corresponding to a single business transaction. TSQL Batch can end with a GO command, and execute database components such as:
•
Stored Procedures — Sets of T-SQL code that are stored and compiled in a SQL Server database.
•
Functions — Saved T-SQL routines that return a single value or a set of columns and rows.
•
Triggers — Batches that fire after the execution of a DML (Data Manipulation Language) statement.
•
Databases — The database context in which the session carried out its operations. A session may switch to numerous databases within its lifetime.
•
Programs — Name of a program that connects to SQL Server and executes the SQL statements, as specified in the Program column of the SQL Server session information.
•
OS Users — Operating system users running the client program.
•
Client Machines — The machines on which the client executable (connected to SQL Server) is running.
•
DB Users — SQL Server login names used for logging in to SQL Server.
•
Context Info — Optional trace information that a session can create using the SET CONTEXT_INFO command. Context Info allows users to associate up to 128 bytes of binary information with the current session or connection. While the technical implementation is binary, users commonly associate keywords or strings of text with their sessions.
•
Command Types — Executed SQL command type (for example, INSERT and SELECT).
•
Sessions — Presents the top sessions which consumed the highest active time during the selected time frame.
* 
NOTE: Session data is kept for 3 days.
•
Locked Objects — Displays the objects that experienced locks, the duration of the lock and the type of the lock. The object view can be sorted by selecting a dimension in the performance tree, for example: by selecting a database name only the locked object that occurred on that database will be displayed.
In the SQL Statements and SQL Batches dimension there is an option to view information regarding the selected statement\batch through the top SQL statements\Top SQL batches table top:
•
View Full Text — View full text of the statement\batch selected.
•
Analyze Plan — View execution plan analytics of the statement\batch selected for more information regarding the execution plan. See Viewing Execution Plans
•
Tune SQL — Export the statement\batch selected to a file that can be opened by Dell's SQL Optimizer toll.
•
Compare — Retrieve the selected statement\batch to the compare dashboard for comparing it to other activities. For more information regarding the Compare, see Comparing Performance.
Viewing Historical Metrics
The History section view is divided into two sections that are correlated to each other:
•
Resource consumption charts — This section displays data in five different charts:
•
Workload chart — Displays the instance resource activity over the selected time frame by emphasizing the resources by colors.
•
Baseline chart — Displays the instance workload compared to the baseline over time.
•
Breakdown chart — Activity of the instance by second.
•
Resource Breakdown Pie chart — Displays the resource breakdown usage by % of the total instance activity.
•
All wait events pop up — Displays details of the wait events that the instance is waiting on during the selected time range.
•
Overview section- Displays a graphical representation of the metrics highlighted in the Workload related Metrics table below.
•
Workload related Metrics - A table that displays a variety of resource consumption metrics which can give an in-depth of the instance activity, each resource holds its default metrics.
Selecting each dimension in the performance tree together with a specific resource effects the data displayed for each Level.
For example, by selecting the Lock resource the Instance view dimension will present only locks related data, the SQL Statements dimension will present only the statements that were experiencing locks and DB users the were experiencing locks and so on through all the dimensions and resources.
Resource Toolbar Options
 
Table 7. Resource Toolbar options and the instance metrics displayed.
Resource Toolbar Option
Instance Level Default Metrics
Dimension Type and Dimension Value Default Metrics
Workload Panel
The Workload resource is selected by default
•
Active Time
•
Executions
•
CPU Usage
•
Average SQQL Response Time
•
Wait Time Percent
•
Logins Rate
•
Batches Rate
•
Active Time
•
Executions
•
CPU Usage
•
Average SQL Response Time
•
Wait Time Percent
•
Row Count
CPU
The CPU panel allows you to review the CPU usage and CPU wait events.
•
CPU Usage
•
CPU Wait
•
Host Run Queue Length
•
Host CPU Usage
•
Host Non SQL CPU utilization
•
CPU Wait
•
CPU Usage
•
CPU Time
•
CPU Wait
•
CPU Usage
I/O
The I/O panel allows you to review the I/O-related data, such as wait events and physical reads and writes.
Default metrics displayed:
•
I/O Completion
•
I/O Data Page
•
Latch Buffer
•
Host Disk Utilization
•
Logical Reads
•
Deferred Task Worker
•
Physical Reads
•
I/O Wait
•
Writes
•
Host Disk Queue Length
•
I/O Bulk Load
•
I/O Completion
•
I/O Data Page
•
Latch Buffer
•
Logical Reads
•
Deferred Task Worker
•
Physical Reads
•
I/O Wait
•
Writes
•
I/O Bulk Load
Memory
The Memory panel allows you to view memory related performance data, such as wait events and physical reads and writes.
Default metrics displayed:
•
Total Instance Memory
•
Pages out
•
Pages in
•
Buffer Cache Hit Ratio
•
Memory Growth Pressure
•
% Total Server Memory
•
Target Instance Memory
•
Page Life Expectancy
•
Memory Wait
•
Procedure Cache Hit Ratio
•
Host Physical Memory
•
Cache Hit Ratio
•
Memory Wait
Network
The Network panel displays the network related wait events.
Default metrics displayed:
•
Network IPC
•
Network Mirror
•
Network Wait
•
Packets received
•
Packets sent
•
Instance Packets Total
•
Network HTTP
•
Network IPC
•
Network Wait
•
Network Mirror
•
Network I/O
•
Network HTTP
Lock
The Lock panel displays the database’s lock related wait events.
Default metrics displayed:
•
Lock Update
•
Lock Shared
•
Average Lock Duration
•
Lock Wait
•
Lock Intent
•
Lock Timeouts
•
Lock Exclusive
•
Deadlocks
•
Lock Schema
•
Lock Bulk Update
•
Lock Update
•
Lock Shared
•
Lock Wait
•
Lock IntentLock Exclusive
•
Lock Bulk Update
•
Lock Schema
Latch
The Latch panel displays the database’s latch related waited events.
Default metrics displayed:
•
Latch Savepoint
•
Latch Wait
•
Internal Catch Latch
•
Latch Savepoint
•
Latch Wait
•
Internal Catch Latch
Log
The Log panel displays the database’s log related wait events.
Default metrics displayed:
•
Log Wait
•
Log Write
•
Log Buffer
•
Log Other
•
Log Synchronization
•
Log Wait
•
Log Write
•
Log Buffer
•
Log Other
•
Log Synchronization
CLR
The CLR panel displays the database’s wait events occurring as a result of statements waiting for CLR code execution to complete.
Default metric displayed:
•
CLR Wait
•
CLR Wait
Remote Provider
The Remote Provider panel displays the databases wait events that take place when various processes are waiting either for a remote OLEDB call to complete or for DTS synchronization.
Default metrics displayed:
•
Remote Provider Wait
•
Distributed Transaction
•
OLEDB Provider Full Text
•
Remote Provider Wait
•
Distributed Transaction
•
OLEDB Provider Full Text
Other
The Other panel allows monitoring the time spent waiting for the completion of miscellaneous operations. That is, operations that cannot be classified into any other wait categories.
Default metrics displayed:
•
Parallel Coordination Wait
•
Other Wait
•
Database Replication
•
Always On
•
Other Wait
•
Hosted Component
•
Synchronous Task
•
Service Broker
•
Other Miscellaneous
•
Full Text Search
 
XTP
•
XTP Wait
•
XTP Transaction Wait
•
XTP Procedure Wait
•
XTP Log write Wait
•
XTP Miscellaneous Wait
•
XTP Transactions
•
XTP Read Only Transactions
•
XTP Aborted Transactions
•
XTP Failed due to Validation
•
XTP Failed Due to Dependencies
•
XTP Failed due to Unique Constraint
•
XTP Failed due to Write Conflicts
•
XTP Writes
•
XTP Transactions on Durable Tables
•
XTP Wait
•
XTP Transaction Wait
•
XTP Procedure Wait
•
XTP Log write Wait
•
XTP Miscellaneous Wait
Viewing XTP Data
The XTP pane displays information gathered by SQL Performance Investigator and XTP-related DVMVs. Metrics displayed are:
Table 8. XTP metric definitions
Metric
Description
XTP Transaction Wait
Time spent waiting for in memory transaction events.
XTP Wait
Time spent waiting for in memory OLTP (XTP) related events.
XTP Miscellaneous Wait
Time spent waiting for in memory miscenllaneous events. Those events are not related to transactions, log or natively compiled stored procedures.
XTP Log write Wait
Time spent waiting for in memory log and checkpoint related events.
XTP Procedure Wait
Time spent waiting for events related to natively compiled stored procedures.
XTP Transactions
The total number of transactions that have run in the In Memory OLTP database engine.
XTP Failed due to Unique Contraint
Total number of unique constraint violations.
XTP Writes
Total number of megabytes written to the In Memory OLTP log records.
XTP Failed due to Write Conflicts
The number of conflicts between transactions while writing a row which lead to aborts.
XTP Transactions on Durable Tables
Total number of transactions that require log IO. Only considers transactions on durable tables.
XTP Failed due to Dependencies
The number of times a transaction aborts because a transaction on which it was dependent aborts.
XTP Read Only Transactions
The number of read only transactions.
XTP Failed due to Validation
The number of times a transaction has aborted due to a validation failure.
XTP Aborted Transactions
The total number of transactions that were aborted, either through user or system abort.
Monitoring and Tuning the Highest XTP Wait Events
In general, the XTP option on the SQL performance dashboard should show none or almost none XTP wait consumption. The most likely way to observe any XTP activity would be to show background processes.
However, in case there are significant waits related to XTP activity; use the Resource Breakdown pie chart. Clicking on the "All Wait Events" anchor allows you to get the actual wait counts. This drilldown display also allows you to compare the XTP wait events in comparison with other waits detected by SQL PI.
Investigation XTP Transaction Failures
Use the XTP related Metrics:
•
xtp_aborted_transactions
•
xtp_failed_due_to_validation
•
xtp_failed_due_to_dependencies
•
xtp_failed_due_to_unique_constraint
•
xtp_failed_due_to_write_conflicts
These metrics allow you to find if there were XTP transactions that failed. They also enable research on the top reasons of failure. Clicking on each of these values allows you to investigate the amount of failure over time.
 
Viewing Change Tracking
The change tracking tool is an integrated monitoring mechanism. It periodically tracks changes in environments and activity that can potentially influence system performance and enables the user to view correlation between occurrences of changes and SQL Server's activity and behavior patterns.
Use the Categories filter mechanism to refine the set of displayed change tracking occurrences. These categories are displayed on the right hand side of the pane and include:
Accounts
For each database in an MSSQL instance: Users and permissions changes.
Database Configuration
For each database in an MSSQL instance: File group parameters; files (data, log and control) location, size and status.
Database Objects
For each database in an MSSQL instance: Any schema object: Tables and indexes and their partitions and sub-partitions; clustered and non-clustered indexes; constraints; views; functions; procedures; types and triggers (for example, altering a table; altering or rebuilding an index).
Execution Plan
SQL PI captures SQL statements whose execution plans have changed. When an execution plan has (unintentionally) changed, the outcome can result in SQL performance degradation. Therefore it is essential you investigate the change, by clicking the Properties button to graphically display the execution plan before and after the change, to verify that the nature and outcome of the change improve SQL performance.
Master Configuration
Master database: file groups, files, instance configuration, and databases defined in the instance. The master database houses all critical information such as server specific configuration information, user login accounts, running processes, system error messages, system stored procedures, and the location of the primary files that contain the initialization information for the user databases.
System Configuration
Hardware and operating system configuration (for example, disks and network interfaces (NIC); amount of RAM; CPU count; device installations and swap space allocation).
* 
NOTE: Selecting a specific dimension from the Performance Tree will display only the changes made in this specific dimension. The instance view displays all changes made.
Viewing Execution Plans
This view presents the execution plan of a selected SQL\batch and the cost of it. A Historical execution plan can be generated and will present any two views of the execution plan.
There are two ways to access the Execution Plan History dialog:
•
From a Change Tracking pane by clicking on a row that displays Execution Plan change.
•
From History by selecting the statement or batch and by clicking the 'Analyze' button in the top of the table.
The execution plan dashboard displays the following content.
Top Bar
Date — Displays all the execution date and time of the selected statement\batch
Type — Displays the type of the execution plan.
Plan handle — Displays the SQL handle on the execution plan.
Plan analysis section
The execution plan is displayed in tabs which present the cost of each operation and object of each step of the plan.
By pressing the Generate Plan button the execution plan is generated and can be viewed by clicking on Compare Plan. This opens a pop up with a compression of all available execution plans of the selected statement.
Blocking History
Provides the same view as without SQL PI configured. See Blocking History.
Comparing Performance
Use the SQL PI Compare where differences in period activity illustrate underlying performance and monitoring issues. It helps you determine whether a comparison occurrence is an isolated incident or a sign of a potentially significant performance problem.
Compare can be access from the Performance tree.
The comparison can be of whole instances or selected dimension breakdowns (such as user or SQL). Use Compare to address questions such as:
•
What caused a specific activity?
•
What were the resource and load demands of today's instance activity compared with that of a previous day?
•
Is an instance imposing different load levels now than previously?
•
How do we explain the difference in a SQL performance compared with a previous period?
•
What are the differences in program characteristics over two periods that caused different performance?
•
After identifying the different resource usage, you can use compare to identify what caused this difference: Usage pattern? Different SQL behavior? Environmental problem?
The upper panes graphically displays the Workload or any other selected resource.
The middle Activity section displays the dimensions which were significant elements of the difference. Expanding the individual lines displays the dimension members which caused the difference and the composing metrics whose differences exceed the specified threshold.
Use the set of performance related metrics (defined by the chosen resource) to help you explain the difference in resource consumption; for example, a rise in I/O Wait might be explained by a rise in the quantity of physical reads.
Use the Comparison Parameters section in order to enter all the desired information for the cooperation. For example, time range, instance, dimension, time frames, resource.

Reviewing Memory Usage

The Memory drilldown provides access to graphs that display details of memory usage for the currently monitored server.
Viewing the Memory Summary
The Summary panel allows monitoring how the SQL Server instance is using its physical memory. This panel features charts that allow you to perform a full investigation of memory-related issues on the server.
Use this panel to review any possible memory-related performance issues, and then go to the Buffer Cache or Plan Cache panels for an in-depth investigation of such issues.
Figure 10. The Summary panel
The Summary panel allows you to perform the tasks described in the following topics:
•
Monitoring Status
•
Investigating Resource Pool Memory Issues
•
Investigating Cached Memory Issues
Monitoring Status
The Status chart displays the main areas used by SQL Server for carrying out database operations within memory. Use these charts to review the efficiency of the memory management, as reflected in the amount of memory allocated to each area.
Memory pressure is defined as the (100-100*Total Server Memory/Target Server Memory) or in other words, 1-accepted memory/requested memory by the SQL server. The percentage division shows the current memory pressure and the amount of memory used or free by SQL Server or entire server memory
Clicking on either of the Machine RAM or the SQL Server metrics allow you to investigate how these values changes over time.
Page Life expectancy is the length of time that a database page will stay in the buffer cache without references. A value too low for your system indicates that pages are being flushed from the buffer pool too quickly. The longer a page can stay in the buffer pool and be read from memory the better.
The Memory Used metric indicates the total percentage of dynamic memory, within the host's physical RAM, that the server is currently consuming.
Alerts regarding memory pressure may indicates that:
•
More RAM Memory is required for the server.
•
The processes running on the SQL server may consume more memory than possible for a healthy server.
The chart at the bottom of this section displays the metrics over time.
Selecting the Configuration Parameters link allows you to view the memory related options on the server.
Investigating Resource Pool Memory Issues
The Resource Pools (RP) metrics are supported only beginning with SQL Server v2008.
The middle division of the Memory Summary dashboard provides metrics which may help you to prevent resource pool memory related errors. For example:
•
There is insufficient system memory in resource pool <RP Name> to run this query.
•
There is insufficient memory available in the buffer pool.
The first chart in the Resource Pool section displays the resource pools available for application and server usage and alerts memory utilization if occurs on any resource pool. The values are grayed out when the resource governor is disabled.
The list contains the following values:
•
An alert icon for the RP utilization.
•
The name of the RP.
•
A bar showing the usage within the RP.
•
The total memory potentially available for the RP.
Two resource pools "internal" and "default" are created when SQL Server is installed. Clicking on the resource pool displays how the usages changes over time.
Further drilling into the databases related to this resource pool is also available as of SQL Server v2014, for databases which have the In-Memory feature enabled. When using In-Memory databases, it is highly recommended to define a dedicated resource pool.
The lower part of the Resource Pool section provides metrics on up to eight resource pools over time. Click the Properties tab to view the definition parameters of the resource pools on the server
Investigating Cached Memory Issues
The Memory Plan metrics provide data on cache-related issues.
Slow running processes and Hit ratio issues
Plan cache is a component of SQL memory that stores query plans for re-use. When a query is issued against SQL, the optimizer attempts to re-use a cached plan if the traits of the query permit - but can only do so if that plan resides in cache, otherwise it needs to compile a new plan.
Low hit ratio may indicate a lack of memory resources, a new plan might also be compiled even if a similar plan exists in cache when:
•
There are changes in the schema.
•
A query is running parallel that may have run serially before.
•
Parameters have changed.
Plan compilations are expensive though; if plans are flushed from cache only to be recompiled afterwards because of size constraints even more memory resources are consumed.
Selecting the Plan Cache hit ratio allows you to investigate changes over time. Further drilling down leads to the Plan Cache dashboard.
Buffer cache (Also called Buffer Pool) is the place in system memory that holds data and index pages read from disk. This has two purposes:
•
To modify pages according to INSERT and UPDATE statements. Those pages are marked as "dirty" and are flushed to disk when a checkpoint is performed.
•
To increase response time when retrieving the same data.
Buffer Cache size is determined among other things by server memory and the target server memory specified in the Max Server Memory parameter. When that threshold is reached and SQL Server needs to read more pages, previously cached pages are discarded.
A low hit ratio may indicate lack of memory. It also indicates that the Buffer Cache cannot improve performance by reducing IO operations. As such, the Buffer Cache size can correlate to improved performance during a heavy workload.
Improving Buffer Cache hit ration in SQL Server 2014 is also possible by setting a Buffer Pool Extension on an SSD drive.
The hit rate for this cache should normally be above 90%. Selecting the Buffer cache hit ratio bar allows you to investigate these metrics over time. Further drilling down leads to the Buffer Cache dashboard.
Investigating In-Memory OLTP (XTP) Performance
New in SQL Server 2014, In-Memory OLTP can significantly improve OLTP database application performance.
The Memory Usage of In-Memory (XTP) indicates the amount of data used by memory optimized tables. A large percentage indicates a server that heavily uses In-Memory OLTP features. It is recommended that you dedicate a specific resource pool to databases containing In-Memory OLTP objects hence ensuring proper allocation of memory resources among applications on the SQL Server.
The Oldest XTP Transaction currently active on the server indicates issues regarding long running XTP transactions. Any value over 20 seconds is regarded a warning to an un-healthy behavior on the server. Long-running transactions should be avoided with memory-optimized tables. Such transactions increase the likelihood of conflicts and subsequent transaction terminations. A long-running transaction also defers garbage collection.
The longer a transaction runs, the longer In-Memory OLTP keeps recently deleted row versions, which can decrease lookup performance for new transactions.
Selecting the Oldest XTP transaction displays general information regarding the transaction and the session that had initiated it.
Investigating Major and Minor Memory Areas
The Major Memory Areas chart displays the amount of memory allocated to the Buffer cache, Plan cache, free memory and XTP. Use this chart to ensure that:
•
The buffer cache has appropriate size of memory, which is sufficient to hold the most frequently used data pages.
•
The free memory value is not consistently close to zero, which may indicate use of inefficient queries or shortage of SQL Server cache memory.
•
The plan cache has appropriate size of memory, which is sufficient to hold the most frequently used execution plans.
•
The XTP memory has no significant peaks that might mean there are memory utilization issues during the day.
The Minor Memory Areas chart displays the main areas SQL Server uses for carrying out database operations within memory. The display is broken down into each of the main cache areas:
•
Lock Area — memory allocated to keeping track of locks.
•
Optimizer Code — a work area for the SQL Server optimizer. For details, see glossary definition of Optimizer.
•
Sort, Hash, Index — memory used for each of these operations.
•
User Connections — memory allocated to keeping track of each connection's attributes.
Use this chart to review the efficiency of the memory management, as reflected in the amount of memory allocated to each area.

Viewing the Memory Summary

The Memory drilldown provides access to graphs that display details of memory usage for the currently monitored server.
The Summary panel allows monitoring how the SQL Server instance is using its physical memory. This panel features charts that allow you to perform a full investigation of memory-related issues on the server.
Use this panel to review any possible memory-related performance issues, and then go to the Buffer Cache or Plan Cache panels for an in-depth investigation of such issues.
Figure 10. The Summary panel
The Summary panel allows you to perform the tasks described in the following topics:
•
Monitoring Status
•
Investigating Resource Pool Memory Issues
•
Investigating Cached Memory Issues
Monitoring Status
The Status chart displays the main areas used by SQL Server for carrying out database operations within memory. Use these charts to review the efficiency of the memory management, as reflected in the amount of memory allocated to each area.
Memory pressure is defined as the (100-100*Total Server Memory/Target Server Memory) or in other words, 1-accepted memory/requested memory by the SQL server. The percentage division shows the current memory pressure and the amount of memory used or free by SQL Server or entire server memory
Clicking on either of the Machine RAM or the SQL Server metrics allow you to investigate how these values changes over time.
Page Life expectancy is the length of time that a database page will stay in the buffer cache without references. A value too low for your system indicates that pages are being flushed from the buffer pool too quickly. The longer a page can stay in the buffer pool and be read from memory the better.
The Memory Used metric indicates the total percentage of dynamic memory, within the host's physical RAM, that the server is currently consuming.
Alerts regarding memory pressure may indicates that:
•
More RAM Memory is required for the server.
•
The processes running on the SQL server may consume more memory than possible for a healthy server.
The chart at the bottom of this section displays the metrics over time.
Selecting the Configuration Parameters link allows you to view the memory related options on the server.
Investigating Resource Pool Memory Issues
The Resource Pools (RP) metrics are supported only beginning with SQL Server v2008.
The middle division of the Memory Summary dashboard provides metrics which may help you to prevent resource pool memory related errors. For example:
•
There is insufficient system memory in resource pool <RP Name> to run this query.
•
There is insufficient memory available in the buffer pool.
The first chart in the Resource Pool section displays the resource pools available for application and server usage and alerts memory utilization if occurs on any resource pool. The values are grayed out when the resource governor is disabled.
The list contains the following values:
•
An alert icon for the RP utilization.
•
The name of the RP.
•
A bar showing the usage within the RP.
•
The total memory potentially available for the RP.
Two resource pools "internal" and "default" are created when SQL Server is installed. Clicking on the resource pool displays how the usages changes over time.
Further drilling into the databases related to this resource pool is also available as of SQL Server v2014, for databases which have the In-Memory feature enabled. When using In-Memory databases, it is highly recommended to define a dedicated resource pool.
The lower part of the Resource Pool section provides metrics on up to eight resource pools over time. Click the Properties tab to view the definition parameters of the resource pools on the server
Investigating Cached Memory Issues
The Memory Plan metrics provide data on cache-related issues.
Slow running processes and Hit ratio issues
Plan cache is a component of SQL memory that stores query plans for re-use. When a query is issued against SQL, the optimizer attempts to re-use a cached plan if the traits of the query permit - but can only do so if that plan resides in cache, otherwise it needs to compile a new plan.
Low hit ratio may indicate a lack of memory resources, a new plan might also be compiled even if a similar plan exists in cache when:
•
There are changes in the schema.
•
A query is running parallel that may have run serially before.
•
Parameters have changed.
Plan compilations are expensive though; if plans are flushed from cache only to be recompiled afterwards because of size constraints even more memory resources are consumed.
Selecting the Plan Cache hit ratio allows you to investigate changes over time. Further drilling down leads to the Plan Cache dashboard.
Buffer cache (Also called Buffer Pool) is the place in system memory that holds data and index pages read from disk. This has two purposes:
•
To modify pages according to INSERT and UPDATE statements. Those pages are marked as "dirty" and are flushed to disk when a checkpoint is performed.
•
To increase response time when retrieving the same data.
Buffer Cache size is determined among other things by server memory and the target server memory specified in the Max Server Memory parameter. When that threshold is reached and SQL Server needs to read more pages, previously cached pages are discarded.
A low hit ratio may indicate lack of memory. It also indicates that the Buffer Cache cannot improve performance by reducing IO operations. As such, the Buffer Cache size can correlate to improved performance during a heavy workload.
Improving Buffer Cache hit ration in SQL Server 2014 is also possible by setting a Buffer Pool Extension on an SSD drive.
The hit rate for this cache should normally be above 90%. Selecting the Buffer cache hit ratio bar allows you to investigate these metrics over time. Further drilling down leads to the Buffer Cache dashboard.
Investigating In-Memory OLTP (XTP) Performance
New in SQL Server 2014, In-Memory OLTP can significantly improve OLTP database application performance.
The Memory Usage of In-Memory (XTP) indicates the amount of data used by memory optimized tables. A large percentage indicates a server that heavily uses In-Memory OLTP features. It is recommended that you dedicate a specific resource pool to databases containing In-Memory OLTP objects hence ensuring proper allocation of memory resources among applications on the SQL Server.
The Oldest XTP Transaction currently active on the server indicates issues regarding long running XTP transactions. Any value over 20 seconds is regarded a warning to an un-healthy behavior on the server. Long-running transactions should be avoided with memory-optimized tables. Such transactions increase the likelihood of conflicts and subsequent transaction terminations. A long-running transaction also defers garbage collection.
The longer a transaction runs, the longer In-Memory OLTP keeps recently deleted row versions, which can decrease lookup performance for new transactions.
Selecting the Oldest XTP transaction displays general information regarding the transaction and the session that had initiated it.
Investigating Major and Minor Memory Areas
The Major Memory Areas chart displays the amount of memory allocated to the Buffer cache, Plan cache, free memory and XTP. Use this chart to ensure that:
•
The buffer cache has appropriate size of memory, which is sufficient to hold the most frequently used data pages.
•
The free memory value is not consistently close to zero, which may indicate use of inefficient queries or shortage of SQL Server cache memory.
•
The plan cache has appropriate size of memory, which is sufficient to hold the most frequently used execution plans.
•
The XTP memory has no significant peaks that might mean there are memory utilization issues during the day.
The Minor Memory Areas chart displays the main areas SQL Server uses for carrying out database operations within memory. The display is broken down into each of the main cache areas:
•
Lock Area — memory allocated to keeping track of locks.
•
Optimizer Code — a work area for the SQL Server optimizer. For details, see glossary definition of Optimizer.
•
Sort, Hash, Index — memory used for each of these operations.
•
User Connections — memory allocated to keeping track of each connection's attributes.
Use this chart to review the efficiency of the memory management, as reflected in the amount of memory allocated to each area.
Verwandte Dokumente

The document was helpful.

Bewertung auswählen

I easily found the information I needed.

Bewertung auswählen