Sql-server – What throughput should I expect with MPIO

sql server

Dell PowerEdge 2950 with two 1 Gbps NIC's going to two 1 Gbps ports on a switch which then goes to a NetApp with four 1 Gbps NIC's that present as one virtual interface. 24 drives, 7200k SATA, NetApp RAID-DP. I've mapped each host NIC to the NetApp using MPIO in the Microsoft iSCSI initiator. Testing with SQLIO my write throughput appears reasonable at about 200 MBs, but my reads are closer to 100 MBs.

Shouldn't my reads be closer to 200 MBs just like my writes? Is this a configuration problem or is there a fundamental storage issue I don't understand?

enter image description here

Update:
Here are IOPS for the random workload. The reads make sense, though, I'm not sure what to make of 20000 for the writes. SAN cache is 3.2 GB. SQLIO tests are against a 25 GB file.

enter image description here

Best Answer

Disk writes are actually going to memory (NVRAM) on the filer, to be flushed to disk later - on an idle filer, these will be incredibly fast, and iops of 20,000 are quite believable (you'll see similar speeds from most SSDs).

Reads, on the other hand, need to come from disk unless they already happen to be in the filer's read cache (which, unlike writes, are on volatile memory).

It's hard to pin storage vendors down on iops for spinning disks, but for a 7200RPM drive, 80-120 iops is quite believable. Considering that you've probably lost a couple of disks to NetApp's RAID-DP and/or spares, 2,200 iops is close to what you could expect from 22 disks performing around 100 iops each.

This may not explain your read speeds (your disks may not be doing the full 2200 iops when you're performing a sequential read), but it may at least help explain your write performance.

Related Solutions

Sql-server – What can cause a mirroring session to timeout then failover

It sounds like you might be running out of TCP ports on the SQL Server. How many connections are you seeing to the server at a time?

Timeouts like that would definitely be causing the problem.

SQL Server Queries – Should ORDER BY Clause Be Included for Specific Order?

I had a simpler repro in mind:

CREATE TABLE #x(z CHAR(1));
CREATE TABLE #y(z CHAR(1));

INSERT #x SELECT 'O';
INSERT #x SELECT 'R';
INSERT #x SELECT 'D';

INSERT #y SELECT 'E';
INSERT #y SELECT 'R';

SELECT z FROM #x
UNION ALL 
SELECT z FROM #y;

Results:

O
R
D
E
R

Now add an index:

CREATE CLUSTERED INDEX z ON #x(z);

SELECT z FROM #x
UNION ALL 
SELECT z FROM #y;

Results:

D -|
O -|- ordered based on the clustered index, not how originally inserted
R -|
E
R

This still produces the first three rows from #x and the last two rows from #y, so still doesn't prove that SQL Server might return those entire queries in a different order than physically laid out in the query (just that you can't rely on the ordering inside those sets). But clearly this mythical "observe an order once, and it will always be true" statement needs to be nipped.

Bottom line: do not rely on observed order. If you want a certain ordering, say so using ORDER BY.

Also, please read this post by Conor Cunningham, a pretty smart guy on the SQL team.

Best Answer

Related Solutions

Sql-server – What can cause a mirroring session to timeout then failover

SQL Server Queries – Should ORDER BY Clause Be Included for Specific Order?

Bottom line: do not rely on observed order. If you want a certain ordering, say so using ORDER BY.

Related Question