Using Crystal Reports with the Portal to Produce Customized Capacity Planning Reports

by Phil

A Performance Sentry Portal user  emailed support wanting to know how to use Crystal Reports to query the MS-SQL database to generate three basic management reports to illustrate:

  • Average CPU usage in a predefined period (monthly, quarterly, etc.)
  • Average memory usage (PF usage) in a predefined period (monthly, quarterly, etc.)
  • Average disk usage in a predefined period (monthly, quarterly, etc.)

The user wanted to know what fields in the database should be used to generate the report.  I thought this information would be valuable to all users so much of our response is posted here.

Here are the metrics you’ll need for each of the reports that you wish to generate:

Average CPU Usage – Reported in the <computername>@System table in the column ‘PCT_Total_Processor_Time’.  An additional note, there is a column in the ‘Processor’ table by the same name.  This table reports ‘by processor’ meaning the metrics are reported for each logical processor on the machine.  In other words, a 4-way machine will have metrics for processors 0,1,2,3 and you can get cpu utilization for the interval by summing the values of each processor for the interval.

Average Memory Usage –  Here’s where Performance Sentry shines.  Nowhere does Microsoft report the amount of memory being used.  It only reports ‘available bytes’ (or Kbytes, or Mbytes). That number by itself is meaningless, unless you know the amount of memory on the machine.  Performance Sentry knows that and is able to report ‘Percent Available Bytes’.  This is reported in the <computername>@Memory table in the column ‘PCT_Available_Bytes’. Therefore to report memory usage, you would use the formula:

Percent Memory Usage = (1 – (PCT_Available_Bytes/100)) * 100

An additional note, Performance Sentry reports the amount of memory installed on a machine in the ‘Machines’ table in the column ‘sizeofRAM’ If you are using an option to report ‘denominator’ or ‘base’ counters (the values used to get the PCT calculations) then the same value will be in the <computername>@Memory table in the column ‘PCT_Available_Bytes_Base’.

Average Disk Usage – You’ll have to calculate this using the ‘PCT_Free_Space’ column in the <computername>@LogicalDisk table:
Percent Disk Used = (1 – (PCT_Free_Space/100)) * 100
Another thing to consider is the size of the disk.  Microsoft doesn’t report this, but if Performance Sentry is recording ‘denominator’ or ‘base’ counters, then the size of the disk can be determined by looking at the ‘PCT_Free_Space_Base’ column in the <computername>@LogicalDisk table.  This value is reported in Megabytes, so to report in Gigabytes, divide the value by 1024.

You can use Crystal Reports to produce the averages of the metrics since the database you’re accessing stores the values reported in one minute collection intervals.

{ 0 comments }

Using a Server Performance Monitor to find Code Problems

by Steve

If you are reading this blog you already know that server performance monitoring is valuable for a number of reasons, but one thing that you may not have considered is using historical performance data to help to indentify problems introduced to a system by new programming code.

A long time Performance Sentry user of ours is an Internet company that does not have the budget for a dedicated performance team.  In fact, they don’t even have the budget for a dedicated performance person. Though, they did see the need to collect and retain server performance data to help to identify and solve performance problems for when they would pop up.

This particular customer runs Performance Sentry to collect performance data on eight different Internet facing servers. That data is then processed nightly by the Performance Sentry Portal for reporting and trend analysis purposes.

This customer doesn’t often experience performance problems, so they only review performance reports every couple of weeks.  In March they noticed an uptick in CPU usage in two of their servers.  The uptick was not dramatic, but it was noticeable.    They made a note to keep an eye on those servers.

The next time the reports were run those same two servers had periods where the processor queue length had reached alarming levels and CPU had begun to bounce around 100% for brief periods of time.  Clearly something would need to be done, just not quite yet.  The reports were placed in a “needs to be reviewed” stack.

Two servers with high CPU and Processor Queue lengths

The very next day our customer received their first customer call complaining of slower than normal response times.  Well, as you can image that set off alarm bells and caused those performance reports to move to the top of the stack.

Our customer decided to again utilize the Portal to try and figure out exactly when the processor queue lengths began to increase.  Because they store historical data in the portal they were able to quickly pinpoint the exact date and time when the trouble started.

They used this information to go through code change logs to see what changes occurred when the problem began to present itself.

Well to make a long story short they were able to determine that some folder creation code had been changed and that new code was causing the problem.  That code was re-written and put back into one of the production servers at about 2:30 PM.  The results of the change can be seen in the graph below.

As you can see both CPU and the Processor Queue Length dropped right back to acceptable levels.   Our customer was thrilled to be able solve what could have been a tricky problem by quickly isolating the time the problem was introduced into their system.

Single Server after the code fix

This is a great example of the importance of retaining performance information.  Many smaller companies use only occasional real time snapshots for server performance information.  That works well if you’re just concerned with brief windows of time.   But for this company, having access to historical data proved to be invaluable in solving their problem quickly.

We thank them for sharing this success story as well as a few of their Performance Sentry Portal graphs with us to share with this post.

{ 0 comments }

Performance Sentry VM Available for General Beta

by Steve

After months of development and testing Performance Sentry VM, our Performance Monitor for ESX Server, is available for general beta testing.

Performance Sentry VM runs as a service in Windows Vista, Windows 7, Windows Server 2008 or Windows Server 2008 R2.  It can run in a VM guest, or a machine external to the ESX host.  It connects directly to the ESX host server and using APIs retrieves the host and guest metrics and formats them into Windows Performance objects and counters viewable in Perfmon.  The metrics can then be collected by Performance Sentry (NTSMF) or any other software that collects Windows Performance metrics,  such as Microsoft’s Systems Center Operation Manager (SCOM).

Performance Sentry does not require vCenter or vSphere.

Here is a short video that shows Performance Sentry VM data being consumed by Perfmon.

You can find an HD version of the video on the Demand Tech website here.

Reblog this post [with Zemanta]

{ 0 comments }

Next Entries →