1

Let's say we have a SQL Server 2000 box that houses our main database engine.

We added an iSCSI SAN and now that server has a one card connected to the regular network and one card connected to the iSCSI network.

The Data is requested through another server which is our application server and it's not on the iSCSI network.

We enabled Jumbo Packets to 9000 on the iSCSI connection on the data server (as well as the other items on the iSCSI network.

After reading an article by Jonathan Kehayias I'm wondering if what we did is right.

What's the best way to test this on my OLTP system? The OS is Windows Server 2003 R2 Enterprise x64 SP2 and SQL Server is Enterprise 2000 x86.

4 Answers 4

1

Probably the easiest way to test it is to use SQLIO. I've got a tutorial on it here:

http://sqlserverpedia.com/wiki/SAN_Performance_Tuning_with_SQLIO

You can test it before & after to see whether the jumbo frames helped or not.

3
  • isn't that the sort of thing that has to be tested off hours and be prepared that your SAN could crash. We've discussed this before, but I'm just concerned about the implications of SQLIO since I've never run it. Also, doesn't that bypass the application layer as well? Oct 19, 2009 at 20:44
  • SQLIO doesn't go through SQL at all. As Brent says in the link it "mimics SQL Server's disk activity patterns". Yes it would be best to run this in non-production time and go conservative with the settings until you work out how to use it.
    – Sim
    Oct 20, 2009 at 0:22
  • For the iSCSI connection from the Database Server to the SAN Jumbo Frames are enabled and I believe that is the recommendation. I need to run SQLIO against those drives on the SQL Server to simulate SQL Server activity. The application server is not on the iSCSI network at all. It only has one NIC and it's at the default lower rate. If an application makes a request to the database (which is pulling data accross the iSCSI connection), how do I tell if there is a packet loss or performance issues? Does the application server handle splitting the frames? Oct 20, 2009 at 12:40
1

The advice in the article you link to is very good and it explains the reasons why Jumbo frames aren't necessarily a good idea in general purpose LAN environments but he doesn't really discuss the nature of iSCSI network traffic itself and that does usually benefit from Jumbo frames as the disk IO traffic will be in relatively large blocks - 8kb if you haven't modified it. Some SQL experts might want to correct me on this but I think all database IO will be be in 8kb chunks. If the read\write block size is 8k then a single IO can fit in a single Jumbo frame (the protocol overhead is relatively low - < 100 bytes generally) rather than having to be split across six standard sized ones.

You probably won't see any significant throughput change (maybe a few %) but what I would expect to see is a significantly lower CPU load and interrupt rate from the network interface driver since your NIC's will generally be handling only 1/6th the number of packets to carry the same amount of data. This may not be a huge deal for you but if you have multiple NIC's carrying iSCSI traffic it can add up to a significant chunk of CPU resources or a busy server. If you have smart NIC's with iSCSI\TCP offload the benefits will obviously be lower but overall the increased frame size still makes it easier for everything on the iSCSI network fabric so it would still be recommended.

That said - I'd echo Brent Ozar's recommendation that you carry out some performance tests if at all possible.

0

I would be surprised if was anything other than a help. Granted, I'm a Unix SA, not a DBA/Networking/Windows guy, but I expect it to do nothing but good (assuming it is supported end to end of course).

I would expect that the DB is going to be reading and writing at least a page at a time, generally a DB page is the same size as an OS page (most RISC systems had 4K for 32 bit, and 8K for 64 bit, but I suspect that it is still a 4K for 64bit AMD64 systems). Sure, it theory, you could read and write single disk blocks (512 bytes), but I bet they don't. So with 4K vs 1.5K (normal ethernet), you can fulfill most requests with 1/3 the number of packets, which ought to be a win, possibly even a noticeable one.

0

The article is completely irrelevant to your concern. It talks about application traffic that your SQL Server sees. On the other hand, you have a question about storage connectivity - traffic that your server's iSCSI initiator sees.

So, first, forget the article.

Second, I am yet to see a best practices or white paper or manual from a storage vendor that would NOT recommend "enable jumbo frames".

Application doesn't matter. Platform doesn't matter. iSCSI likes jumbo frames, period.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .