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...
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.
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)
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
