SCOM: Customising the standard reports for greater presentation / functionality

Firstly I need to start by saying that it is not currently supported to modify the standard reports included in the core Management Packs. With good due diligence you should be able to prevent any accidental damage to the standard reports. The reason I mention the support factor is you would not be able to receive assistance from the Microsoft support team to achieve this. So make sure you use good development, test, and release processes for any of this work.

Now that has been established lets talk about why we might want to do this. Typically I have always created new custom reports from a SQL query that I already had or found online and I wanted to make the data either 1) easily consumed in a visual way 2) able to be automatically distributed. I was however faced with a very good reason by a customer recently and that was:

"Can we modify the standard built-in reports like Most Common Alerts so that the Report Parameters section is not included as it will contain a long list of Management Packs, or move that list to the end of the report. By having the long list of Management Packs at the start of the report we find report consumers (i.e. managers) were not seeing the value as they could not see the data as soon as they opened the report and had to scroll to page 3 or 4."

Now you could argue that it isn't much effort to scroll a page or two, but isn't the objective of a report to display data which is easy to consume. It is important to note that the Report Parameters being mention are part of a collapsible section which works in the report viewer, but when it is exported to PDF, Excel, etc it looses that dynamic capability and shows the values expanded.... hence the pages of data to scroll.

Another situation I can think of where this sort of modification might be needed is when you want to modify the way the data is visualized or add another visualization to the existing report. Now you can add parts from other reports into your report via linked reports.... but sometimes that just doesn't cut if for your needs.

Ok so onto the steps. I have broken the total process into the following phases, but have continued the step numbers throughout so you can follow where you are up to in the overall process.

Part 1: Designing/Creating Your Report
Part 2: Deploying Your Report
Part 3: Fixing the Report Parameters

Part 1: Report Design/Creation
1. Browse to the Reports web service

a) If you don't know the URL to your SSRS instance, in SCOM open Administration, Settings and the Reporting dialogue

2.  The reports will be stored in folders that represent the view you see under the Ops Console Reporting. Locate the folder containing the report you wish to modify. In our example we will use the "Microsoft ODR Report Library\Most Common Alerts". This report is found in the "ODR" folder within SSRS.

3. Something to be mindful of is that Linked Reports are used frequently within SCOM reporting. This means you may find the source report you are trying to edit is actually located in a different location. To identify if this is the case select "Manage" from the report menu.

Then within the details look for the "Link to" path. If this is present then you will need to locate that source report for the next steps. In my example the source report I need to locate is /Microsoft.SystemCenter.DataWarehouse.Report.Library/Microsoft.SystemCenter.DataWarehouse.Report.MostCommonAlerts.

4. Once you locate the report you wish to modify, download a copy of the report definition file (.rdl). There are two files for each SCOM report, we will explain the second file (.rpdl) later during Part 3. Download the .rdl file using the report menu.

5. Rename the file you just downloaded to a suitable name. This will be the name which eventually is displayed within SCOM, therefore following your organizations naming conventions is recommended (e.g. prefix the file name with your company name etc)


6. Open SQL Server Data Tools (aka Visual Studio), create a New Project of the type Report Server Project.

TIP: For simplified report development you can just use Report Builder, but due to the complexities and objects in the built-in reports I recommend using SQL Server Data Tools (aka Visual Studio with the Report Services templates).

7. From solution explorer right click Reports and select "Add > Existing Item"

8. Browse to the report file you downloaded and add it into your project. Open the report for editing.

9. Make the required change to the report layout. For our example we will remove part of the table which outputs the targeted objects, but the exact steps here will vary.

I've found in my case that this also left a blank space from the parent table, so I had to delete that row as well.

This whole step is all about report design and that skill is something far deeper than what I can go into in this blog, plus there are plenty of other blogs that cover the various techniques for designing report so I will stick to what you need to know for SCOM specifically.

10. Now that you have the layout of the report complete. You may find that if the report included any charts then there may be an area in the report showing "The custom report item type Enterprise.ManagementChartControl is not installed" such as in the following image.

