How do you use cloud databases? Take the survey.
Options

I need to following information from SQL monitor captured performance metrics

ssh4478ssh4478 Posts: 8 Bronze 1
in Redgate Monitor
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
Tagged:
· Share on Twitter

Answers

  • Options
    Alex BAlex B Posts: 1,132 Diamond 4
    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
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
    · Share on Twitter
  • Options
    ssh4478ssh4478 Posts: 8 Bronze 1
    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
    · Share on Twitter
  • Options
    Alex BAlex B Posts: 1,132 Diamond 4
    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
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
    · Share on Twitter
Sign In or Register to comment.