How to view queries stored in the SQL Monitor data repository

While we provide a useful interface for this information, we often are asked how to access the queries stored in SQL Monitor as they are not plain text. 

NOTE: We do not support direct querying of the data repository so use of this is at your own risk, please take a backup of the data repository. The use of this or modification of the below is AT YOUR OWN RISK.

Requirements

A connection to the SQL Monitor data repository and appropriate permissions in SQL Server for the required actions.

Steps

You will need to enable CLR Integration on your database for this to work, as this process uses a compression utility.
Please see here for information on enabling CLR Integration: https://docs.microsoft.com/en-us/sql/relational-databases/clr-integration/clr-integration-enabling, and be sure that have the appropriate permissions to do this.

Then, install the attached CLR function into the SQL Monitor repository.

Once this is done, you can utilize the example query below to pull out the Query text, execution time and database name:

SELECT MAX(_ExecutionTime) - MIN(_ExecutionTime) [Execution time (ms)], utils.GZipToString(MIN(i._Querytext)) [Query text], MIN(k._DatabaseName) [Database] FROM 
data.[Cluster_SqlServer_TopQueries_UnstableSamples] us
INNER JOIN data.[Cluster_SqlServer_TopQueries_Instances] i ON i.Id = us.Id
INNER JOIN data.[Cluster_SqlServer_TopQueries_Keys] k ON k.Id = i.Id
GROUP BY i.Id ORDER BY MAX(_ExecutionTime) - MIN(_ExecutionTime) desc


You can then export the results into Excel.

Was this article helpful?

0 out of 0 found this helpful
Have more questions? Submit a request