This is because the SCOM reports are built with a custom charting control. While there are some blogs online that suggest methods for adding support for this control to your project, none of the information I found allowed modification to the chart. Therefore the easiest way I found to fix this is to replace the custom control with the standard Chart control which can be done with the following steps.

a) Delete the Enterprise.ManagementChartControl

b) Add a Chart to the report from the toolbox



c)  Edit the chart properties and associate it with the appropriate data set.
d) Make further adjustments to the Series and Values as required for your purpose.

11. Now proceed to make all other changes to the report as you require. You can remove other components or add new tables/charts with new data sets too. Just look at the way the existing components/reports are created and follow that configuration. As I mentioned this is largely a Report Design process which is not unique to this topic so I won't go into any more detail here.

12. Once you have made all the changes you require to the report layout, save the .rdl file.


Now there is another critical step here which is to configure the Report Parameters which is covered in Part 3 and I typically perform that step now, however for this article we will come back and fix that after deploying the report so that we can demonstrate why it is required showing why. In the future though you may choose to perform Part 3 here and deploy both the RDL and RPDL files together.



Part 2: Deploying Your Report
14. Locate the .rdl file you just created in SQL Data Tools (aka Visual Studio).

15. From Internet Explorer, browse to the Reports web service as we did in the very first steps.

16. If you haven't already done so create a unique folder for your custom reports. This should follow a good naming convention. To do this from the Home path of the Reports server, click New Folder and follow the wizard.

17. Navigate to this new folder in the ReportServer

18. From the menu select Upload File

19. Navigate to the .rdl file you created and upload it to the report server. Correct the name if required, however if you have followed a good naming convention earlier then this should not be required.

20. Before you can view the report we must associate it with the Shared Data Source, otherwise you will receive this error:
The report server cannot process the report or shared dataset. The shared data source 'DataWarehouseMain' for the report server or SharePoint site is not valid. Browse to the server or site and select a shared data source. (rsInvalidDataSourceReference)

To correct this select Manage from the report menu (suggest using Detail View for this)

21. Select Data Sources and scroll down to the definition of DataWarehouseMain.

a) Check if it reports that the reference is no longer valid. If it does then click the Browse button

b) Select the Data Warehouse Main data source in the root of the report server. Click OK. 

c) When returned to the Data Sources configuration for your report, scroll to the bottom of the screen and select Apply.

d) Your report is now associated with the data source and ready to display data to the user

22. Switch to the Operations Manager Console. Select the Reports in the Wunderbar (left menu). Locate and select the custom report folder you created to hold your modified reports.

TIP: If you do not see the report folder you created, try Refreshing the Reporting view as I have found the console cache occasionally prevents it from immediately displaying.

23. You can now select your report and perform common actions on it like Saving to favorites, scheduling, etc



Part 3: Fixing the Report Parameters
You may notice that the when you view your report the report parameters are not as interactive as other SCOM reports. The other thing you may notice is that if you modified an existing report it all of a sudden includes all these unexpected parameters (particularly if you had to locate a Linked report). For example the following is the view of our modified Most Common Alerts report:

The reason for this is that SCOM uses custom report parameter components which are built into the SCOM Report Viewer control. Now the report is usable at this stage, but you need to know the precise values which are expected by the particular parameters for it to display accurate data. The following steps will outline how to correct this and configure the parameters for the best user experience.


24. Earlier in Part 1 step 4 we mentioned a .rpdl file, we now must locate that file for the source report, or if you are building a report from scratch then a report with a similar parameter values (note you would need to have configured the queries the same way too during your design of a new report for this to work but that is outside of the scope of this article).

a) So in our case we navigate back to the path in Step 4 and download the .rpdl file.

b) Make sure you save it with the same name as your report (e.g. FourthCoffee.Report.MostCommonAlerts.rpdl).

TIP: This rpdl file contains the definition for the Report Parameter Definition Language. It is this file which maps the report parameter to a custom control within SCOM.


25. You can edit the file in your favorite XML editor, however if you have based your report on an existing report such as in the case of this article, and you didn't make any changes to the Parameters during report design, then you shouldn't need to make any changes.

The following screen shots shows the layout of the unmodified MostCommonAlerts parameter definition file. You can see here how it binds the parameters by name to particular controls within the SCOM report engine.

