SQL Sentry Portal SSAS Top Commands

Introduction

The Top Commands tab shows Analysis Services query level collection and any MDX, DAX, DMX, and XMLA commands that have run longer than one second. All of the like queries have been rolled up and grouped together so you can see their total impact. Drill down to see individual queries and group by event class to do discovery on the grid data.

Columns	Descriptions
Count (Events)	The number of events.
Text Data	Associated text data captured for the event.
Parameters	Contains the parameters for parameterized queries and commands associated with the query event.
Application	Name of the client application that created the connection.
Database	Name of the database for the associated event.
Host	Name of the target where the associated event is running.
Login	The Windows or SQL Server account associated with the event.
Total Duration	The amount of time taken by the associated event.
Total CPU	The amount of CPU time (in milliseconds) used by the event.
CPU %	The percentage of CPU time used by this event, in relation to any other Top Commands events that are shown in the grid.
FE Time	Time spent by workload on the Formula Engine.
SE Time	Time spent by workload on the Storage Engine.
Processing Time	Time spent by workload processing.
SQL Query Time	Time spent by workload in SQL queries.
SE Non-cache Ops	Number of Non-cache Storage Engine operations.
SE Cache Ops	Number of Cache Storage Engine operations.
Callbacks	Applies to Tabular mode only. Provides the number of callbacks from the VertiPaq Storage Engine to the Formula Engine associated with the query.
Measure Groups	Provides a count of the number of Measure Groups accessed by the query or command.
Partitions	Provides a count of the number of Partitions accessed by the query or command.
Agg Hits	Applies to Multidimensional mode only. Provides a count of the number of Aggregations accessed by the query.
Dimensions	Provides the dimensions of the query hierarchy.
Attributes	Provides a count of the number of Attributes accessed by the query.
Rows Sent	Provides a count of the number of rows returned by the query.
Processing Rows	Provides a count of the number of rows processed by the command.
Errors	Error number of a given event.
Error Text	Contains the text data associated with the error event.
Reads	The number of logical disk reads performed by the server on behalf of the event.
Reads %	The percentage of bytes read by this event, in relation to any other events that are shown in the Top Commands tab.
Writes	The number of physical disk writes performed by the server on behalf of the event.
Writes %	The percentage of bytes written by this event, in relation to any other events that are shown in the Top Commands tab.
Start Time	Time the event started.
End Time	Time the event ended.
Information	Additional data captured about the event.

Completed Queries

The Completed Queries tab lists all recorded completed queries matching the current filters across the selected time period. The Completed Queries tab is displayed on the Top Commands tab by default. There are two modes for this pane: Totals and Trace Events.

The Totals table lists all events for the active date range in a standard list format, sorted descending by Total Duration by default. The Totals table is activated whenever auto-refresh is enabled by selecting Play on the toolbar, which shows the events for the last hour with new events coming in automatically at the top of the list.

The Completed Queries table displays the Average Duration for all events, and the percentage of change for all events compared to the last recorded command execution.

Totals

The Totalstable displays the text data and associated information such as database, duration, count, and CPU for the collected SQL statements (including procedure stats, query stats, and completed queries). This groups all like events together using a normalized version of the T-SQL with all variable parameters replaced, and it shows aggregates for each group. This enables you to quickly determine which events are responsible for the most CPU or IO activity for the active date range.

Note:  By default, the Totals table displays the top 15 queries by logical reads (descending ▼. For all tables, the arrow with the circle around it highlights by which column the data is sorted; ascending ▲ or descending ▼.

Click a recorded event to display all of the Trace Events for your selection.

Totals Filtering

Click the filter button to filter by a specific database. Enter the database name in the Value field, and then click + Add filter to apply your filter.

Trace Events

Click Traces on the Completed Queries tab to flip all totals and statements tables to the detailed Trace Events and Trace Events Statements tables and display details for any collected Trace Events such as RPC:Completed.

Trace Event Filtering

Click the filter button to apply a filter. Select the desired column, operator, and enter a filter value. Click + Add Filter to apply the filter.

Note:  You can filter by the Event Class, Host, Application Name, Database Name, Login, CPU, Reads, Writes, Error, and SPID categories.

Trace Event Command Details

Select a Command from the Traces tab to open the Command Details chart for your selection.

The Command Details chart displays detailed information about the recorded Command including the Cube, Measure Group, Duration, CPU, Rows, KB, and more.

Running Queries

The Running Queries tab shows all running queries matching the current filters and is independent of the time range setting. Click the Copy Code button in the Text Data or Active Statement columns to copy the running queries text Data, or active statement code. If there are active blocks for any running query, the SPID for the blocking and blocked by queries will be displayed in those respective columns.