Update on PCIE-SSD (Fusion-IO) Performance

I’ve been working on this and wanted to achieve max throughput before posting, but since I am stuck, thought I would post what I have to this point. I have 4 Fusion-IO cards including a duo in a 12 core (2.66 MHz/12 MB Cache, E5650 processors) with 96 GB of RAM. Here are the numbers I got on SQL Server backup after striping database file-groups over three different drives and the log mirrored on 2 of the drives. What is interesting is that the CPUs show as 100% for the whole process, so even though theoretically this configuration should allow over 2 GBps throughput (i.e. 3 x 750 MBps per IODrive), the processors get maxed out.

Also, it doesn’t seem to matter whether SQL Server is cold or hot, SQL Backup seems to always go back to the physical database files to read even if most of the database is in cache. I am using database compression, maybe in this case it is hurting more than helping?

Doing the testing using backup to Null device to avoid bottleneck on output media too slow, changing to writing out to a RAID-0 set of SSDs though gets about the same performance. Interestingly, performance was a little better with adding multiple NUL device references (to force parallelism to multiple devices). Its hard to tell which backup parameters are really making that much impact, since the bottleneck are the CPUs at this point.

Below is the results of a 120 GB database on the cards.

BACKUP DATABASE [tp_v5_dev] TO 
DISK = 'NUL:', DISK='NUL:',DISK='NUL:',DISK='NUL:',DISK='NUL:',DISK='NUL:'
,DISK = 'NUL:', DISK='NUL:',DISK='NUL:',DISK='NUL:',DISK='NUL:',DISK='NUL:'
--disk='h:\sqlbackup\folibackuptest1.bak',
--disk='h:\sqlbackup\folibackuptest2.bak',
--disk='h:\sqlbackup\folibackuptest3.bak',
--disk='h:\sqlbackup\folibackuptest4.bak',
--disk='h:\sqlbackup\folibackuptest5.bak',
--disk='h:\sqlbackup\folibackuptest6.bak'

WITH NOFORMAT, NOINIT,  NAME = N'tp_v5-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
-- Magic:
,BUFFERCOUNT = 256
,BLOCKSIZE = 65536
--,MAXTRANSFERSIZE= 4097152 – Doesn’t seem to matter too much
GO

