The Microsoft Assessment and Planning Toolkit is a fantastic tool for helping gather resource usage for a SQL consolidation / virtualisation project.
http://technet.microsoft.com/en-us/library/bb977556.aspx
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
