SQL Centre of Excellence

The Microsoft Assessment and Planning Toolkit is a fantastic tool for helping gather resource usage for a SQL consolidation / virtualisation project.


One thing that is quite basic though is its presentation of disk IOPS (see below). This article shows how to analyse the data in a bit more detail using the underlying SQL database and excel.


1) IOPs distribution graph

This is useful for seeing the distribution of IOPS so we can determine what percentage of IO falls within percentile boundaries. For example on the above example, would we want to purchase a device only capable of performing 15 IOPS. Probably not as this means that only 50% of IO is within performance targets. We may want 80% or 95% of IO coming in within performance targets. So how do we determine this….

a) Locate the database on the “MAPS” instance and query the database for IO broken down by 15 minute periods:

SELECT     CAST(FLOOR(CAST(dbo.performance_disk.collection_datetime AS float(53)) * 24 * 4) / (24 * 4) AS smalldatetime) AS time, 
                      CEILING(SUM(dbo.performance_disk.disk_transfers_per_sec) / COUNT(*) * COUNT(DISTINCT dbo.performance_disk.device_number)) AS IOPS
FROM         dbo.performance_disk INNER JOIN
                      dbo.devices ON dbo.performance_disk.device_number = dbo.devices.device_number
WHERE     (dbo.performance_disk.instance = N'_Total')
GROUP BY CAST(FLOOR(CAST(dbo.performance_disk.collection_datetime AS float(53)) * 24 * 4) / (24 * 4) AS smalldatetime)

OK – i know the query is horrible, but its a once off!

b) Copy and paste the results into excel


c) Click Insert-Pivot Table and make a Pivot table showing the IOPS on the rows and the count of time (twice on the columns). name the first count of time “Freq” and the second “Quartile”


d) Format the second series (we named Quartile) as “% Running Total In” IOPS


e) Click Insert 2D Line Chart, right click on the second series (Quartile)  and select Format Data Series – Plot Series on Secondary Axis and tidy up the chart with nice formatting.



We can now see some interesting data as: While the average IOPS is about 19.5, the 80% quartile is about 25 and the 90% quartile is about 34.

This might help specify SAN requirements as we could now say that our workload requires:

  • 25 IOPS at a latency of 8ms (80% percentile)
  • 34 IOPS at 20ms (90% quartile)
  • max IOPS of 37 (100%)

Ok, these are just small play numbers, but the concept is there. Some wider points to consider:

a) This is assuming that we don’t want to massively increase performance

b) We may need to add more IOPS for future growth and other factors

A Sample excel sheet with results is attached..Love to hear if anyone finds this useful!


Download File - IO_Distribution.zip

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


Currency conversion can be a PITA. As you may know there are three standard requirements that pop up:

  1. One to Many. Where the database is in one currency and it needs to be reported in many currencies.
  2. Many to One. Where different rows are in different currencies and need to be converted into a standard currency.
  3. Many to Many currency. Different rows are in different currencies and need to be reported in many currencies.

This is slightly difficult in PowerPivot due to the lack of “Many to Many” support” and also no support for “default members” to have a default currency.

In this example we have the following tables (example PowerPivot Workbook attached). One initial solution is to use a “calculated member in the PowerPivot Window to convert to a standard currency (EUR) and then apply a one to many relationship for the reporting currency. For a lot of customers that won;t work as this results in a double conversion and therefore errors in the maths.

image Fact Table


ExchangeRate Table

My solution is not elegant. In fact, maybe it will appear in the sqlcat site of “worst practices”, but it works like a dream so far…

The Solution
1. First add a sheet for your FromCurrency and your ToCurrency. Its quiet common that your source currency may be in 10-20 currencies but you only want 2-3 reporting currencies

2. Decide on your “default” currency. in my case EUR

3. do NOT add a relationship between the Facts and the “To Currency”, when a To currency is selected we need to use a calculated measure to do the conversion dynamically as many to many relationships are not supported by default in PowerPivot.

4. Add a slicer for the “To Currency”

5. Add a calculated measure for the Amount.

=if(countrows(ToCurrency)=1,sumx(FactTrans,sumx(filter(ExchangeRates,ExchangeRates[FromCurrency]=FactTrans[Currency]),FactTrans[Amount]/ ExchangeRates[MidRate])),sumx(FactTrans,sumx(filter(filter(ExchangeRates,ExchangeRates[FromCurrency]=FactTrans[Currency]),ExchangeRates[ToCurrency]="EUR"),FactTrans[Amount] / ExchangeRates[MidRate])))


How does it Work?

The trick is the “sumx” function effectively iterates through every row, finding the related conversion rate and applying the conversion and summing the results. The IF statement detects of we have not selected only one currency and if so, applies a default one.

How does it compare to Analysis Services Currency Conversion?

The formula looks  lot more complex than the SSAS MDX equivalent technique, and SSAS has the benefit of also being able to do formatting with “Locales” to change the currency symbols dynamically too. But its quick and easy-ish and it works…


Currency_Conversion_Challenge_2.0.xlsx (274.78 kb)

Page List

Page List