Zero Milli Seconds Disk Latentcy

Lately I done a performance lab where finding the limits of an existing SQL Server 2012 based application. The Test Lab was done with Team Foundation Server Test Suite on latest Hardware. SQL Server ran on HP DL 580 with 48Cores in 240GB RAM and Disk Subsystem was FusionIO 4TB Card.

The application itself was very Disk Intensive - storing excessively transactions in large tables and in addition large blobs in the database.

With all Load Agents running we came up to 50.000 SQL Batch Requests/Sec (monitored with Windows Perfmon) and during all the tests we never encountered a Disk latency greater than 0ms (Zero Milli-Seconds) whereas Processor Usage went up to 90-95% Processor Time.

These Results are very uncommon since multicore Systems have arrived and change the way we have to do performance tuning of Database Applications. Where usually we start by tuning queries that take about 200ms now we can start by tuning 50ms queries. And it becomes much more important to even tune queries that take about 5ms but occur very often.

The results change the way I think about performance tuning again. Where SSD Storage still has latency and is bottlenecked by Controllers performance (usually 780MB/s per channel) FusionIO seems to have no real limit that can be reached with this application. In addition the way the application makes use of SQL Server Features becomes much more important to tune even fast queries (<5ms) becomes very important.

The next step to optimize applications is to go with SQL 2014 and use In Memory Optimized Tables.

https://msdn.microsoft.com/en-us/library/dn133186(v=sql.120).aspx