Documentation forSQL Sentry

Performance Analysis Dashboard

Introduction

The Dashboard provides a graphical representation of the monitored server activity through a variety of graphs. Color coded graphs distinguish between what's used by the instance versus any other processes.

Applies to: Some tabs and features may not be available to all products or target types. Please read further for more detail.
SQL Sentry Portal: This feature is available in SQL Sentry Portal. To learn more about configuring your environment to use the on-premises, browser-based option with your existing SQL Sentry database, see the SQL Sentry Portal article.
See the SQL Sentry Portal Custom Charts article for an example of the Dashboard feature in a browser.

Opening the Performance Analysis Dashboard

The Performance AnalysisDashboard can be opened by right-clicking any instance in the Navigator pane, and selecting Open > Performance Analysis.

Select Performance Analysis in the Navigator

It can also be opened by double-clicking the instance in the Navigator pane, selecting Performance Analysis from the View Selector, and then selecting Ok.

Select Performance Analysis in the Navigator

Dashboard Overview

The Performance Analysis Dashboard provides an overview of different targets and instances, such as Windows and SQL Server. The dashboard displays the most important metrics used for determining health and load. Performance data is organized into two vertical panes, split into smaller sections depending on the type of dashboard you are viewing. For example, the Dashboard for a Windows based target with a SQL Server instance shows Windows operating system specific metrics in the left pane and SQL Server or SSAS metrics in the right pane.

Note:  Each instance has a unique color assigned to it that remains constant across all charts. Hovering over colored sections of these charts displays the corresponding instance.

SQL Sentry Performance Analysis Dashboard instance colors

Network

The Network section displays the total network traffic on the server as well as the network utilization on each of the adapters that's present on the monitored target.

Performance Analysis Dashboard Network graph

CPU Usage

The CPU Usage section displays the total CPU Usage for the server as well as information on context switching, user time, kernel time, and more.

Performance Analysis Dashboard CPU Usage graph

Performance Analysis Dashboard CPU Usage Other CPU

Note:  The Other category represents any collected processes that are not within a Well-Known Processes group. For more information about the Processes tab, and creating your own well-known processes groups, see the Performance Analysis Processes article.

System Memory

The System Memory section displays information about the amount of memory being used by different processes on the server as well as page faults and page file usage.

Note:  A graph that contains multiple uses of the same color indicates the environment has multiple NUMA nodes.

Performance Analysis Dashboard System Memory graph

Disk IO

The Disk IO Section displays the read and write latency for each of the physical disks on the server.

Performance Analysis Dashboard Disk IO graph

Metrics for all SQL Server or SSAS instances on the server are integrated into the Windows Network, CPU Usage, and System Memory charts, allowing you to quickly determine exactly how much of these resources is being used by each instance, at any point in time.

SQL Sentry Performance Analysis Dashboard Network, CPU Usage, System Memory, Disk IO graphs

SQL Server Activity

The SQL Server Activity Section displays information about what the SQL Server instance is doing.

Performance Analysis Dashboard SQL Server Activity graph

SQL Server Waits

The SQL Server Waits section displays information about the classes and categories of waits that occurred as well as the duration of milliseconds that the waits were in effect during that time period.

Performance Analysis Dashboard SQL Server Waits graph

SQL Server Memory

The SQL Server Memory section displays information about how the Server instance is using memory that has been allocated to it. 

Performance Analysis Dashboard SQL Server Memory graph

Note:  A graph that contains multiple uses of the same color indicates the environment has multiple NUMA nodes.

Database IO

The Database IO Section displays information about the read and write latency for the databases.

Performance Analysis Dashboard Disk IO graph

Understanding the Dashboard

For more information about the various performance metrics displayed on the Dashboard, see the Performance Metrics topic.

Note:  A graph that contains multiple uses of the same color indicates the environment has multiple NUMA nodes.

Performance Analysis Dashboard Multiple Numa nodes

For performance reasons, the Dashboard never queries the remote server directly; all data is retrieved from the SQL Sentry database after it's collected by the SQL Sentry monitoring service

Note:  There may be a slight delay between the time the performance data is collected and the time it's displayed on the Dashboard; although, typically this isn't more than a few seconds.

When in real-time mode (auto-refresh is enabled), the charts and gauges update automatically approximately every 10 seconds to show the values for the last collected sample. Enable real-time mode by selecting the Play button in the toolbar.

toolbar Start auto-refresh button

Dashboard Display Modes

The Dashboard has two display modes: Sample and History. The display mode can be changed at any time by toggling the mode toolbar button.

toolbar Sample mode button

Note:  The graphic and the text changes when the mode button is toggled, and it always indicates the mode that becomes active when you select the button, versus the currently active mode.

Sample mode uses various bar charts and digital gauges to show activity for a specific time, or sample time. This mode includes certain metrics that aren't available in History mode, such as Network activity by adapter, CPU Usage by individual processor, and SQL Server Waits by specific wait class.