26. Upload the RPDL file to the Report Server in the same location as your custom report (.RDL) from Part 2 of these instructions.

27. Switch back to your Operations Manager Console and reopen the report. This time, if your file is correctly defined, you will be presented with the typical SCOM Report Parameter controls.

NOTE: These controls only work within the SCOM report viewer, they will not display when viewing the report natively in SSRS Report Server (even if you can work around the authentication prompt! ).

28. Make any final changes to the report parameter definition file (.RPDL) to configure the parameters as required. It is through this file which you should be configuring parameters to be visible (by removing their definition), however you may need to adjust the parameter configuration within your report as well depending on the changes you make.


Reference Material
The following are some of the articles I used while figuring out this process.

Report parameters block
http://blogs.msdn.com/b/eugenebykov/archive/2007/06/15/report-parameters-block.aspx

How to change built-in OpsMgr performance reports to show data in a timeline - Part2
http://blogs.technet.com/b/daniels_opsmgr_blog/archive/2011/01/27/how-to-change-built-in-opsmgr-performance-reports-to-show-as-a-timeline-part2.aspx

Using Linked Reports in SCOM to Create Custom Availability Reports for Specified Monitors
http://blogs.technet.com/b/emreguclu/archive/2011/10/13/using-linked-reports-in-scom-to-create-custom-availability-reports-for-specified-monitors.aspx



Conclusion

That's it, you have now modified a built in report from SCOM. Most of the practices used in this guide could be applied to custom reports as well to further enhance their usage within your environment (such as the RPDL).

Happy report writing and please send me feedback if you use this guide or come across anything further which may need to be explored during this process.


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.

SCOM: Optimizing the SQL Server Page Life Expectancy monitor

Something I come across a lot in the field when talking to people about the SQL Server Management Pack for SCOM is people claiming "The Page Life Expectancy monitor doesn't work" or "it's too noisy". In a lot of those cases the SCOM Administrators or even the DBA's are either about to or have just disabled the monitor. In my opinion that is the wrong decision and the "easy way out". In this post I will explain how I optimize this monitor for an environment.


Debunk the "it's broken" myth
Firstly lets talk about the usual reaction. Most people I talk to believe this monitor is broken. This is typically because this monitor will alert and then after some time resolve the alert (the intended behavior of a Monitor). The reason they reach this conclusion is because of the frequency at which this monitor will alert, but that is not because it is broken but because it has not yet been tuned for your environment.

I should also point at this time that just because you are receiving an alert for Page Life Expectancy doesn't mean that:

• You have Memory Pressure
• There are Buffer Pool issues
• That your server needs more memory

These are just some other myths that should be debunked. I'm not saying it isn't possible to use the Page Life Expectancy to qualify those situations, but there is more to it than that, so you shouldn't just use this counter alone to reach those conclusions.


Page Life Expectancy counter
The accuracy and way we must look at this counter has changed over the years as a result of improvements in hardware (e.g. NUMA), virtualization of infrastructure, reduced cost of memory allowing for very large physical machines.

Paul Randal has a very detailed write up about this counter and it is well worth reading
http://www.sqlskills.com/blogs/paul/page-life-expectancy-isnt-what-you-think/

Lets look at factors we need to consider with this counter and hardware configurations.

NUMA hardware
For NUMA hardware the Page Life Expectancy counter “SQLServer:Buffer Manager” is not always as accurate because it is a aggregated view of all Buffer Nodes. Paul’s blog describes this so I won’t go into the details, but essentially it can be more accurate to monitor the counter on the “SQLServer:Buffer Node” object rather than the consolidated object when using NUMA hardware.

Non-NUMA hardware
For Non-NUMA hardware, the “SQLServer:Buffer Manager” is no longer an aggregated value, so this is generally what will be targeted for monitoring. As it is impossible to create a single collection rule in SCOM to account for all possible scenarios, this is also the counter which has been chosen, largely due to legacy but also because in a world of increasing acceptance for virtualised environments then this is still the typical counter used in most scenarios.

