How do you use cloud databases? Take the survey.

Writing custom queries against repository for pulling SQL Monitor alert data

I'm trying to pull data from the repository about long running queries. To address the underlying problem, I need the SP name, function name, etc. from the process fragment. I'd like to be able to query this from the repository but I can't find the data I'm looking for. I realize the process fragment is probably BLOB but I can parse, etc. - I just can't find the table. Any thoughts of sample queries to allow me to pull date/time, query duration, and SQL process fragment for all alerts of a certian type?
Tagged:

Answers

  • Hi @ncs_parallax

    The table the query text for the Long Running Query alerts is stored in is [data].[Cluster_SqlServer_SqlProcess_UnstableSamples] in the _FullCommand column. The view [data].[Cluster_SqlServer_SqlProcess_UnstableSamples_View] will give you an idea of how it connects to other tables.

    However this is going to be changed in the near future to save space and I believe it will be located in it's own table so the query text isn't replicated and it may also be compressed in some manner at that time (similar to how the _QueryText is compressed in the [data].[Cluster_SqlServer_TopQueries_Instances] table). If it is compressed you will need to write your own application to extract this information and decompress it.

    Kind regards,
    Alex
    Product Support Engineer | Redgate Software

    Have you visited our Help Center?
Sign In or Register to comment.