10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
Processed 22472 pages for database 'tp_v5_dev', file 'TradingOptimizer_v2' on file 1.
Processed 44800 pages for database 'tp_v5_dev', file 'History09' on file 1.
Processed 16 pages for database 'tp_v5_dev', file 'HistoryA09a' on file 1.
Processed 15560 pages for database 'tp_v5_dev', file 'TP_History01' on file 1.
Processed 14520 pages for database 'tp_v5_dev', file 'History02' on file 1.
Processed 20096 pages for database 'tp_v5_dev', file 'History03' on file 1.
Processed 11464 pages for database 'tp_v5_dev', file 'History04' on file 1.
Processed 13528 pages for database 'tp_v5_dev', file 'History05' on file 1.
Processed 16296 pages for database 'tp_v5_dev', file 'History06' on file 1.
Processed 20656 pages for database 'tp_v5_dev', file 'History07' on file 1.
Processed 30056 pages for database 'tp_v5_dev', file 'History08' on file 1.
Processed 846248 pages for database 'tp_v5_dev', file 'HistoryData2a' on file 1.
Processed 134872 pages for database 'tp_v5_dev', file 'LoadData02' on file 1.
Processed 197656 pages for database 'tp_v5_dev', file 'LoadData2' on file 1.
Processed 347176 pages for database 'tp_v5_dev', file 'LoadData01' on file 1.
Processed 96448 pages for database 'tp_v5_dev', file 'LoadData03' on file 1.
Processed 2939584 pages for database 'tp_v5_dev', file 'MiscData2' on file 1.
Processed 3666864 pages for database 'tp_v5_dev', file 'MiscData3' on file 1.
Processed 28944 pages for database 'tp_v5_dev', file 'History10' on file 1.
Processed 32 pages for database 'tp_v5_dev', file 'Load00' on file 1.
Processed 8176 pages for database 'tp_v5_dev', file 'Load01' on file 1.
Processed 30760 pages for database 'tp_v5_dev', file 'History09b' on file 1.
Processed 16 pages for database 'tp_v5_dev', file 'HistoryA09b' on file 1.
Processed 33448 pages for database 'tp_v5_dev', file 'History09c' on file 1.
Processed 16 pages for database 'tp_v5_dev', file 'HistoryA09c' on file 1.
Processed 36496 pages for database 'tp_v5_dev', file 'History09d' on file 1.
Processed 16 pages for database 'tp_v5_dev', file 'HistoryA09d' on file 1.
Processed 34760 pages for database 'tp_v5_dev', file 'History10a' on file 1.
Processed 16 pages for database 'tp_v5_dev', file 'HistoryA10a' on file 1.
Processed 38576 pages for database 'tp_v5_dev', file 'History10b' on file 1.
Processed 16 pages for database 'tp_v5_dev', file 'HistoryA10b' on file 1.
Processed 51344 pages for database 'tp_v5_dev', file 'History10c' on file 1.
Processed 16 pages for database 'tp_v5_dev', file 'HistoryA10c' on file 1.
Processed 176656 pages for database 'tp_v5_dev', file 'History10d' on file 1.
Processed 16 pages for database 'tp_v5_dev', file 'HistoryA10d' on file 1.
Processed 136 pages for database 'tp_v5_dev', file 'Archive01' on file 1.
Processed 7968 pages for database 'tp_v5_dev', file 'Load02' on file 1.
Processed 9720 pages for database 'tp_v5_dev', file 'Load03' on file 1.
Processed 9168 pages for database 'tp_v5_dev', file 'Load04' on file 1.
Processed 9360 pages for database 'tp_v5_dev', file 'Load05' on file 1.
Processed 10200 pages for database 'tp_v5_dev', file 'Load06' on file 1.
Processed 9872 pages for database 'tp_v5_dev', file 'Load07' on file 1.
Processed 10552 pages for database 'tp_v5_dev', file 'Load08' on file 1.
Processed 12680 pages for database 'tp_v5_dev', file 'Load09' on file 1.
Processed 13344 pages for database 'tp_v5_dev', file 'Load10' on file 1.
Processed 11984 pages for database 'tp_v5_dev', file 'Load11' on file 1.
Processed 13360 pages for database 'tp_v5_dev', file 'Load12' on file 1.
Processed 11552 pages for database 'tp_v5_dev', file 'Load13' on file 1.
Processed 9912 pages for database 'tp_v5_dev', file 'Load14' on file 1.
Processed 12168 pages for database 'tp_v5_dev', file 'Load15' on file 1.
Processed 11648 pages for database 'tp_v5_dev', file 'Load16' on file 1.
Processed 12456 pages for database 'tp_v5_dev', file 'Load17' on file 1.
Processed 11616 pages for database 'tp_v5_dev', file 'Load18' on file 1.
Processed 26272 pages for database 'tp_v5_dev', file 'Load19' on file 1.
Processed 25376 pages for database 'tp_v5_dev', file 'Load20' on file 1.
Processed 20296 pages for database 'tp_v5_dev', file 'Load21' on file 1.
Processed 21864 pages for database 'tp_v5_dev', file 'Load22' on file 1.
Processed 20480 pages for database 'tp_v5_dev', file 'Load23' on file 1.
Processed 24032 pages for database 'tp_v5_dev', file 'Load24' on file 1.
Processed 15960 pages for database 'tp_v5_dev', file 'SimIndex01' on file 1.
Processed 15784 pages for database 'tp_v5_dev', file 'SimIndex02' on file 1.
Processed 15024 pages for database 'tp_v5_dev', file 'SimIndex04' on file 1.
Processed 14952 pages for database 'tp_v5_dev', file 'SimIndex05' on file 1.
Processed 16 pages for database 'tp_v5_dev', file 'Sim42' on file 1.
Processed 131696 pages for database 'tp_v5_dev', file 'History11a' on file 1.
Processed 130872 pages for database 'tp_v5_dev', file 'Intraday01' on file 1.
Processed 42360 pages for database 'tp_v5_dev', file 'Intraday' on file 1.
Processed 109464 pages for database 'tp_v5_dev', file 'History11b' on file 1.
Processed 96360 pages for database 'tp_v5_dev', file 'History11c' on file 1.
Processed 72 pages for database 'tp_v5_dev', file 'History11d' on file 1.
Processed 64 pages for database 'tp_v5_dev', file 'History12a' on file 1.
Processed 64 pages for database 'tp_v5_dev', file 'History12b' on file 1.
Processed 64 pages for database 'tp_v5_dev', file 'History12c' on file 1.
Processed 64 pages for database 'tp_v5_dev', file 'History12d' on file 1.
Processed 7912 pages for database 'tp_v5_dev', file 'loadfg25' on file 1.
Processed 7936 pages for database 'tp_v5_dev', file 'loadfg25a' on file 1.
Processed 7928 pages for database 'tp_v5_dev', file 'loadfg25b' on file 1.
Processed 9616 pages for database 'tp_v5_dev', file 'loadfg26' on file 1.
Processed 9624 pages for database 'tp_v5_dev', file 'loadfg26a' on file 1.
Processed 9616 pages for database 'tp_v5_dev', file 'loadfg26b' on file 1.
Processed 11680 pages for database 'tp_v5_dev', file 'loadfg27' on file 1.
Processed 11648 pages for database 'tp_v5_dev', file 'loadfg27a' on file 1.
Processed 11656 pages for database 'tp_v5_dev', file 'loadfg27b' on file 1.
Processed 9768 pages for database 'tp_v5_dev', file 'loadfg28' on file 1.
Processed 9760 pages for database 'tp_v5_dev', file 'loadfg28a' on file 1.
Processed 9800 pages for database 'tp_v5_dev', file 'loadfg28b' on file 1.
Processed 9744 pages for database 'tp_v5_dev', file 'loadfg29' on file 1.
Processed 9760 pages for database 'tp_v5_dev', file 'loadfg29a' on file 1.
Processed 9784 pages for database 'tp_v5_dev', file 'loadfg29b' on file 1.
Processed 10592 pages for database 'tp_v5_dev', file 'loadfg30' on file 1.
Processed 10624 pages for database 'tp_v5_dev', file 'loadfg30a' on file 1.
Processed 10616 pages for database 'tp_v5_dev', file 'loadfg30b' on file 1.
Processed 9368 pages for database 'tp_v5_dev', file 'loadfg31' on file 1.
Processed 9376 pages for database 'tp_v5_dev', file 'loadfg31a' on file 1.
Processed 9384 pages for database 'tp_v5_dev', file 'loadfg31b' on file 1.
Processed 11768 pages for database 'tp_v5_dev', file 'loadfg32' on file 1.
Processed 11760 pages for database 'tp_v5_dev', file 'loadfg32a' on file 1.
Processed 11760 pages for database 'tp_v5_dev', file 'loadfg32b' on file 1.
Processed 9904 pages for database 'tp_v5_dev', file 'loadfg33' on file 1.
Processed 9912 pages for database 'tp_v5_dev', file 'loadfg33a' on file 1.
Processed 9912 pages for database 'tp_v5_dev', file 'loadfg33b' on file 1.
Processed 152 pages for database 'tp_v5_dev', file 'loadfg34' on file 1.
Processed 152 pages for database 'tp_v5_dev', file 'loadfg34a' on file 1.
Processed 152 pages for database 'tp_v5_dev', file 'loadfg34b' on file 1.
Processed 152 pages for database 'tp_v5_dev', file 'loadfg35' on file 1.
Processed 152 pages for database 'tp_v5_dev', file 'loadfg35a' on file 1.
Processed 152 pages for database 'tp_v5_dev', file 'loadfg35b' on file 1.
Processed 152 pages for database 'tp_v5_dev', file 'loadfg36' on file 1.
Processed 152 pages for database 'tp_v5_dev', file 'loadfg36a' on file 1.
Processed 152 pages for database 'tp_v5_dev', file 'loadfg36b' on file 1.
100 percent processed.
Processed 0 pages for database 'tp_v5_dev', file 'TradingOptimizer_v2_log' on file 1.
Processed 2 pages for database 'tp_v5_dev', file 'TradingOptimizer_log3' on file 1.
Processed 0 pages for database 'tp_v5_dev', file 'TradingOptimizerlog4' on file 1.
BACKUP DATABASE successfully processed 10049026 pages in 57.071 seconds (1375.620 MB/sec).