Viewing Historical Metrics
The History section view is divided into two sections that are correlated to each other:
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
The XTP pane displays information gathered by SQL Performance Investigator and XTP-related DVMVs. Metrics displayed are:
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.
Use the XTP related Metrics:
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:
Addition and deletion of login accounts.
For each database in an MSSQL instance: configuration parameters, File group parameters; files (data and log) location, size and status.
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).
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.
Track changes to sp_configure settings, and DBCC trace flags.
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:
The execution plan dashboard displays the following content.
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.
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.
Provides the same view as without SQL PI configured. See Blocking History .
Provides the same view as without SQL PI configured. See Viewing SQL Statement Details .