SQL Sentry Overview

What is SQL Sentry?

SQL Sentry is a highly capable and scalable platform for enterprise data performance management. It's used to monitor, diagnose, and optimize Microsoft SQL Server environments. SQL Sentry collects and retains historical data; allowing for real-time troubleshooting and investigating long-term patterns and changes. 

SQL Sentry enables users to correlate events and perform root cause analysis of performance issues and job failures. The intelligent alerting feature is designed for customization; in addition to general and out-of-the-box conditions, it allows users to create their own Advisory Conditions and Response Rulesets. These alert optimizations translate to receiving only the alerts that are important to you and unexpected for your system. An alerting system isn't useful when you filter alerts to a folder that contains tens of thousands of other alerts. With SQL Sentry, the alerts that allow you to be proactive at your job aren't buried in that type of spam. Conditions may also be configured with Actions to execute automatically when one is met.

SQL Sentry Use Cases

Some key reasons to use SQL Sentry include the following:

• Quick view of real-time and historical events and performance data
• Pinpoint and resolve unexpected spikes across important Windows and SQL Server metrics
• Intelligent alerting and response system
• Performance tuning through identifying bottlenecks
  • Costly or long-running SQL queries
  • Blocking
  • Deadlocks
  • Processes
• Baselines show the impact of code, hardware, workload, and configuration changes
  • Know what is normal vs. changed performance

Discovering Root Cause with SQL Sentry

Imagine that you're viewing a spike in CPU on the Performance Analysis Dashboard. With SQL Sentry, you can highlight that CPU spike on the Dashboard and zoom in to see how other metrics were impacted during that time period. The entire dashboard syncs to your selection. What do you do next? Use the Jump To feature to go from the Dashboard to another area within the application such as the Calendar, Processes, Top SQL, Disk Activity, Deadlocks, and more! This empowers you to correlate that spike with other activity and discover the root cause.

Jump To Calendar context items are made available from various points allowing you to navigate directly to a Calendar view for the active date range showing all associated Performance Analysis events (Top SQL, Blocking SQL and Deadlocks) alongside Event Calendar events such as SQL Agent Jobs, and Windows Tasks. 

Access Jump To Calendar by doing one of the following:

Right-click the desired graph on the Performance Analysis Dashboard, and then select Jump To > Calendar,

or select Jump to Calendar from the toolbar.

Example:  Correlating Performance Metrics with Jump To

1. Start on the Performance Analysis Dashboard
2. Select the time period representing the CPU spike
3. Use the context menu to select Jump To
4. Jump To > Top SQL 
   1. Select the query consuming the most CPU at that time
5. Jump To > Dashboard
   1. There's now a visual marker on the Dashboard under the CPU Usage showing the start and end times of that query
6. Jump To > Top SQL
   1. Now that the query is confirmed as the CPU usage culprit, jump back to Top SQL to explore the query and plan history
   2. Is the query trending a certain way?
      1. Verify statistics and index maintenance
   3. Is the query using more CPU at noon every day?
      • Jump To > Calendar
        1. What else is running at noon every day?
        2. Need to reschedule a job?
   4. Does it seem like a random, one time occurrence?
      • Jump To > Calendar
        1. Did someone execute a large report?

There are numerous Jump To options and ways to correlate metrics to events to perform quick and comprehensive root cause analysis.

Performance Analysis Tabs

Dashboard

The Performance Analysis Dashboard provides an overview of the targets in your SQL Sentry environment.

Processes

The Processes tab contains a grid view of all of the processes for which you are collecting information. They are grouped by program and function, and you can add new groups for well-known processes.

Disk Activity

The Disk Activity tab provides a patented graphical disk analysis system that breaks down disk activity and latency at the controller, physical disk, and file level, highlighting bottlenecks at any point in a disk system.

Disk Space

The Disk Space tab contains information about disk space utilization within your environment, broken down by database and file. Use the Disk Space tab to identify disk capacity issues, understand where various database files reside on the disk system, and to determine whether available disk space is optimally used.

Top SQL

The Top SQL tab lists all T-SQL batches, stored procedures (RPCs), and statement events collected.

Indexes

The Indexes tab helps you make intelligent decisions about index management in your environment, such as when and how to perform defragmentation operations, when to adjust fill factors, or when an index definition should be changed.

AlwaysOn

AlwaysOn Management includes full alerting capability surrounding your AlwaysOn environment. Fully customizable conditions alert you on both Health and Failover status.

Blocking SQL

The Blocking SQL tab displays all SQL Server blocks that occurred during the active date range that meet the Minimum Block Duration. Each block is displayed in a hierarchical format, showing the relationships between all blocking and blocked SPIDs in a blocking chain.

Deadlocks

The Deadlocks tab displays information about deadlocks happening within your environment. Use the Deadlocks tab to identify and correct deadlocks on your monitored servers.

QuickTrace™

A QuickTrace™ is a comprehensive snapshot of activity created by combining process-level data and trace events collected during a brief sample period. Various metrics such as CPU, I/O, recompiles, cache misses, cursor operations, etc., are automatically aggregated, and are grouped and sorted providing a clear picture the processes, hosts, applications, or users responsible for activity during the sample.

Navigator Pane

New Event Source nodes are added under SQL Servers for Top SQL, Blocking SQL and Deadlocks, enabling calendars and other functions for these sources.

Access the Event Source nodes through the Navigator pane (View > Navigator), and then expand the desired server node.

Event Calendar Pane

The Event Calendar pane has new options for Top SQL, Blocking SQL and Deadlocks to control whether these sources are displayed in the view.

Open the Event Calendar pane by selecting View > Event Calendar > Event Sources. Select the events you want to show by selecting or deselecting their respective checkbox.

Event Calendar

• View Top SQL, Blocking SQL, and Deadlocks on SQL Server, Device, and Custom View calendars.
• Jump ToPerformance Analysis context items on all Calendar events takes you to the selected tab of Performance Analysis preset to the current calendar date range.

• Opening a Top SQL, Block, or Deadlock event on the calendar takes you directly into Performance Analysis with that event selected and expanded.

Conditions, Actions, and Settings

Performance Analysisconditions are available in the Conditions pane (View > Conditions) for the following SQL Server instances:

Blocking SQL Blocking SQL: Output Content Match Blocking SQL: Runtime Threshold Max Deadlock Deadlock: Output Content Match Top SQL Top SQL: Output Content Match Top SQL: Runtime Threshold Max

The Run Quick Trace action is available for certain SQL Sentry conditions. This action executes and saves a Quick Trace when the condition is triggered, including the following:

Event Chain Node: Runtime Threshold Max Event Chain: Runtime Threshold Max Performance Counter: Threshold Max Performance Counter: Threshold Min SQL Server Agent Job: Block SQL Server Agent Job: Retry SQL Server Agent Job: Runtime Threshold Max SQL Server: Blocking SQL SQL Server: Blocking SQL: Runtime Threshold Max

Performance Analysis settings are available from the Settings pane (View > Settings):

Blocking SQL Blocking SQL Source Deadlocks Source Top SQL Top SQL Source