Documentation forSQL Sentry

Performance Analysis Baselining

Applies to: Azure SQL Database, Hyper-V, SQL Server, SSAS, VMware, and Windows targets.

 

Introduction

Baselines are created and managed from the History view of the Performance AnalysisDashboard.  There are two distinct baseline types available. The first type of baseline is the predefined type. SQL Sentry includes several predefined baselines that are based on the historical metrics gathered in your monitored environment. The second type of baseline is the custom type. SQL Sentry also allows you to define your own custom baselines.

Once a baseline has been selected, it's overlaid on the various Performance AnalysisDashboard charts, giving you information that can help you to manage change in your monitored environment. Custom baselines can also be used in Advisory Conditions, allowing the comparison of baseline values to current metrics, as they are captured.

Note:  At this time, you can't display a baseline created by one type of target overlaid on a different type of target. For example, creating a baseline against a SQL Server instance running on a Windows target can't be overlaid on an Azure SQL Database dashboard.

Predefined Baselines

Predefined baselines are available without any custom configuration. They can be accessed from the Baseline drop-down menu on the Dashboard while in History view.

Performance Analysis Dashboard Baseline drop-down list

The different predefined baselines are available based on the historical time range you are viewing on the Dashboard. For more information, see the following table: 

Baseline Time Range Availability Range Option Availability (detail data)
Previous Period This baseline is always available. Corresponds to the immediate preceding timeframe. Yes
Previous Day Zero <= 24 hrs Yes
Previous Week Zero  <= 168 hrs Yes
Previous Month 36 <= 720 hrs No
Previous Quarter 480 <= 2160 hrs No
Previous Year 960 <= 8760 hrs No

Average and Range Modes

It's important to understand that predefined baselines are based on the historical data collected in your monitored environment. The granularity of the data-points that make up a baseline is entirely dependent on the time range you are viewing.   

Average

By default, predefined baselines are shown with data-points that show the average value over that sample period.

Performance Analysis Dashboard Average Baseline

Range

When viewing certain predefined baselines, and within a time range that has a resolution of detail data, an additional option is available, allowing you to view that baseline with a range of values (reference the Range Option Availability column in the previous chart). This range includes both a minimum and maximum observed value during the respective sample period.

Performance Analysis Dashboard Range Baseline

Custom Baselines

Custom baselines can be created on demand using the Dashboard user interface while in History view. To create a custom baseline, left-click on any Dashboard chart and drag to highlight your desired timeframe. 

Performance Analysis Dashboard Create Baseline context menu

Note:  Create a custom baseline for a more specified time frame by adjusting the start and end time on the calendar toolbar to the time frame you'd like to baseline.  

Select Create Baseline from the context menu to display the Create Baseline form.

Create Baseline form

Note:  By default, a baseline is tied to the server and instance where it's created. Enable your Custom Baseline Globally by selecting the Global Baseline check-box.

Dashboard Create Baseline Global Baseline check-box

Create Baseline Form

The Create Baseline form contains information about all of the metrics  captured as part of a custom baseline. For more information, see the following table:

Field Description
Metric The name of the metric captured in the baseline.
Default Defines which metric in the chart is used when displaying the baseline.

Note:  This can be changed at any time.

Average

The value that's used for that metric in the baseline. This controls both the actual line displayed on the respective chart and the value used for comparison purposes inAdvisory Conditions. Average is calculated based on the observed values for the metric within the baseline's time range.

Note:  To define custom values to be used with the baseline, simply edit the pre-calculated average value.

Min The minimum value captured during the baseline's time range.
Max The maximum value captured during the baseline's time range.
Standard Deviation The standard deviation for the values captured during the baseline's time range.
Note:  You can change the Min, Max, Average, and Standard deviation values for any metric in the Create Baseline or Edit Baseline forms. Select the desired metric, change it to your desired value, and then select Ok to save your changes.

Create Baseline changing a metric

Using Custom Baselines in Advisory Conditions