Threshold
One of the main challenges is that the “suggested” threshold that most DBA’s apply, and monitoring systems use, including but not limited to the SCOM SQL Management Pack, is based on generalized hardware specifications and usage patterns, unfortunately this does not necessarily taken into account the advancements of hardware and increases in memory allocations. It is also impossible to have one threshold that covers all the various hardware configurations and application usage patterns we have these days.

The reason that this is so important is that we need to look at and tune the threshold being monitored based on the hardware specifications and usage patterns specific to the individual environment. For example monitoring this value on a Virtual Machine with 4 Gb of RAM is likely to have a vastly different pattern to a physical machine with NUMA hardware and 96 Gb of memory. The same can be said when comparing a transactional application (OLTP) verses a dataset intensive application (OLAP) applications.

Application behavior
The other input to this counter is the fact that application behavior can have a significant impact on it's value. For example if the application suddenly imports a large volume of data, this has to pass through the buffer pool and if your buffer pool is already full (which ideally it should be), then the database engine will need to drop the clean pages from the buffer pool to free up room for this new data. This process will have an impact on the Page Life Expectancy value and it should take a dip. The following graph shows an application usage pattern where there are different datasets consistently being read into the buffer pool and the engine having to make room for these by dropping clean buffers first, thus reducing the life expectancy of a single page. This behavior on it's own may not be impacting on end user experience and it is not always viable to add sufficient memory to a server to host the entire database in memory (nor should you want to without considering some of the new alternative features). Although the monitor could alert depending on the threshold in place, knowing the application usage pattern allows you to determine if this is expected or not.

Optimizing the Page Life Expectancy counter for your environment
The typical way I approach this monitor is to leave the threshold value as default but when it alerts in the console I use the following process to optimize it for the environment. It is important to note that there is no "one size fits all" or "magic wand" you can use here if you want to monitor this correctly.

This is the process I typically follow.

1. Receive the alert or run a report on servers which have alerted for low Page Life Expectancy
2. Open the Performance View in SCOM for the affected server and select the Page Life Expectancy counter
3. Review the behavior over an extended duration. There is no point looking at just a few hours here, you need to understand the server/application behavior over a week, month, or longer to effectively decide on a more suitable threshold for this counter.
4. Review the hardware configuration of the affected server to determine if there is a suitable threshold based on the total memory allocated to SQL (see Paul Randal’s blog for an equation)
5. Adjust the monitoring as required using one or more of the following approaches
1. Depending on the typical hardware configuration in your environment you may choose to lower or increase the threshold of the base monitor, and then use groups to manage the exceptions such as the following points. I typically choose to leave the threshold as is and use 2 groups to manage the exceptions of either high or low baselines, particularly in an environment of a mixture of hardware configurations (VMs vs Physical and small Memory vs large Memory allocations)
2. If the threshold needs to be lowered for a selection of servers, then I typically create a group to hold the specific servers and a suitable override for that group
   E.g. Group "SQL Servers Page Life Expectancy Low" with threshold of 100.
3. If the threshold needs to be increased for a selection of servers, then again create a group to hold that specific server list and a suitable override for that group.
   E.g. Group "SQL Servers Page Life Expectancy High" with threshold of 900.
6. Continue to monitor the server behavior and ensure the changes are appropriate to the application usage pattern.
7. Routinely review the thresholds of any overrides and applicable group membership for any groups to ensure they are still applicable.

When evaluating the behavior of the server, it is also important to review the supporting counters like the Batch Requests/sec, Buffer Cache Hit Ratio, Page Reads/sec, Page Lookups/sec, Total Server Memory, Target Server Memory, and related counters to determine if there is in fact an issue with the configuration of SQL Server or just typical application behavior for that server.



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.

PowerShell: Identifying Strongly or Weakly Typed Variables

PowerShell is a loosely typed language, which means we can run something like this and PowerShell will automatically select the data type for us.

$var = 123;
$var.GetType();

Results in: System.Int32

And if we go and change the value to a string, PowerShell just naturally changes the datatype as required for us.

$var = "string";
$var.GetType();

Results in: System.String 

Now if you are like me and grew up in a strict language, then you may prefer to Strongly Type your variables. There are actually very good reasons for doing this as it guarantees the data type you will be working with, particularly from user input (although there are input validation methods you can use in addition to this too).

