Thresholds
Red or Orange?

Throughout SQL CoPilot you will see some metrics and properties that are highlighted in red or orange. This page lists all of these, together with the threshold used or the reason they are highlighted.

Big Picture

Metric / PropertyOrangeRed
Mirrored databases1 or more not synchronized
Offline databasesNon zero
Overdue backupsFull backup older than 7 days where there is a differential within the last 24 hours
OR
Full backup older than 1 day if no recent differential backup
OR
Log backup older than 4 hours when recovery model is not SIMPLE
CPU utilizationMore than 50%More than 90%
Page life expectancyLess than 1/25th of max available memory*Less than 1/50th of max available memory*
Suspect pagesNon zero
Longest running process (excluding SQL Agent jobs and the sp_server_diagnostics process)More than 1 minute
Longest blocked processMore than 1 minuteMore than 5 minutes
tempdb usageMore than 50%More than 90%
Log flush wait timeMore than 32msMore than 96ms
Data file I/OAverage reads or writes more than 50msAverage reads or writes more than 100ms
Log file I/OAverage read more than 50ms or average write more than 5msAverage read more than 100ms or average write more than 10ms
Runnable tasksNon zero
SQL Agent serviceNot running
Failed jobsNon zero
Cancelled jobsNon zero
Disabled jobsNon zero
Unscheduled jobsNon zero
Long running jobsRun 20%+ longer than the average of the last 10 runs
Default traceNot running
Failed logins in the last 4 hoursNon zero
Deadlocks in the last 4 hoursNon zero
Errors in the error log in the last 4 hoursSeverity 17 - 19Severity 20+

Databases

Metric / PropertyOrangeRed
Database stateRestoring or recoveringNot online, restoring or recovering
Database ownerIs a windows account
Database collationDiffers from the server collation
Compatibility levelNot up-to-date. For example, if it is set to 90 on a 2008 instance
Suspect pagesNon zero
Average I/O (reads/writes and data/log files combined)More than 100ms
Page verify optionTorn page detectionNone
Last full or differential backupFull backup older than 7 days where there is a differential within the last 24 hours. Full backup older than 1 day if no recent differential backup
Last log backupRecovery model is not SIMPLE and no log backup for more than 4 hours
Read-onlyTrue
User accessSingle user
Mirroring stateNot synchronized
In standby modeYES
Last successful CHECKDBMore than 7 days ago, or never performed

Databases -> Database Overview

Metric / PropertyOrangeRed
Auto shrinkON
Auto closeON
Mirroring stateNot synchronized
Database chainingON
Auto create statisticsOFF
Auto update statisticsOFF
TrustworthyON
Forced parameterizationON
Date correlationON
Database ownerIs a windows account
Compatibility levelNot up-to-date. For example, if it is set to 90 on a 2008 instance
Database stateRestoring or recoveringNot online, restoring or recovering
Database collationDiffers from the server collation
Read-onlyTrue
User accessSingle user
Data purityNo data purity check performed successfully since migrating from SQL Server 2000
Page verify optionTorn page detectionNone
Last successful CHECKDBMore than 7 days ago, or never performed
Change data captureEnabled

Databases -> Data Files

Metric / PropertyOrangeRed
Virtual log filesMore than 30
Physical file nameFile on the C: drive
File growthSet to grow by a percentage, or with no growth specified
Average data file readMore than 50msMore than 100ms
Average data file writeMore than 50msMore than 100ms
Average log file readMore than 50msMore than 100ms
Average log file writeMore than 5msMore than 10ms

Performance -> Overview

Metric / PropertyOrangeRed
Page life expectancyLess than 1/25th of max available memory*Less than 1/50th of max available memory*

Performance -> CPU

Metric / PropertyOrangeRed
Runnable tasksNon zero
Signal waitsMore than 25% of total

Performance -> Memory

Metric / PropertyOrangeRed
Max server memoryZero
OR
More than 90% of physical server memory
Page life expectancyLess than 1/25th of max available memory*Less than 1/50th of max available memory*
Locked page allocationsZero. Suggests that the service account has not been given the right to lock pages in memory

Performance -> I/O

Metric / PropertyOrangeRed
Log flush wait timeMore than 32msMore than 96ms

Performance -> tempdb

Metric / PropertyOrangeRed
Percent freeLess than 50%Less than 10%

Activity -> Current -> Sessions

Metric / PropertyOrangeRed
Blocking SPIDNon zero
Open transactionsNon zero
Process elapsed time (excluding SQL Agent jobs and the sp_server_diagnostics process)More than 1 minute

Session Details

Metric / PropertyOrangeRed
Open transactionsNon zero
Process elapsed time (excluding SQL Agent jobs and the sp_server_diagnostics process)More than 1 minuteMore than 5 minutes
Blocking SPIDNon zero
Executing managed codeYES

Jobs

Metric / PropertyOrangeRed
Last run statusRetried or CancelledFailed
Job ownerIs a windows account
Last run time20%+ more than the average of the last 10 successful runs
Current run time20%+ more than the average of the last 10 successful runs

Jobs -> Timeline

The lines showing job execution in the timeline have the following color code:

GreenJob succeeded
RedJob failed
OrangeJob cancelled
BlueJob still running

Note*

"Max available memory" for the purpose of checking the page life expectancy threshold, is calculated as "max server memory" if this has been set, and 90% of physical server memory otherwise. Page life expectancy is considered to be low if its value (in seconds) drops below 1/25th (4%) of this value.

The reason this calculation is used, is because the traditional threshold of 300 seconds is too low for modern 64-bit servers. Any threshold is fairly arbitrary anyway, and should always be used as a guide rather than a hard-and-fast rule.

< Logs Reports