Shared Files for Database Performance Analyzer
  • 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...
    • 14 Dec 2022
  • 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...
    • 13 Dec 2022
  • 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...
    • 13 Dec 2022
  • 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...
    • 13 Dec 2022
  • 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...
    • 13 Dec 2022
  • Resource Metric Report

    DPA does not include a report for Resource Metric values shown on the Resources tab in the GUI. However, the DPA repository can be mined with the attached query to retrieve any metric that DPA is collecting. Metric Names The metric name used in an example script is named SQL Disk Read Latency, but other metric names can be found in the ignite.con_metrics_names_ID table. The ID value can be replaced with the ID value from the "select id, name from ignite.cond order name name" query. Executing...
    • 13 Dec 2022
  • Top SQL by Database User / Login

    DPA does not currently have a long term view of the Top SQL for a specific database user, e.g. SQL Server login. 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 user. 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...
    • 13 Dec 2022
  • SQL Server Indexes Not Used

    Introduction With any database platform, unused indexes consume space that they may not need to. Identifying unused indexes in SQL Server is a hot topic on many sites, but most of them only discuss part of the "usage" definition. With this script, we think about two definitions for non-usage: Indexes that have never been used - the classic case Indexes that have been used, but only have had updates against them - these are indexes that have insert, update and/or delete SQL commands executed...
    • 13 Dec 2022
  • Top New Queries by Total Wait Time

    When an application using one of your databases is modified, e.g. an upgrade, a new application, etc, new queries can appear based on those changes. Within DPA, you can run a Top SQL report or drill into the day a change was made, and probably see the queries that are new, assuming they are causing a performance issue. However, this script will provide the list of all new SQL statements, ranked by wait times. With this data, it becomes easier to head off any performance issues because of the application...
    • 5 Dec 2022
  • Global Index Advisor with Time Savings

    Now that DPA has added the Index Advisor feature (affectionately called the What If Advisor, as in what if I add this index), when I show it to people, they often ask to see the advice across all instances rather than for a single instance. A goal of the feature is to help you understand which suggested indexes would save the most time in your instance, so it makes a lot of sense to see this globally as well. In other words, which index will be the most helpful in my entire environment. DPA Data...
    • 2 Dec 2022
  • CONSW Column Definition

    Many of our custom queries on this site utilize the CONSW tables within DPA. There is a CONSW table for each monitored instance, so the tables you will see are named CONSW_XX where XX is the ID value of the instance from the COND table. This CONSW tables store the last 30 days of second-by-second detailed data. You can think of it as a fact table with several dimension tables hanging off it, i.e. a mini star schema. When the DPA product was first created, this was a main part of the architecture...
    • 2 Dec 2022
  • Global Search and Display of Queries

    Recently, a DPA user asked if we could show queries globally across all instances they are monitoring. The specific ask was to show queries across all instances that have run for more than an hour in total within a selected date range, and be able to provide a search string for the SQL statement itself. DPA Data Source (CONSW) DPA does not currently have a global view for queries, but the data is stored in the repository so a custom query could be used to combine data from all instances. To...
    • 2 Dec 2022