26 November 2009 14:34 david.castro

Using PowerPivot to read my perfmon Data

Last week I attended SQL Bits V and I can say it was a great experience it is definitely a must go event, if you missed it, keep you eyes open to SQL Bits VI. Anyway, on the Friday sessions we had Donald Farmer talking about PowerPivot for Excel/SharePoint 2010 and also the always energetic Brent Ozar talking about the use of Perfmon to monitor proactively our DB’s (I recommend his blog is full of useful stuff for all levels) and during the chat it hit me lest mix the two.

I like the idea of capturing key perfmon indicators as detailed by Mr Ozar but instead of dumping them into a csv to then import into excel and use the excel data mining tool I do a dump to a sql db. I then create a view on top of the 3 tables generated by perfmon and do some magic with PowerPivot. I have to say that the exercise took me around 2 days to capture the required data, setting up VM machine with SQL Server, Office 2010 and PowerPivot.

The actual PowerPivot work was under an hour, once you get use to the tool everything is simple adding extra columns on the fly using expressions etc…

The nice thing about having perfmon dumping data to the sql db is that the only thing I need to do now is refresh the data feed for PowerPivot and Bob’s your uncle!

I got the following result:

Not bad for an hours dev eh!!

Ok so here is how I did it:

1. On Performance Monitor Logs setup a job to grab the following (Brent Ozar has  great blog on this):

Memory – Available MBytes
Paging File – % Usage
Physical Disk – % Disk Time
Physical Disk – Avg. Disk Queue Length
Physical Disk – Avg. Disk sec/Read
Physical Disk – Avg. Disk sec/Write
Physical Disk – Disk Reads/sec
Physical Disk – Disk Writes/sec
Processor – % Processor Time
SQLServer:Buffer Manager – Buffer cache hit ratio
SQLServer:Buffer Manager – Page life expectancy
SQLServer:General Statistics – User Connections
SQLServer:Memory Manager – Memory Grants Pending
System – Processor Queue Length

2. Set up the dump to a db (Note you will need to do a run as on the server with an account with the correct access)

3. Create a view on top of the Perfmon Tables with the  following code:

   1: CREATE VIEW [DBO].[PERFMONDATA]
   2: AS
   3: SELECT          
   4:               DOD.DISPLAYSTRING
   5:             , CDT.MACHINENAME
   6:             , CDT.OBJECTNAME
   7:             , CDT.COUNTERNAME
   8:             , CDT.INSTANCENAME
   9:             , CDA.COUNTERDATETIME
  10:             , CDA.COUNTERVALUE
  11: FROM         DBO.COUNTERDATA AS CDA 
  12:                     INNER JOIN DBO.COUNTERDETAILS AS CDT
  13:                             ON CDA.COUNTERID = CDT.COUNTERID 
  14:                     INNER JOIN DBO.DISPLAYTOID AS DOD
  15:                             ON CDA.GUID = DOD.GUID

4. Fire up Excel 2010 and open the PowerPivot Plugin

5. Add Connection String to the instance hosting your Perfmon Db and pull the view created earlier

6. I used the expressions SECOND,MINUTE,HOUR,DAY,MONTH and YEAR to calculate those value on the fly, so you end up with something like this:

7. Use Pivot functionality from Excel and add a couple of sliders and you are Done.

I hope you found this useful

 

Cheers,

 

@dcastrogavino

Filed under: , ,

Comments

# My first useful powershell script – capturing performance counters

30 November 2009 10:52 by SQL and the like

After playing around with powershell a bit, I manage to do something quite useful.  There are a

# Collection and Reporting of Perfmon data for SQL Server ???Capacity Planning??? and ???Trend Analysis??? « SQLActions.com

Pingback from  Collection and Reporting of Perfmon data for SQL Server ???Capacity Planning??? and ???Trend Analysis??? « SQLActions.com