SentryOne SQL Server Essentials

SQL Server Performance Monitoring and Tuning the SentryOne Way
Overview

This course provides the knowledge and skills needed to gain basic proficiency with the extensive performance monitoring and optimization capabilities of SQL Sentry on the SentryOne platform. Using our practical, real-world approach to SQL Server performance analysis and tuning, students learn how to use this powerful toolset to find and correct the SQL Server performance problems prevalent in organizations today.

Curriculum

Module 1: Performance Analysis and Event Calendar Overview

All craftsmen know that a key element to obtaining proficiency in their trade is knowing the purpose of each tool in their toolbox and what tool to use in every situation. We start this course by introducing you to the tools in the SentryOne toolbox.

Module 2: SentryOne Software Product Components

Before we start delving into details, it’s important we understand the fundamentals. In this module we cover the role played by each SentryOne component, the Windows and SQL Server features used to enable data collection, and your deployment options that ensure an efficient and reliable monitoring solution. We also discuss factors impacting the size and responsiveness of the SentryOne database, and how to ensure maximum performance. Topics covered include:

  • Product Components
    • SentryOne Monitoring Service
    • SentryOne Database
    • SentryOne Client
    • Cloud.SentryOne.com & Power BI

Module 3: Key Concepts

Our Performance Analysis and Event Calendar functionality are built on some foundational concepts of what we should expect from monitoring and performance software and how it should work. It is important we understand what they are and how they have been implemented. Topics covered include:

  • SentryOne Product Component Integration
  • Targets and Instances
  • The Alerting and Response System Hierarchy
  • Data Retention and Dashboard Resolutions

Module 4: Product Security Management

Security is an essential element of any software product. In this module we discuss permissions required by SentryOne to monitor your systems, user access privileges necessary to use the SentryOne client effectively, and options available to limit user access. Topics covered include:

  • Monitoring Service Security
  • Client Software Security
    • Rights-Based Security
    • Role Based Security

Module 5: The SentryOne Client Interface

The SentryOne Client provides a seamless experience for SQL Server and Windows monitoring and optimization, query tuning, alerting and job scheduling. This module discusses the elements of the SentryOne Client and their basic usage. Topics covered include:

  • Navigator and Event View Panes
  • Red \ Bold errors on Navigator pane
  • Conditions and Settings Panes
  • Workspace – Event Calendar & Performance Dashboard
  • Status and Progress Indicators
  • User Preferences
  • Monitoring Service Settings and Site Management

Module 6: SentryOne Deployment Options (Optional)

Organizations’ SQL Server landscapes come in all shapes and sizes in response to their own business needs. The Performance Analysis and Event Calendar features have been designed to monitor most every configuration from a single, local SQL Server instance to hundreds of instances (virtual and physical) deployed in multiple data centers around the world.

  • Deployment Basics — SentryOne monitoring services, database and client
  • Planning, Creating and Managing Sites
  • Deploying in complex environments
  • Monitoring cloud environments – Azure and AWS

Module 7: Creating an Effective Event Monitoring and Response System

The SentryOne platform enables you to create a comprehensive Monitoring and Response System using Conditions, Actions, and Settings. This module explores each of these concepts in depth, and discusses how to implement them effectively. Topics covered include:

  • Effective Event Monitoring and Response Systems
  • Plan and Implement a Structured Event Monitoring and Response System
  • Settings, Conditions, and Actions
  • Leveraging the Actions Log
  • Going Beyond Alerting — automating actions in response to events
    • Documenting remediation using notes
  • Advanced Event Monitoring – using Object Groups, Custom Event Views and Event Chains

Module 8: Monitoring SQL Server & Windows Performance

Beyond monitoring and alerting on scheduled and unscheduled events such as job failures and deadlocks is performance monitoring. At this level we want our monitoring solution to be on the lookout for high resource utilization, combinations of performance metrics exceeding defined thresholds, and deviations from established baselines. In this module we examine how to realize this level of monitoring, and more. Topics covered include:

  • SQL Server and Windows KPMs – the Performance Dashboard
  • Baselines
  • Monitoring the Disk Subsystem
  • SQL Blocking and Deadlocks
  • Top SQL
  • Database Tables and Indexes
  • Monitoring Virtual Machines

Module 9: Monitoring SQL Server HA Solutions

SQL Server offers a range of native high availability solutions to meet most every budget and business requirement. SQL Sentry provides a centralized monitoring and alerting mechanism for all of them.

  • Log Shipping
  • Database Mirroring
  • AlwaysOn Availability Groups
  • Monitoring Database Replication

Module 10: Monitor SQL Server Performance with Advisory Conditions

We can’t always be actively doing Performance Analysis, waiting for indications of a performance issue. Instead, use Advisory Conditions to define performance metric based conditions and alerts.

  • What are Advisory Conditions?
  • Working with Advisory Conditions in SQL Sentry – Conditions Lists and Events Logs
  • Getting Started — the download pack
    • download and explore conditions in the conditions pack
    • enable Advisory Conditions by assigning actions
    • disable Advisory Conditions
  • Cloning, Importing and Exporting
  • Building a advisory condition

Module 11: Performance Troubleshooting

Troubleshooting SQL Server performance issues does not lend itself to formulaic approaches. Individual metrics provide clues, but combinations of the right metrics, along with an understanding of “normal” behavior usually leads down the correct path to resolution. SQL Sentry excels at showing you combinations and correlations of metrics that, when combined with an understanding of how SQL Server works, makes finding the right path much easier. In this module we will explore methods for uncovering the root cause of common SQL Server performance issues. Topics covered include:

  • SQL Server Configuration
  • Using Waits and Queues
  • CPU, Memory, and Disk I/O – Where’s the Bottleneck?
  • Long running T-SQL, Blocking and Deadlocks

Module 12: Query Tuning using Plan Explorer

Plan Explorer helps you quickly get to the root of query tuning issues by making it obvious where issues exist in your execution plans. SentryOne continuously captures deep plan information for slow, poorly performing T-SQL. In this module we discuss the basics of query plan analysis and query tuning using Plan Explorer. Topics covered include:

  • What plans are (and are not) captured
  • Settings that affect query and plan capture
  • The enhanced Plan Diagram, and other PE tabs
  • Query Plan Analysis using selected examples
  • Sharing Query Plans
  • Integration with other SQL Sentry features and SQL Server Management Studio

Module 13: Cloud.SentryOne.com

Performance related SentryOne data can be accessed using any web browser on any device from cloud.sentryone.com. In this module we present our current cloud based feature set, and walk through deploying your data. Topics covered include:

  • Server Health Status
  • Server Details
  • Reporting from the cloud
  • SentryOne PowerBI Content Pack

Module 14: Summary

This module will provide a wrap-up of topics in this course, a preview of Mastery level topics, and an opportunity for students to evaluate the course.

SentryOne Essentials
Need help selling it? Here's a convenient PDF to share. Get your team the help they need and contact Professional Services.
To start the conversation, contact Professional Services at proservices@sentryone.com or call toll-free 855-775-7733.

LOOKS LIKE YOUR BROWSER IS OUT OF DATE

For your convenience here are links to supported browsers:

Your browser could be incompatible with this website.
If you close this window and continue you may experience less than optimal behavior.

Don't show this again