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 do that, and also show other dimensions of data like the database that the query ran in, we can use the detailed data tables for DPA. The main table for this data is named CONSW_XX where XX is the ID of the monitored target. Each instance has its own set of tables, so the query we develop will need to combine data from several tables.
For reference, here is a description of the CONSW table and its columns (the original DPA architects obfuscated the column names to keep some of the things a secret).
In this case, the resulting query needs to show each database the queries executed in as well as the total times and other key information. Based on the CONSW definition above, we need to join it using the IXOY column to the CONO table where database information is stored.
Combining Multiple SQL Text Rows (CONST)
Another tricky thing to tackle is that SQL text is stored within the DPA repository as several 8000 character rows in a table, and these need to be combined to be able to search for a piece of the query. In this example, I used the STRING_AGG function and a CTE within the query. This is then joined to the CONSW table in the main part of the query:
WITH st (sqlhash, ProcName, sql_text) AS ( SELECT sqlhash, ProcName, SUBSTRING(sql_text, ss, CASE se WHEN -1 THEN DATALENGTH(sql_text) ELSE se END - ss+1) AS sql_text FROM ( SELECT h AS sqlhash, MIN(ss) AS ss, MAX(se) AS se, MAX(pname) as ProcName, STRING_AGG(CONVERT(varchar(max), st), '''') WITHIN GROUP (ORDER BY h ASC, p ASC) AS sql_text FROM ignite.const_'+@instance_id+' GROUP BY h) a
Cursor to Loop Through Each Monitored Instance
The other somewhat tricky thing to tackle is combining multiple CONSW tables. For this, I used a cursor to loop through each monitored instance (stored in the COND table), run the main query as a dynamic query and store the results in a table variable named @Results. Finally, the SQL batch returns the combined results and orders it by the total_wait data.
Variables to Control the Search
At the beginning of the script are four variables that control the minimum amount of time to consider (60 seconds by default), the date range to search, and the portion of the query text to search for. Obviously the more monitored instances you have, the longer this query will take to execute, so if you want to limit the number of instance in any way, modify the cursor definition to limit the number of rows retrieved from COND.
How to Execute this Script
Download the attached SQL script, and run it in the DPA repository using your favorite tool like SQL Server Management Studio (SSMS).