To strongly type a variable we prefix the variable name (at creation) with the desired type.

[string]$var = "string";

Now this what I would consider good coding practice. So how do we determine if variables have been strongly typed. The following script will create an array to hold all known system variables, because we don't want to mess around with them. Then it will use Get-Variable and look at the Attributes property of a variable to determine if the type conversion was set. Now this method could also be used for some of the other attribute options (e.g. range validation, etc)

<#
This script can be used to check for Strongly and Weakly typed variables defined within the session
Things to note:
- Variables created in child scopes which have ended will not be visible
- Some System Variables may appear in the list
#>

#create an array to hold all the known system variables (variables from other modules are not included here)
$SystemVars = @('$'
    , '?'
    ,'^'
    ,'true'
    ,'false'
    ,'args'
    ,'Error'
    ,'ErrorView'
    ,'ExecutionContext'
    ,'FormatEnumerationLimit'
    ,'HOME'
    ,'Host'
    ,'input'
    ,'ConfirmPreference'
    ,'ConsoleFileName'
    ,'DebugPreference'
    ,'ErrorActionPreference'
    ,'MaximumAliasCount'
    ,'MaximumDriveCount'
    ,'MaximumErrorCount'
    ,'MaximumFunctionCount'
    ,'MaximumHistoryCount'
    ,'MaximumVariableCount'
    ,'MyInvocation'
    ,'NestedPromptLevel'
    ,'null'
    ,'OutputEncoding'
    ,'PID'
    ,'profile'
    ,'ProgressPreference'
    ,'PSBoundParameters'
    ,'PSCommandPath'
    ,'PSCulture'
    ,'PSDefaultParameterValues'
    ,'PSEmailServer'
    ,'PSHOME'
    ,'psISE'
    ,'PSScriptRoot'
    ,'PSSessionApplicationName'
    ,'PSSessionConfigurationName'
    ,'PSSessionOption'
    ,'PSUICulture'
    ,'psUnsupportedConsoleApplications'
    ,'PSVersionTable'
    ,'PWD'
    ,'ShellId'
    ,'StackTrace'
    ,'SystemVars'
    ,'VerbosePreference'
    ,'WarningPreference'
    ,'WhatIfPreference'
)

#view all variables and report if Weakly or Strongly typed
Get-Variable -Exclude $SystemVars | Select Name, @{Name='TypedState';Expression={
    If ($_.Attributes -match 'System.Management.Automation.ArgumentTypeConverterAttribute') {'Strong'} else {'Weak'}
}};

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.

Two new PowerShell v5 features worth waiting with bated breath or becoming an early adopter for

Recently there were a two new PowerShell v5 Preview features which IMHO are well worth waiting with bated breath for..... or definitely becoming a WMF 5.0 early adopter for :)

The first is the ISE extension for finding and installing modules. This feature takes advantage of the new PowerShellGet module. Best of all the PowerShellGet functionality helps our community overcome the module management problem, and this feature provides an easy to use GUI for discovering modules on community or company repositories.

ISE Module Browser - A new way to manage your PowerShell modules
http://blogs.msdn.com/b/powershell/archive/2015/02/23/ise-module-browser-a-new-way-to-manage-your-powershell-modules.aspx


The second is a static code analyser for PowerShell scripts and modules. Taking a plugin written by a colleague the PowerShell team have now included the functionality in the WMF 5.0 framework (Feb release). This analyses your script against known patterns and practices. Well worth running against your scripts even just to satisfy curiosity, I know I will be.

PowerShell Script Analyzer: Static Code analysis for Windows PowerShell scripts & modules
http://blogs.msdn.com/b/powershell/archive/2015/02/24/powershell-script-analyzer-static-code-analysis-for-windows-powershell-scripts-amp-modules.aspx

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.

Azure: How to utilise the Primary and Secondary storage access keys

One topic that comes up a bit when people first start to deal with Microsoft Azure storage is "what is the purpose of the Primary and Secondary storage access keys". The good news is that the guidance/documentation provided for Azure services and features is very rich in depth, and even includes screenshots. I've linked to the best documentation below.

