Shared Files for Database Performance Analyzer
  • SQL Server Table Sizes

    If you want to alert on table sizes, the attached script will retrieve a list of tables with its associated size in GB for all non-system database in the instance. This script can be plugged into a Custom - Multiple Numeric alert in DPA. For example, if you want to get a Warning alert when a table grows to above 850GB and a Critical alert, setup an alert with thresholds similar to this:
  • Resource Metric Baseline

    Introduction DPA can currently alert on resource metrics that exceed specific threshold values. However, some customers have wanted DPA to alert based on baselines. For example, my CPU typically averages about 40% and never deviates that much, but I want to know if that jumps to 60%. The default threshold for CPU % is 90% and an alert would not get triggered. However, if you calculated baselines for CPU, it might have a 90th percentile value of 47, so if you use that within the alert, 60% would...
  • SQL Server Job Failure

    Description DPA includes an out of the box alert to alert you on database job failures. However, there is no way to adjust it to exclude jobs by name, owner, etc. Wi this custom alert, simply modify the query that executes to exclude any necessary jobs and an example is provided to exclude by job name. Note : the SQL statement returns the job information and error in the first column, and a value of 1 (think of as true, i.e. it failed) in the second column. The alert levels near the bottom include...
  • Oracle Long Running Jobs

    Description If you have Oracle Scheduler jobs running and want to be notified when one of those jobs runs long, this alert will help. A long running job in this case is defined as one that is currently running and has exceeded 2 standard deviations of its average execution time. This alert uses 14 days of history to calculate the average. Alert Definition Create a Custom Alert of type " Custom SQL Alert - Multiple Numeric Return", copy/paste in the attached SQL statement and make it look similar...
  • SQL Server Database File Size Changes

    If you want to get alerted by DPA when a data or log file grows/shrinks, this alert will help you do that. As a pre-requisite, an extended event session needs to be configured to track the changes that occur. Configure Extended Event Session Extended Event Sessions (EES) can be used to track many events that occur within your SQL instance, and one of those events is named "database_file_size_changed". It fires when a database data or log file grows or shrinks and to configure the EES from Management...
  • SQL Server Alert Starter Set for DPA

    Introduction DPA's base feature set includes alerts for SQL Server, but none are enabled by default. Almost all SQL Server environments that are using DPA could benefit from a base set of alerts this article includes. The attached zip file contains 25 alerts that can be imported into your environment. Some of them are standard DPA alerts and some are custom alerts that may be important to you (and will get added to DPA eventually). Importing the Alerts To import these alerts, download the attachment...
  • VMware vMotion and Power Off Events

    DPA can be configured to monitor your VMware environment for the databases you are monitoring. The easiest approach is to add a vCenter server into DPA and here is more information on how to configure this: https://documentation.solarwinds.com/en/success_center/dpa/content/dpa-register-virtual-machine.htm Once this is configured, DPA will collect performance data as well as events that happen within VMware. Some of these events may be more important to you, especially events for vMotion (the VM...
  • Execution Plan Change with Worse Performance

    This alert executes against the DPA repository and finds queries that have had plan changes recently, and the wait times have increased by more than 100% (doubled). It will return the query text along with details about recent execution times as well as historical averages. To create this, add an alert of type Custom SQL - Multiple Numeric Return and make it look similar to this (paste in the attached SQL statement into the alert):
  • MySQL Long Running Query

    This custom alert can be used to catch sessions that have been running for more than X seconds. The first columns provides a description of the session and what the session is doing, and the 2nd column is the time it has been running. Configure a Custom SQL Alert - Multiple Numeric Return alert in DPA, and plug in the attached query for the SQL Statement field. Setup thresholds based on the number of seconds you consider a long running query. Here is an example I am using to catch session running...
  • MySQL Blocking

    When it comes to blocking, DPA can currently alert when the cumulative blocking wait time goes beyond a threshold. The alert can be found under the category of Wait Time and is named Total Blocking Wait Time. However, one drawback to alerting on cumulative blocking time, is that it can potentially be noisy in a busy server. For example, lets take 2 blocking scenarios: 10 root blocker sessions each blocking 20 other sessions for 15 seconds 1 root blocker session blocking 2 other sessions for...
  • Oracle Database not open Read Write

    An Oracle database can be started in several different states, but READ WRITE is the most common. To ensure the database remains in a READ WRITE state, you can create a custom alert within DPA to detect any other status using the attached query. This query will return 0 (false) if the status is READ WRITE and 1 for any other status and alert you. To create the alert, create a Custom SQL Alert - Single Numeric Return and paste in this query. You can use 1 as your Min threshold for the High status...
  • PostgreSQL Deadlock Alert

    PostgreSQL Deadlocks When deadlocks occur in a PostgreSQL instance, they are written into a log file specified by log_directory and log_filename parameters in posgresql,conf file. The error looks similar to the following: 2023-06-02 09:15:03.717 CDT [10384] ERROR: deadlock detected 2023-06-02 09:15:03.717 CDT [10384] DETAIL: Process 10384 waits for ShareLock on transaction 3087012926; blocked by process 11352. Process 11352 waits for ShareLock on transaction 3087012925; blocked by process 10384...
  • SQL Server - Alert for when an object gets created, modified or dropped

    Within SQL Server, it is nice to know when objects are getting created, modified and or dropped. To configure DPA to alert you about these events, here are the steps: Step 1 - Either edit an existing extended event session like system_heath or create a new session and have it watch for events named object_created, object_altered, object_deleted. On some versions, these are already part of the system_health extended event session, so check there first. Step 2 - Configure a DPA alert to read...
  • SQL Server Replication

    To monitor SQL Server replication, you should first add in the custom metrics from this article: https://thwack.solarwinds.com/content-exchange/database-performance-analyzer/m/custom-metrics/3748 . The custom metric queries are also attached to this article as well. When adding the metrics, set thresholds on the ones you want to alert on, and then create a Resource Alert similar to below. To use a single alert for all replication metrics, choose All Metrics in a Category as the alert type: ...
  • SQL Server Database Corruption

    Description Database corruption occurs when data is not stored correctly on disk or I/O subsystem. The cause of the corruption is often a hardware issue on the I/O systems or a bug in the operating system, but corruption can be caused by a wide array of issues. The problem you will notice are errors when SQL Server tries to read the corrupted information. Within SQL Server the suspect_pages table in the msdb database records the corrupted pages whenever the page is read and an error occurs, CHECKDB...
  • SQL Server Table Growth

    DESCRIPTION This alert works in conjunction with the SQL Server Table Size Collector alert in this same section. Please install that alert as a requirement for this alert. This alert will review the last 2 days of data from the collector alert (runs once a day) and calculate the growth of each table based on number of rows. You can use the thresholds in the alert to get warned when the number of rows in a table grows by more than 10% is a given day as an example. ALERT DEFINITION To create...
  • SQL Server Table Size Collector

    DESCRIPTION This alert will collect the number of rows of the top 500 tables (based on number of rows) in all databases in a SQL instance. This example alert is used only for collecting data and would never alert anyone. This data is provided as input for another custom alert named "SQL Server Table Percent Growth". Also shown below is a query that will allow you to report on this data as collected in the DPA alert tables. ALERT DEFINITION To create this alert, click on Alerts > Manage Alerts...
  • DB2 Error Log

    Monitoring the db2diag.log file is key to understanding when problems are occuring. Both warning and error message are written to this file, and a DPA custom alert can watch this file by using the SYSPROC.PD_GET_LOG_MSGS system function. The 2 main columns used in this alert are the MSG (the error message) and MSGSEVERITY (W for Warning or E for Error) columns. This custom alert turns any Warning messages into an alert value of 1 and Error message use a value of 2. This allows us to setup alert...
  • DB2 Database Growth

    This alert is based off data collected as part of the Database Size custom metric: https://thwack.solarwinds.com/content-exchange/database-performance-analyzer/m/custom-metrics/3452 It checks the max collected value from today, compared to yesterday and returns a percent of growth. The threshold are based on the percent growth, so it you want to be notified when a database grows more than 10 or 20%, use these thresholds: Note: after pasting in this SQL script, modify the value for the variable...
  • Sybase Disk Space Monitoring

    Later versions of Sybase include a monitoring view named monDeviceSpaceUsage that can help us monitor disk space within DPA. This monitoring view shows data for each file for all databases, but we can use some MIN and MAX logic to break that down to the drive level. The query will return multiple rows of free space percentages at the drive level. To create the alert within DPA, create an alert of type Custom - Custom SQL Alert - Multiple Numeric Alert and configure thresholds accordingly. Note...