Thursday, January 22, 2015

SCOM: Managing Data Aggregation in the OpsMgrDW database


One of the common discussions I have when work with a customer on their Operations Manager (SCOM) environment is how the data aggregation works and how that impacts on the database size. There are plenty of blogs around how to review the data retention periods, my favourite is:

http://blogs.technet.com/b/kevinholman/archive/2010/01/05/understanding-and-modifying-data-warehouse-retention-and-grooming.aspx

So now that you have read that blog and know that the defaults are a bad idea. How do we actually determine the data size that each aggregate dataset takes up?

The typical answer is to use the dwdatarp.exe tool (available from http://blogs.technet.com/b/momteam/archive/2008/05/14/data-warehouse-data-retention-policy-dwdatarp-exe.aspx) however some organizations may have change and security restrictions which prevent the use of such tools. Alternatively people may just be more comfortable with seeing exactly what data is being accessed..... That is the scenario I was faced with this week so I have come up with the below SQL query which will give you the output of each aggregate dataset, the total size, and the percentage of the total database size.

--Which DataSet is usin the most database space (alternative to using dwdatarp.exe
USE OperationsManagerDW;
WITH TableSizes AS
(
 SELECT so.name,
  Coalesce(8 * Sum(CASE WHEN si.indid IN (0, 1) THEN si.reserved END),0) AS data_kb,
  Coalesce(8 * Sum(CASE WHEN si.indid NOT IN (0, 1, 255) THEN si.reserved END), 0) AS index_kb,
  Coalesce(8 * Sum(CASE WHEN si.indid IN (255) THEN si.reserved END), 0) AS blob_kb
 FROM dbo.sysobjects AS so JOIN dbo.sysindexes AS si ON (si.id = so.id)
 WHERE 'U' = so.type 
 GROUP BY so.name 
)
SELECT (CASE WHEN CHARINDEX('_',so.name) > 0 THEN LEFT(so.name,CHARINDEX('_',so.name)-1)
 ELSE so.name
 END) as dataset,
 Coalesce(8 * Sum(CASE WHEN si.indid IN (0, 1) THEN si.reserved END),0) +
 Coalesce(8 * Sum(CASE WHEN si.indid NOT IN (0, 1, 255) THEN si.reserved END), 0) +
 Coalesce(8 * Sum(CASE WHEN si.indid IN (255) THEN si.reserved END), 0) AS totalsize_kb,
 CAST
   (1. * (Coalesce(8 * Sum(CASE WHEN si.indid IN (0, 1) THEN si.reserved END),0) +
   Coalesce(8 * Sum(CASE WHEN si.indid NOT IN (0, 1, 255) THEN si.reserved END), 0) +
   Coalesce(8 * Sum(CASE WHEN si.indid IN (255) THEN si.reserved END), 0))
   / (SELECT SUM(data_kb + index_kb + blob_kb) FROM TableSizes)
   * 100
    AS DECIMAL(5,2) ) AS PercentOfTotal
FROM dbo.sysobjects AS so JOIN dbo.sysindexes AS si ON (si.id = so.id)
WHERE 'U' = so.type 
AND (so.name like '%daily%'
OR so.name like '%hourly%'
OR so.name like '%raw%')
GROUP BY (CASE WHEN CHARINDEX('_',so.name) > 0 THEN LEFT(so.name,CHARINDEX('_',so.name)-1)
 ELSE so.name
 END)
ORDER BY totalsize_kb DESC

In my lab environment, which is not doing anything, I get the following output. Typically though you should see PerfHourly as the highest user when the defaults are in effect.



With this information it becomes much easier to qualify the value in adjusting the aggregate table grooming setting in the blog above.



Legal Stuff: As always the contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

5 comments:

  1. Invest in an heirloom to cherish forever with a timepiece from the fake cartier watches collection. A fusion of cutting-edge technology and heritage craftsmanship, replica calibre de cartier carbon diver watchesCartier’s fine watches carry the replica calibre de cartier carbon diver watches prestige of the brand’s illustrious history and exceptional quality.

    ReplyDelete
  2. Paris Fashion Week would Replica handbags uk have been incomplete without replica Yves Saint Laurent bags Anthony Vaccarello’s homage to maison founder Yves Saint Laurent in the Saint Laurent spring/summer 2019 collection.

    ReplyDelete
  3. Louis Vuitton himself was born the son of a miller in 1821 in Anchay, a hamlet in the Jura Mountains, not far from the Swiss border. Replica bagsThe region was a poor one - serfdom had only been abolished less than 40 years previously, so Louis left to seek his fortune when he was a teenager, arriving in the French capital aged 16. This was the Paris of Victor Hugo's Les Misérables, with nearly one million inhabitants. As the composer Chopin said in a letter to a friend at the time, "Here you find the greatest luxury and the greatest filth, the greatest virtue and the greatest vice."replica Louis Vuiton bags

    ReplyDelete
  4. A heritage brand with a cult following, Moncler makes cold weather effortless to maneuver. cheap moncler jackets moncler Super soft puffer jackets and packaway hooded styles offer a variety of choices that'll each prove indispensable throughout the cheap moncler short-outerwear seasons' increasingly unpredictable climes.

    ReplyDelete
  5. Montblanc MeisterstückClassique is not surprisingly regarded as one replica mont blanc ballpoint pen of Montblanc's top ten fountain pens, replica great characters edition and perhaps the most iconic of the Montblanc writing series. This pen is coated with precious black resin and has a bright white star badge. It is a must-have for collectors and has a well-known name, with German as a "masterpiece". Each pen has a separate serial number, which means that no two pens are the same. Similarly, each nib of Montblanc is individually stamped after manual testing by Montblanc's master craftsmen.

    ReplyDelete