Performance Analysis Dashboard Sample mode

Note:  In Sample mode, the CPU Usage chart displays Kernel time in red, and Processor time in green for each of the cores on your monitored instance.

Performance Analysis Dashboard CPU Usage chart Sample Mode

Note:  In Sample mode, a Quick Report can be run directly from any of the gauges. Right-click the desired gauge and select Quick Report to open the report.

Performance Analysis Dashboard select Quick Report in Sample mode

History mode uses line and stacked area charts to show you how the Windows Server and SQL Server targets are performing over any time range. The range can be changed by adjusting the Start and/or End times on the toolbar, and then selecting Go.

Performance Analysis Dashboard History mode time toolbar

Note:  There is no restriction on the size of the time range. The resolution of the data displays changes, growing less detailed as you select a longer time range. For example, when selecting any range of 30 minutes or less, you view the actual raw performance data, and if you select an eight-hour range, 10-minute averages display. For more information, see the Dashboard Resolutions/Retention topic.

Customizing SQL Server Waits Chart

One of the most common reasons our customers customize the SQL Server Waits Chart is to break down the Other category into something more specific. 

Note:  Read Blythe Morrow's blog post on Customizing Wait Categories for more information.

Customize the SQL Server Waits historical chart by adding an additional category to the Performance Analysis Wait Type category table, and then assigning counters in the Performance Analysis Wait Type table to the newly created category.

For example, create a new category for parallelism by using the following query:

New Category Creation

INSERT INTO [SentryOne].[dbo].[PerformanceAnalysisWaitTypeCategory] (Name, SortOrder, Enabled, RGBColor) 
VALUES ('Parallelism',6,1,'#bb7fd1')

After creating the Parallelism category, add the following counter to it:

Adding a Counter to the Category

UPDATE [SentryOne].[dbo].[PerformanceAnalysisWaitType] SET WaitTypeCategoryID = 7
WHERE Name = 'CXPACKET'

Interacting with the Dashboard

Changing the Active SQL Server/SSAS Instance

You can change the SQL Server or SSAS instance using the Instance drop-down control in the top middle of the screen. The metrics in the right pane reload, but the active date range and other options are maintained.

Performance Analysis Dashboard select the desired instance

The following functions are available in History mode:

Tooltips

Hover over any point on the chart to see tooltips with specifics about the sample, including the sample instance Time, Name, and Value.

Performance Analysis Dashboard tooltips

Select any graph metric to highlight that metric on the graph.

Performance Analysis Dashboard highlighted metric on graph

Sample Selection

Clicking on a history chart presents a vertical red line at that position called a cursor. The selected time is indicated by the Sample Pos box at the top right. The cursor is synchronized across all charts allowing you to easily correlate what's happening with one performance metric with what's happening with other related metrics at the same time.

Performance Analysis Dashboard Sample Pos

After selecting a sample with the cursor and toggling Mode , detail data for the selected time loads in Sample mode, allowing you to see specifics for that sample that aren't available in History mode. For example, if you select a spike on the SQL Server Waits chart and change to Sample mode, you can see the specific wait classes responsible for the spike, whereas in History mode only general wait categories display.

Performance Analysis Dashboard Sample Pos data

Click and Hold

If you click and hold a sample, the cursor changes color, and when you release the mouse you'll be presented with a subset of the Jump To context items used for range selection. This allows you to jump directly to a different tab and view only operations that overlapped the currently selected sample time.

Performance Analysis Dashboard Click and hold context menu

Range Selection

Select a history chart and drag the cursor to select a range. Range Start and Range End boxes update, and releasing the mouse button causes context items to appear, allowing you to choose whether to Zoom In the selected range or Jump To another part of the application using the selected range.  For example, selecting a range that includes a spike in network activity, and then selecting Jump To > Top SQL, displays and loads the Top SQL tab with queries that ran during that range, enabling you to determine which queries may have been responsible for the network spike.

Performance Analysis Dashboard Range selection

Note:  When using  Jump To, the Sync toolbar button is automatically deactivated because the toolbar date ranges for the different tabs are no longer in sync. This allows you to jump back and forth between the Dashboard and the other tabs using Jump To with different sub-ranges without losing the original full range displayed on the Dashboard. You can reactivate synchronized tabs at any time by selecting Sync; just remember that all other tabs immediately use the range that's visible and active on the toolbar for the current tab.

toolbar Sync button

Advisory Condition Markers

Display Advisory conditions on the Performance Analysis Dashboard graphs by enabling Advisory Condition Markers.

Advisory Condition Marker

Select any Advisory Condition Marker to see the Advisory condition(s) that occurred.

Advisory Condition window

Colored lines across the bottom of each graph indicate where a condition fired, and the duration a condition was in effect for each instance. Hover over a colored line to display this information.

 

