I need to following information from SQL monitor captured performance metrics

ssh4478

Hello,
I need to capture following details from SQL monitor metrics (backend tables/views) for specific duration:

Count
Duration(Total,min,max)
CPU(Total/Avg)
Reads(Total/Avg)
Writes(Total/Avg)
 for all the Queries executed on the databases between certain time.
I need to use the above information to calculate a figure which will give us more info at the troubled time.

Is it something possible and if you can offer any help with it

Alex B

Hi @ssh4478,

We don't support directly querying the repository at the moment and the schema is liable to change with any update.  The date you are talking about there is the information from the Top Queries section on the server overview page when you click into the SQL instance and you can sort the table there by the various columns to get a different table sorted by that value (rather than just reordering the same list of queries).  You can also drill into each database at the bottom of the server overview and see the Top Queries list specific to each database.

Having said that, if the above doesn't help, these views may help you find the data in the repository (The DECOMPRESS function is from SQL 2016 I believe, otherwise you'd need to find a way to unzip it via a SQL CLR function I believe).

SELECT csstqiv.*
     , CAST(DECOMPRESS(CAST(CAST(csstqi.Cluster_SqlServer_TopQueries_QueryText  AS varchar(MAX)) AS varbinary(MAX))) AS varchar(max)) as decompressedText
FROM data.Cluster_SqlServer_TopQueries_Instances_View AS csstqiv;

SELECT * FROM data.Cluster_SqlServer_TopQueries_StableSamples_View

SELECT * FROM data.Cluster_SqlServer_TopQueries_UnstableSamples_View

Kind regards,
Alex

ssh4478

Thanks Alex, I am looking into above views , jsut wanted to clarify the purpose of stable and unstable sample views (what it does etc)? Many Thanks

Alex B

Hi @ssh4478,

The stable samples are only recorded if the value has changed, the unstable samples are recorded at each sample regardless.  So the different names are a comment on how likely the data is to be volatile and how to interpret the data in the table.

Kind regards,
Alex