Monitoring SQL Performance

The SQL Performance page provides the ability to investigate the activity and resource consumption of a selected instance.

There are two levels of metrics available:

• Without SQL PI configured — basic History, lock analysis data, and activity highlights.
• With SQL PI configured — The ability to perform a more in-depth analysis and investigation of the Instance activity and resource consumption by adding a dimension view of the activity, change tracking analysis, execution plan analysis and compare toll.

The SQL Performance page displays the following components:

• The Instance View — Provides the ability to investigate and analyze the resource consumption of the instance.
• The Resource menu — By selecting one of the resources, the resource consumptions charts and the overview section are updated to display only the relevant information regarding the activity of the selected resource.

This section covers the following key areas:

• Monitoring SQL Performance with SQL PI Configured
  
  • Viewing the Activity Highlights
  • Using the Performance Tree
  • Viewing Historical Metrics
  • Viewing the Resource menu
  • Viewing Change Tracking
  • Viewing Execution Plans
  • Comparing Performance
• Monitoring SQL Performance without SQL PI configured
  • Viewing Metrics
  • Blocking History
  • Viewing SQL Statement Details
  • SQL Activity section
  • Viewing specific SQL statement details
  • Pie Charts

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:

• Activity Highlights
• Performance tree
• History
• Change Tracking
• Execution plan

Viewing the Activity Highlights

The Activity Highlights are provided for fast performance analysis and allow users focus on the most significant dimensions that are relevant for the resource selected in the selected time range. This pane comprises the following elements:

• Summary — Summarizes the instance consumption time by % of the total instance activity.
• Activity Highlights table — Displays the activity highlights, resource breakdown usage, and the top wait event that the instance is waiting on within the selected time range.

Using the 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.
• 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\TSQL Batches:

• 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.
• Tune SQL — Export the statement\batch selected to a file that can be opened by Quest’s SQL Optimizer toll.
• Compare — Retrieve the selected statement\batch to the compare dashboard for comparing it to other activities.

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 lock-related data. The SQL Statements dimension will present only the statements that were experiencing locks, and the DB users dimension will present only the users who were experiencing locks, and so on through all the dimensions and resources.

Viewing Resource menu

To review specific data metrics, select one of the specific metrics from the menu. The table below lists which metrics are available under each menu.

The following table displays the metrics available under each resource menu:

Resource menu	Default Metrics Available
Workload	Allows you to review the instance workload by gathering all resources into one view. Default metrics displayed: - Blocked Lock Requests - Latch Wait - Total CPU Usage - Procedure Cache Hit Ratio - Virtual Memory Used - Availability
CPU	Allows you to review the Instance CPU usage and CPU wait events. Default metrics displayed: - Total CPU Usage - CPU Wait
I/O	Allows you to review the I/O-related data, such as wait events and physical reads and writes. Default metrics displayed: - I/O Wait - I/O Bulk Load Wait - I/O Completion Wait - I/O Data Page Wait - Lazy Writes - Checkpoint Pages - Page Splits - Page Life Expectancy - Disk Utilization
Memory	Allows you to view memory related performance data, such as wait events and physical reads and writes. Default metrics displayed: - Page Life Expectancy - SQL Server Cache memory - SQL Server Connections Memory - Target Instance Memory - Total Instance Memory - Memory Wait
Network	Displays the network related wait events. Default metrics displayed: - Network Wait - Network HTTP Wait - Network I/O Wait - Network IPC Wait - Network Mirror Wait
Lock	Displays the lock related wait events of a database. Default metrics displayed: - Blocked Lock Requests - Table Lock Escalation - Lock Requests - Lock Wait - Lock Bulk Update Wait - Lock Exclusive Wait - Lock Intent Wait - Lock Schema Wait - Lock Shared Wait - Lock Update Wait
Latch	Displays the database’s latch related waited events. Default metrics displayed: - Latch Wait - Latch Savepoint Wait
Log	Displays the log related wait events of a database. Default metrics displayed: - Log Flushes - Log Wait - Log Buffer Wait - Log Other Wait - Log Synchronization Wait - Log Write Wait
CLR	Displays the wait events of a database occurring as a result of statements waiting for CLR code execution to complete. Default metrics displayed: - Total CPU Usage - CLR Wait
Remote Provider	Displays the wait events of databases 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 - OLEDB Provider Full Text Wait
XTP	The XTP (Extreme Transaction Processing) menu displays information gathered by the SQL Performance Investigator and XTP-related DMVs. Default metrics displayed: - XTP Transaction Wait - XTP Wait - XTP Miscellaneous Wait - XTP Log write Wait - STP Procedure Wait - XTP Transactions - XTP Failed due to Unique Constraint - XTP Writes - XTP Failed due to Write Conflicts - XTP Tranactions on Durable Tables - XTP Failed Due to Dependencies - XTP Read only Transactions - XTP Failed due to Validation - XTP Aborted Transactions
Other	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: - Other Wait - Parallel Coordination Wait - Cursor Synchronization Wait - Backup Recovery Wait - Database Replication Wait - Deferred Task Worker Wait - External Procedure Wait - Full Text Search Wait - Hosted Components Wait - Other Miscellaneous Wait - Service Broker Wait - Synchronous Task Wait