Selecting the name of the condition takes you to that event in the Events Log.

Advisory Condition window

Toolbars

Sample Mode Toolbar

Show History Mode - Changes the view to History mode.
Quick Trace - Runs a Quick Trace.
Show Windows Processes - Displays Windows processes running on the server.
Jump To Calendar- Opens the corresponding Event Calendar.
Report- Opens the Report viewer for Windows and SQL Server performance data.
Show Advisory Condition Markers- Displays Advisory Condition Markers on the Performance Analysis Dashboard.

History Mode Toolbar

Show Sample Mode - Changes the view to Sample mode.
Run Quick Trace - Runs a Quick Trace.
Show Windows Processes - Displays Windows processes running on the server.
Jump To Calendar- Opens the corresponding Event Calendar.
Report- Opens the Report viewer for Windows and SQL Server performance data.
Show Advisory Condition Markers- Displays Advisory Condition Markers on the Performance Analysis Dashboard.

Controls

Sample Mode

Network    

  • Total - Displays network activity as an aggregate across all adapters, with SQL Server/SSAS activity integrated.
  • Adapters - Displays activity by the individual network adapter, enabling you to spot adapter-specific saturation issues. 

Performance Analysis Dashboard Sample mode Total/Adapters

History Mode

Disk IO

  • Total - Displays average latency across all physical disks.
  • By Disk - Displays actual latency for a specific physical disk.

Performance Analysis Dashboard History mode Disk drop-down menu

Database IO

  • Total - Displays average latency across all database files.

  • By Database file - Displays actual latency for a specific database file.

Performance Analysis Dashboard History mode File drop-down menu

Using Quick Trace™

Quick Trace gives you an instant snapshot of what all of the processes on a SQL Server are currently doing. Trace and process-level data is collected for a specified interval and then automatically correlated, effectively combining the functionality of Profiler and Activity Monitor together with a single click. Launch Quick Trace by doing any one of the following:

  • Select the Run Quick Trace toolbar button.
    Run QuickTrace toolbar button
  • Select the Quick Traces tab, and then select Run Quick Trace. Performance Analysis QuickTraces tab Run QuickTrace button
  • Right-click any chart in the Dashboard, and then select Quick Trace.Performance Analysis Dashboard select QuickTrace from the context menu

For example, if you observe a spike in activity while in real-time mode, you can immediately launch Quick Trace and see exactly which SQL Server processes or applications are responsible for the spike.

Note:  Quick Trace isn't supported on an Azure SQL Database target. For more information, see the Quick Traces topics.   

Viewing Windows Process Activity

See metrics for all Windows processes using Show Windows Processes directly from SQL Sentry, which avoids having to open a remote desktop connection to a server, and open Windows Task Manager. The Process Activity window loads all active operating system processes, sorted in descending order by CPU by default.

Launch the Process Activity window by doing any one of the following:

  • Select the Show Windows Processes toolbar button.
    Show Windows Processes toolbar button
  • Right-click a target in the Navigator pane, and then select Show Windows Processes.
    Navigator Show Windows Processes from the context menu
  • Right-click any chart in the Dashboard, and select Show Windows Processes.Performance Analysis Dashboard Show Windows Processes context menu
Important:  Rows with zero values in the sort column are hidden by default. Unhide these rows by un-selecting the associated option at the bottom of the screen.
Note:  Viewing Windows Process Activity is not supported on an Azure SQL Database target.
Note:  Administrative access to the target server is required in order to launch the Process Activity viewer.

Event Overlays

Specific events taking place in your environment can also be overlaid on the Performance Analysis Dashboard. Access this feature by selecting Jump To Dashboard from a number of places within the SQL Sentry client that display a range of historical events, including any: 

When using the Jump To Dashboard feature, events are overlaid on the x-axis for each chart. The start of each distinct event is represented with a green circle, with the event's duration represented with a pink line, ending in a pink dot. Hovering over an event displays a tooltip about that event.

Note:  If the selected time range is not large enough, events are  represented by a green circle. If this is the case, changing the time range to a smaller interval preserves the overlay of the event, and may offer a better view of the impact that each event had.

From Event Calendar

When using the Jump To Dashboard feature from Event Calendar any events that are highlighted with the Calendar Highlighting feature are overlaid on the Dashboard.

Performance Analysis Dashboard Event Calendar Overlay

From Top SQL 

When using the Jump To Dashboard feature from Top SQL , if the Show Totals is enabled all events belonging to the selected group are overlaid on the Dashboard. If Default mode is enabled only the selected event is overlaid on the Dashboard.

Performance Analysis Dashboard Top SQL Overlay

From Blocking or QuickTraces 

When using the Jump To Dashboard feature from Blocking or QuickTraces the selected event is overlaid on the Dashboard.

Performance Analysis Dashboard Blocking SQL Overlay