The main purpose of the Primary and Secondary keys (and this doesn't just apply to storage resources), is to minimize application impact when the keys need to be regenerated. In fact the quote from Azure documentation is: “By providing two storage access keys, Azure enables you to regenerate the keys with no interruption to your storage service or access to that service.”

Lets take the scenario of a trusted employee has left the organization. This employee was either the Azure Account Administrator, a Developer, or for whatever reason had access to one or both of the Storage Access Keys for a critical storage blob on your Azure account. You also have a critical online application hanging off this blob storage where downtime costs you $$$$.

With only a Single Access Key the process would have to be the following which depending on the time between steps 1 and 4 could be a considerable outage for the application.

1. Stop the online application
2. Regenerate the Access Key
3. Update the application configuration
4. Start the application

Maybe in the above scenario you don't even have the ability to stop the application, then the impact of this becomes even more sever because your users will receive an error as the application code fails to use the out of date key.

Now with the Primary and Secondary Access Keys the scenario has a few more steps but far less if not zero outage to the application.

1. Regenerate the Secondary Access Key.
   This is important as the employee may have had access to this key as well and if they left under "bad terms" this may make this even more critical. Additionally you should regenerate keys routinely, so by doing this first you can test that the the secondary key works and should you have any issues then immediately stop this process and revert to the existing Primary Key while investigating what failed in your application.
2. Update the Application Code to use the new Secondary Access Key
3. Pause, take a breath….
   Give your some some time depending on your application to ensure all active processes have finished and new processes switched over to the new key.
4. Check your application is working off the Secondary Key and is successful. If not then you might want to revert to the existing key while you find the other places you need to update the Storage Account details.
5. Regenerate the Primary Access Key.
6. Update the Application Code to use the new Primary Access Key
7. Repeat steps 3 and 4

The best document to review regarding this topic is: http://azure.microsoft.com/en-us/documentation/articles/storage-create-storage-account/


There are some caveats when dealing with keys on storage for Virtual Machines, Media Services, and Applications (but I cover that above). For the full details see the above link but these are the extracts for reference:

Virtual Machines - If your storage account contains any virtual machines that are running, you will have to redeploy all virtual machines after you regenerate the access keys. To avoid redeployment, shut down the virtual machines before you regenerate the access keys.

Media services - If you have media services dependent on your storage account, you must re-sync the access keys with your media service after you regenerate the keys.

My suggested best practices

1. Routinely regenerate your keys so you can more easily track who has access to the environment
1. Do NOT regenerate keys on storage holding virtual machines, unless this can be factored into your monthly/quarterly patch maintenance
2. Use separate Storage Accounts for Virtual Machines and other application storage (e.g. Tables, etc).
3. Run the application off the Primary key, and only the Secondary key during key regeneration.
4. If you must provide access to the storage to an employee/developer/etc..... use a Shared Access key.

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.

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.

GoPro Studio Importer causing High CPU

Ping back to http://blog.notmyfault.ch/?p=427

Lately I have noticed my laptop experiencing the following symptoms:
- Running Hot
- CPU constantly at >50%
- Slow to load things

After using Process Explorer (Sysinternals) I could see this was related to a WMI Provider Host (WmiPrvSE.exe) running as NETWORK SERVICE. The strange thing is the performance profile as it constantly spikes for a second, drops, then spikes back for a second, drops... repeat. Clearly indicating that there is a query being run at very frequent intervals as evident by this chart.

I found it difficult to track down the culprit causing the WMI queries, but I knew the problem was still evident after a reboot so I started checking my tray icons and start up applications. By process of elimination I found that closing the GoPro Studio Importer the problem went away.

The importer is run from the executable: C:\Program Files (x86)\GoPro\Tools\Importer\GoPro Importer.exe
GoPro Studio version: 2.5.4.404

To validate this I launched the executable and the problem returned.

I haven't found any settings in GoPro Studio to prevent the importer launching at startup, so I simply went to the Startup tab in taskmgr and disabled it from there.


Props to the blog http://blog.notmyfault.ch/?p=427 which helped to clarify my findings.