Writing custom queries against repository for pulling SQL Monitor alert data
ncs_parallax
Posts: 1 New member
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
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
Have you visited our Help Center?