-
Title
Is SQL PI available for Azure SQL database agents? -
Description
Is the SQL Performance Investigator (SQL PI) extension available for Azure SQL database agents?
-
Resolution
Beginning with the 5.9.7.20 and higher releases the SQL Performance Investigator extension is available for Azure SQL database agents.
The SQL PI view provides the ability to perform a more in-depth analysis and investigation of the database activity and resource consumption by adding a dimension view of the activity, locked objects, and blocking history.
Performance Tree
The performance tree provides iterative (up to three levels) access to any of the key dimensions associated with Azure SQL database activity, based on the OLAP multidimensional model and an instance view of the database activity. Domain nodes offer a hierarchical view of all types of Azure SQL database 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 User, follow the steps described below:
- Select the 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.
- Select the first user, to focus the entire window on that user's activity.
- 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.
- Select a specific SQL statement to focus the entire window on the selected statement's activity.
- 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 Azure SQL database dimension of interest, to gain a complete understanding of the causes of its behavior.
The default Azure SQL database dimensions are as follows:
- SQL Statements — The executed SQL queries.
- TSQL Batches— A batch of SQL statements. That is a group of two or more SQL statements or a single SQL statement that has the same effect as a group of two or more SQL statements.
- Programs — names of the programs that connected to Azure SQL database and executed the SQL statements.
- Users — Azure SQL database login names used for logging in to Azure SQL database. • Client Machines — The machines on which the client executable (connected to Azure SQL database) is running.
- Context Info — Displays the information set by the DBMS_APPLICATION_INFO.SET_CLIENT_INFO procedure.
- Command Types — Executed SQL command type (for example, INSERT and SELECT).
- 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.
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.
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
- SPID
- Blocked By
- Resource
- Lock Resource Type
- Status
- Duration
- Program
- User
- SQL Text
- Machine
- Command Type
Viewing Execution Plans
This view presents the execution plan of a selected SQL and the cost of it. A Historical execution plan can be generated from History by selecting the statement or batch and by clicking Open in SSMS in the top of the table