In addition, the following non-default metrics can be added to each of the above resources:

• Active Time
• Active Transactions Total
• Disk Queue Length
• Full Scans
• Index Searches
• Instance Unavailable
• Instance Up Time
• MSSQL Physical I/O Operations
• Physical Page Reads
• Physical Page Writes
• Probe Scans
• Range Scans

The following table displays the resource menu and the instance metrics:

Resource menu	Instance Level Default Metrics	Dimension Type and Dimension Value Default Metrics
Workload	The Workload resource is selected by default - Active Time - Executions - CPU Usage - Average SQL Response Time - Wait Time Percent - Logins Rate - Batches Rate	- Active Time - Executions - CPU Usage - Average SQL Response Time - Wait Time Percent - Row Count
CPU	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	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	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
Lock	Displays the lock related wait events of a database. 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	Displays the latch related waited events of the database. Default metrics displayed: - Latch Savepoint - Latch Wait - Internal Catch Latch	- Latch Savepoint - Latch Wait - Internal Catch Latch
Log	Displays the log related wait events of the database. Default metrics displayed: - Log Wait - Log Write - Log Buffer - Log Other - Log Synchronization	- Log Wait - Log Write - Log Buffer - Log Other - Log Synchronization
CLR	Displays the wait events of the database that occur as a result of statements waiting for CLR code execution to complete. Default metric displayed: - CLR Wait	- CLR Wait
Remote Provider	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	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:

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 Addition and deletion of login accounts.

Database Configuration: For each database in an MSSQL instance: configuration parameters, File group parameters; files (data and log) location, size and status.

Database Objects: For each database in an MSSQL instance: Any schema object: Tables and indexes and their partitions and subpartitions; 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: Tracks the changes to sp_configure settings, and DBCC trace flags.

System Configuration: For hardware and operating system configuration (for example, disks and network interfaces (NIC); amount of RAM; CPU count; device installations and swap space allocation).

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. Click Generate Plan to generate the execution plan, and then click Compare Plan to view it. This opens a pop up with a compression of all available execution plans of the selected statement.

Blocking History

Navigate to the Database > Overview > SQL PI page. In the History section, click Blocking History. The Lock Analysis displays all locks that took place within the selected time range. The lock analysis feature is integrated as part of the performance tree and it displays all the lock trees including further details for both the blocker and the blocked session including:

• Event Start
• SPID [SPID, Date-Time]
• Blocked By
• Resource
• Lock Resource Type
• Status
• Duration
• Program
• User
• SQL Text
• Database
• Machine
• Command Type

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.

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.

    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. The lock analysis feature is integrated as part of the performance tree and it displays all the lock trees including further details for both the blocker and the blocked session including:

• Lock event start date
• Session Identifier - [Sid,SERIAL#]
• Session Identifer of the blocker
• Locked Object name
• Status
• Lock Duration
• Program
• DB User
• SQL Text
• Client Machine

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. Selecting the SQL Text in the SQL Statements tree displays a page that allows viewing detailed information about the selected SQL statement.

To view SQL statement details:

1. In the Instance view, expand the Instance View.
2. Click SQL Statements.

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 executions of the number of selected SQL statement 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 dashboard.

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

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