Shared Files for Database Performance Analyzer
  • Global Monitoring Overview

    This custom query can be executed against the DPA repository database and it provides an overview of the monitoring status and other information. A prerequisite for this to work is that you need to implement this custom alert named Database Offline. This alert populates the status of each database into the DPA alerts table, and the attached script utilizes the information. If you have implemented the custom alert and named it differently than "SQL Database Offline", adjust the script as needed...
  • Global Deadlock Report

    To see a deadlock report for a single instance in DPA, drill into the instance and select the Deadlock tab on the Trends tab. This will show both a chart as well as deadlock details. However, if you want a global deadlock report across all monitored instances, the attached query will provide it. There are two results that will be output from this script: A summary table that lists each instance, the date and number of deadlocks that occurred A detail table that lists each deadlock Running...
  • Current Alert Status

    The attached script can be used to get the latest status of all alerts from the DPA repository. The result set will show the alert name, instance, latest status and last date the alert executed. This script can be executed from the DB Query Tool found in the DPA Options page or it can be executed from other query tools like SQL Server Management Studio (SSMS).
  • Historical Wait Times for All Instances

    This custom query will return historical wait time data for the specified timeframe, and y default it shows data for the last 90 days while summarizing to a daily level. This is similar data to the chart in the upper left on the Home screen, but will allow you to see more than 10 days of information. To run the query, login to the DPA repository with your favorite query tool like SSMS and execute it. To visualize it you can use Excel charts, PowerBI, SSRS or other charting tools. The top 2 lines...
  • DPA Metric query collector report Day tables.

    This query is to gather metrics from the DPA repository for reporting. There is a long time feature request to report on DPA's metrics in the product but, for now the data can be mined from the repository database. Notes on Structure. DPA stores the metrics in table sets for detail data and summary data. Detail data tables are names such as CON_METRICS_DETAIL_X where X is the ID of a registered instance in DPA in the table COND. Instance with ID of 1 would be CON_METRICS_DETAIL_1 etc. Summary tables...
  • DPA Metric query collector report Detail tables.

    This query is to gather metrics from the DPA repository for reporting. There is a long time feature request to report on DPA's metrics in the product but, for now the data can be mined from the repository database. Notes on Structure. DPA stores the metrics in table sets for detail data and summary data. Detail data tables are names such as CON_METRICS_DETAIL_X where X is the ID of a registered instance in DPA in the table COND. Instance with ID of 1 would be CON_METRICS_DETAIL_1 etc. Summary tables...
  • Alerts with Assigned Contacts Report

    This query will return the entire set of alerts that are defined in DPA, along with each threshold and the contacts assigned to the threshold. If a group is used as a contact, all contacts in those groups will be shown too. Webhooks and SNMP traps destinations are also shown. It's a great report to help make sure all alerts have the correct contacts tied to them. To run this report, download the attached script and run against the DPA repository database. The output will look similar to this:
  • Global Top Waits Across All Monitored Instances

    DPA can currently show Top Wait reports for a single instance using the Top Waits report or the Trends tab. However, this script will show the Top Waits across all instances that are currently being monitored. It uses hourly data, so it can only be used to show Top SQL for a max of the last 90 days (default). If a longer timeframe is needed, change the following line to use daily data which goes back 5 years by default: AND es.period = ''D'' -- use daily data Cursor to Loop Through Each Monitored...
  • Database Instance Availability Report

    A common request from our customers is to get an "Up Time" report from DPA. Although it is not available in the base product, we can use the attached query to mine the repository database and calculate up times for each instance. For this to work, first setup a Database Instance Availability alert which can be found under Alerts > Manage Alerts and choose the Administrative category. I typically run this type of alert every minute so I quickly know when an instance is down. In this case, this alert...
  • Top Databases by Wait Time

    This custom query will provide a list of database ranked by amount of wait times for the specified date range. Before executing script, set the instance id value which can be retrieved from the "select" statement a the top. Also, set the date range as needed for up to 90 days ago.
  • Global Top SQL by any Metric

    This is a global script that will return the Top SQL from all instances being monitored. It's the global version of this script: Top SQL by any Metric (single instance)
  • Top SQL by any Metric - Logical Reads, Executions and more

    This script will return all SQL statements for the given date range ordered by the metric of choice. See comments inside the procedure for more details on the variables used to tweak the output. It uses hourly data which is kept for 90 days by default. If you need longer timeframes, use 'D' in this line and you can summarize up to 5 years of data: WHERE ss.period = ''H'' -- hourly data, if you have a large time range, use ''D'' for daily data Note: If you get errors related to the Ignite schema...
  • Global SQL by Top Root Blocker Impact

    DPA shows blocking from two different perspectives, Top Root Blocker SQL and Top Waiting SQL. The top root blocker report is shown underneath the main trend charts and shows the SQL causing the most blocking issues, i.e. the culprit of the blocking and the SQL you need to possibly tune to alleviate blocking problems. This data is shown in the GUI on this chart underneath the main trend and anomaly charts: However, to find out more information about these queries, you need to click into the query...
  • SQL Server Plan Search - Find CONVERT_IMPLICIT Issues

    A customer found many examples of a SQL Server query that had to convert data in a column because the application passed in a data type different than the SQL Server table was defined. This resulted in a CONVERT_IMPLICIT against that column, which negated the use of the existing index. As a result, a full clustered index scan was required against the 10 million row table and DPA showed this nicely in the Table Tuning Advisor. The customer then asked if there was a way to search through DPA to find...
  • Global Top SQL Across All Monitored Instances

    DPA can currently show Top SQL reports for a single instance using the Top SQL report or the Trends tab. However, this script will show the Top SQL statements across all instances that are currently being monitored. It uses hourly data, so it can only be used to show Top SQL for a max of the last 90 days (default). If a longer timeframe is needed, change the following line to use daily data which goes back 5 years by default: AND ss.period = ''D'' -- use daily data Cursor to Loop Through Each...
  • Top SQL by Any Dimension

    DPA does not currently have a long term view of the Top SQL for many of the tabs at the top of the charts when drilling into an instance. You can only see this information in DPA if you drill into a day and a time, and then filter the list by the dimension you want. However, the DPA repository can be mined to obtain this information for the last 30 days (default data retention). Connect to the DPA repository with Management Studio, open a query and copy/paste the attached script. Modify the top...
  • Top SQL by Program

    DPA does not currently have a long term view of the Top SQL for a specific program/application. You can only see this information in DPA if you drill into a day and a time, and then filter the list by a program. However, the DPA repository can be mined to obtain this information for the last 30 days (default data retention). Connect to the DPA repository with Management Studio, open a query and copy/paste the attached script. Modify the 4 parameters at the top of the script as needed. Note...
  • VMware Resource Metric Detail

    DPA does not currently contain a report to show VMware metric details. The attached script can be used to retrieve values for a specified metric name (controlled by @MetricName variable) ordered by the collection timestamp. The values are typically collected once every minute. To execute the script, connect to the DPA repository with Management Studio, open a query and copy/paste the attached script. Modify the 4 parameters at the top of the script as needed. Note: this script uses the "ignite...
  • VMware Event History

    If you have configured DPA to monitor your VMware environment (Options > VMware Register), this custom query can be used to show all events for a specific VM. Events include powering on/off a VM, a moving a VM from one ESX host to another, etc. To execute this query, connect to the DPA repository using Management Studio (or other query tool), connect to the DPA repository, copy/paste in the attached script and modify the first 3 variables as needed. Note: this script uses the "ignite" schema...
  • Top SQL by Database

    DPA does not currently have a long term view of the Top SQL for a specific database. You can only see this information in DPA if you drill into a day and a time, and then filter the list by a database. However, the DPA repository can be mined to obtain this information for the last 30 days (default data retention). Connect to the DPA repository with Management Studio, open a query and copy/paste the attached script. Modify the 4 parameters at the top of the script as needed. Note: this script...