By Bob Duffy6. April 2011 11:43
As mentioned previously (http://blogs.prodata.ie/post/Using-MAP-Tool-and-Excel-to-Analyse-IO-for-SQL-Consolidation-Part-I-e28093-basic-distribution.aspx), the MAP tool has a nice database containing all your disk IOPS data and this is nicely contained in a single table called “performance_disk”
We can use PowerPivot to analyse the IOPs form different perspective and start to apply some logic such as: excluding non-business hours, seeing demand by date, time and other factors, and examining read/write ratios
I’ve attached a sample PowerPivot mashup that shows us some more details on one sheet (sample below)
As its PowerPivot we can do some funky stuff like drag the “ServerName” onto a chart to see the breakdown by actual servers to help locate who are the IO gobblers (see below)
Non BI folk can stop reading now. Go away and play with the PowerPivot sheet, refresh it against your MAPS database and hopefully its useful.
—————————————————————————————————-
For those BI folk, a few interesting challenges for PowerPivot:
- The IOPS is semi-additive as in we need to average the IOPS across time, but then sum it across servers to get the correct figure. We do this by simply averaging the IOPS and then multiplying by the number of unique servers. the number of unique server is determined by the DAX expression as below:
- For a better distribution graph we want to “band” IOPS into buckets. For example 20-25, 25-30 and so forth. To do this we can use the “ceiling” DAX function which is much more powerful than the TSQL equivalent as it allows for rounding up on large whole numbers like 5,10,50 or 100. the example below rounds up IOPS to the nearest two.
Download File – MAP_IO_Analysis.xlsx