Cluster_SqlServer_SqlProcess_UnstableSamples - What alert(s) write to this?

Comments

• Adam W Redgate › Posts: 111 Silver 3

  August 23, 2017 10:43AM

  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.

  0 · Share on Twitter
• Alex B Redgate › Posts: 1,157 Diamond 4

  August 25, 2017 1:22PM

  @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

  Product Support Engineer | Redgate Software
  
  Have you visited our Help Center?

  0 · Share on Twitter