The values defined for metrics that make up a custom baseline can be used in Advisory Conditions. To access a baseline in an Advisory Condition, define the Advisory Condition at that same instance level for which the baseline is defined. For example, if you create a baseline on the server named LONDON.UK.COM, to access the baseline in an Advisory Condition, create the Advisory Condition from the context of the LONDON.UK.COM server in the Navigator pane. For more information about Advisory Conditions, see the Advisory Conditions topic.   

Editing Baselines

Edit baselines in the Edit Baseline form. Open the Edit Baseline form by right clicking the graph next to the baseline,

Performance Analysis Dashboard Edit Baseline context menu option

 or by selecting...by the Baseline drop-down menu.

Performance Analysis Dashboard Edit Baseline (...) button

Deleting Baselines

Delete a baseline by selecting Delete Baseline from the Edit Baseline form, and then selecting OK from the Delete Baseline window.

Delete Baseline

Baselines and Performance Counter Mapping

When using baselines in Advisory Conditions, reference the following tables that include mapping between the metrics captured in baselines and their associated friendly performance counter names.

SQL Server Baseline Mapping

Chart Metric Associated Exposed Performance Counter (Advisory Condition format)
Backup/Restore

Backup MB/sec SQL Server Database Engine: SQL Server: Databases: Backup/Restore MB/sec
Cache Hit Ratios

Page Life Expectancy (sec) SQL Server Database Engine- Buffer Node - PLE : Equals : "Node Name"

SQL Plans SQL Server Database Engine Plan Cache Hit Ratio: Equals: SQL Plans

Object Plans SQL Server Database Engine Plan Cache Hit Ratio: Equals: Object Plans
Checkpoint pgs | Lazy writes

Checkpoint pages/sec SQL Server Database Engine SQL Server Buffer Manager: Checkpoint pgs

Lazy writes/sec SQL Server Database Engine SQL Server Buffer Manager: Lazy writes/sec
CPU Usage

Total CPU Usage Percent Windows: Processor Information: Percentage Processor Time: Total
Database Latency

Total ms/Read SQL Server Database Engine: SQL Server Virtual File Statistics: ms/Read

Total ms/Write SQL Server Database Engine: SQL Server Virtual File Statistics: ms/Write
Disk IO

Total ms/Read Windows: PhysicalDisk: ms/Read

Total ms/Write Windows: PhysicalDisk: ms/Write
Faults (Read | Write)

Faults: Read Windows: Memory: Faults: Read

Write Faults: Write Windows: Memory: Faults: Write
Key lookups | Forwarded recs

Key lookups/sec SQL Server Database Engine: SQL Server:Access Methods: Key lookups/sec

Forwarded records/sec SQL Server Database Engine: SQL Server:Access Methods: Forwarded Records/sec
Log Flushes

Log Flushes/sec SQL Server Database Engine: SQLServer:Databases: Log flushes/sec: Total
Network In

In Percent Windows: Network Interface: Received percentage: Total
Network out

Out Percent Windows: Network Interface: Sent percentage: Total
Pages

Page reads/sec SQL Server Database Engine: SQLServer:Buffer Manager: Page reads/sec

Page writes/sec SQL Server Database Engine: SQLServer:Buffer Manager: Page writes/sec
SQL Activity

Batches/sec SQL Server Database Engine:SqlServer: SQL Statistics: Batches/sec

Transactions/sec SQL Server Database Engine:Databases: Transactions/sec: Total

Compiles/sec SQL Server Database Engine: SQLServer:Statistics: Compiles/sec

Recompiles/sec SQL Server Database Engine: SQLServer:Statistics: Recompiles/sec
SQL Server Memory

Total SQL Memory Usage (MB) SQL Server Database Engine:SQLServer: Buffer Node: Database pages: Total
SQL Server Waits

Total Wait Time (ms) SQL Server Wait Statistics: Waits by Category: Total
System Memory

Total Memory Usage (MB) Windows: Memory: Total used Memory (MB)

