Cluster_SqlServer_SqlProcess_UnstableSamples - What alert(s) write to this?

jpmauck

Can you help us figure out which alert type(s) write to the table Cluster_SqlServer_SqlProcess_UnstableSamples? Over the weekend we had this table grow to 100+ GBs and it nearly crashed the SQL instance that the server runs on.

I found a few articles indicating problems with this table in the past, but that was back in version 2/3. We are running 7.1.2.8327.

Adam W

The data from that sampler is used to used to generate the Blocking Query and Long Running Query alerts. It is also used to populate the "SQL User Processes (Top 10 by CPU)" panel in the server overview page.

You could disable this sampler via the XML config file or using the "ConfigurationFileEditor.exe" but you would then no longer get those particular alerts.

We are looking at improving this sampler to be more predictable about the % of the overall repository database it consumes, and to make it show more useful information about blocking processes and SQL user processes in the server overview.

I hope that answers your question, but let me know if you need any further information.

Alex B

@jpmauck

Just to add to Adam's reply - generally the size in that table comes from the _FullCommand column as it contains query text for the process, which in some instances can be quite large (and if it occurs a lot that adds up).

You can do something like:

SELECT LEN(_FullCommand) AS CharCount 
FROM data.Cluster_SqlServer_SqlProcess_UnstableSamples 
ORDER BY CharCount DESC

to get an idea if this may be the case (a lot of large queries).

Then, if you wanted to save space and didn't mind losing the query text there, you could UPDATE that column value to something like:

--This query text was <description of what is removed> and was x size and has been removed to save space
SELECT 1;

I would ensure you keep it as valid T-SQL in case some parsing happens that I'm not aware of, and should keep a backup at first just in case.

Also when doing this, the file size may not reduce, but the used space within the file would be reduced so you would possibly need to shrink the db to reclaim that space.

Kind regards,
Alex