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]
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