SSAS Baseline Mapping

Chart Metric Associated Exposed Performance Counter (Advisory Condition format)
CPU Usage

Total CPU Usage Percent Windows: Processor Information: Percentage Processor Time: Total
Disk IO

Total ms/Read Windows: PhysicalDisk: ms/Read

Total ms/Write Windows: PhysicalDisk: ms/Write
Faults (Read | Write)

Faults: Read Windows: Memory: Faults: Read

Faults: Write Windows: Memory: Faults: Write
Network In

In Percent Windows: Network Interface: Received Percentage: Total
Network out

Out Percent Windows: Network Interface: Sent Percentage: Total
SSAS Activity: Rows Transferred

Query rows sent/sec SSAS: Storage Engine Query rows sent/sec

Processing rows read/sec SSAS: Processing: Processing rows read/sec
SSAS Activity: Threads Formula Engine

Query pool busy threads SSAS: Threads: Query pool busy threads

Query pool job rate SSAS: Threads: Query pool job rate

Query pool job queue length SSAS: Threads: Query pool job queue length
SSAS Activity: Threads Storage Engine

Busy SSAS: Threads: Processing pool busy I/O job threads

Processing pool job rate SSAS: Threads: Processing pool I/O  job completion rate

Queued SSAS: Threads: Processing pool I/O job queue length
SSAS General : Avg Time

Total Wait Time (ms) SSAS Trace Waits: Total Wait Time (ms)
SSAS General: MDX

Cells calculated/sec SSAS: MDX : Cells calculated/sec

Calculation covers/sec SSAS: MDX : Calculation covers/sec

Sonar subcubes/sec SSAS: MDX : Sonar subcubes/sec

Recomputes/sec SSAS: MDX : Recomputes/sec

NON EMPTY unoptimized/sec SSAS: MDX : NON EMPTY unoptimized/sec

NON EMPTY for calculated members/sec SSAS: MDX : NON EMPTY for calculated members/sec
SSAS General: Processing

Processing rows written/sec SSAS: Processing: Processing rows written/sec

Index rows created/sec SSAS: Proc Indexes: Index rows created/sec

Aggregation rows created/sec SSAS: Proc Aggregations: Aggregation rows created/sec
SSAS Mem Usage (MB)

Total Cleaner Memory (MB) SSAS: Memory: Total Cleaner Memory (MB)

Total Category Memory (MB) SSAS: Memory: usage by Category: Total memory Usage (MB)
SSAS Memory: Cache Activity

Cache inserts/sec SSAS: Cache: Cache inserts/sec

Cache evictions/sec SSAS: Cache: Cache evictions/sec

Flat cache inserts/sec SSAS: MDX: Flat cache inserts/sec

KB added/sec SSAS: Cache: KB added/sec

KB shrunk/sec SSAS: Memory: KB shrunk/sec
SSAS Memory: Cache Lookups

Dimension cache hits/sec SSAS: Cache: Lookups/sec

Measure group cache lookups/sec SSAS: Storage Engine Query: Measure group cache lookups/sec

Calculations cache lookups/sec SSAS: Storage Engine Query: Calculations cache lookups/sec

Flat cache lookups/sec SSAS: Storage Engine Query: Flat lookups/sec
SSAS Storage: Current Partitions

Indexes SSAS: Proc Indexes: Current partitions (indexes)

Aggregations SSAS: Proc Aggregations: Current partitions (aggregations)
SSAS Storage: File Queries

Total queries from file/sec SSAS: Storage Engine Query: Total queries from file/sec

Aggregation hits/sec SSAS: SSAS Trace: Aggregation hits/sec: Total
SSAS Storage: File Read KB

Total System File Read KB Windows:System:Total System read KB/Sec
SSAS Storage: File Write KB

Total System File Write KB Windows:System:Total System write KB/Sec
System Memory

Total Memory Usage (MB) Windows: Memory: Total used Memory (MB)