Dong Xie's Blog - All Comments

re: 2.3 GByte/s, can your SAN do that?

Colin Leversuch-Roberts — Fri, 15 Dec 2006 10:33:20 GMT

I don't want to get into silly arguments about this but :-

SQL Server generally read/writes in 8kb or 64kb blocks, thus a 1Mb block is unrealistic.

A pci bus ( 133Mhz 64 bit ) has a maximum burst value of 1064 MB/s, therefore unless you have a different pci bus you could not achieve greater than 1Gb/s for a single card, for SAN's which connect by HBA a single 4gb hba can support a maximum burst rate of 400Mb/s which would mean your server would require 6 4gb HBA's to achive a theoretical 2.3Gb/s.

In reality about 80% of these figures is likely to be achievable.

A storage array might be expected to support these levels of throughput, but not to one server. Technically I would expect a minimum of 8 x 4gb hba's distributed across 4 pci bus(s) for a server to be able to manage a 2.3 gb/s ( sustained ) throughput. Interesting post none the less.

re: 2.3 GByte/s, can your SAN do that?

dong — Thu, 14 Dec 2006 13:19:26 GMT

Dear Colin,

1, The SQL Server Mag article really worth a read.

The design difference:

Rich J v.s. Dong

1.1, LSI SAS3442X HBA - LSI MegaRAID 8480E

point: to offload RAID job. Note: Although Rich is doing mirroring with Windows, we still got the similar throughput, and without his %CPU data, point can't be proved.

1.2, SATA - SAS

Dr. Jim has many papers on this issue. One thing apparently is I'm paying much more for much less capacity, doing almost same scan speed.

2, Clarification:

spindles: about 60

spindle speed: SAS 10K, Read: 85MB/s, Write: 80MB/s

block size: can be derived from pic, 1MB, that makes the 20ms reasonable, because this is large block size access, so the hard drive is doing multiple read to fulfill that.

3, About challenge the SAN:

SAN is nothing more than a storage head + spindles + a fancy software. If put Windows Storage Server / SANMelody on my server, it is a SAN too. The weird moment comes when you compare the price tag: 10 or 100 times difference.

re: 2.3 GByte/s, can your SAN do that?

Colin Leversuch-Roberts — Thu, 07 Dec 2006 17:34:02 GMT

umm .. the whole point of hardware raid is that the processing is handled by the controller, Serial Scssi disks are good cos like fc disks they work full duplex. Your figures need clarification of the number of spindles, spindle speed, raid config, and block size being written/read. I'd actually be unhappy with a 20ms av i/o response time, I usually look for read times under 3 ms and 6 ms for write ( on a san )

re: SSIS Lookup Transformation Dynamic SQL workaround

dong — Mon, 17 Jul 2006 11:30:47 GMT

Hi, Simon,
Thanks for the comments. Yes, I did think of View. But that will be another Workaround, isn't it? Both have limitations and benefits.

E.g. Create and Drop View can't work if you don't have the privilege on the db.

Also I would think Bcp+BULK is a No-touch, clean solution to the db. The View solution will mess up the db if the package is run many times per day.

re: SSIS Lookup Transformation Dynamic SQL workaround

simonsabin — Thu, 13 Jul 2006 20:20:33 GMT

Could you use a view?

Rather than your process task have an execute SQL task that changes the definition of the view. You have need to use 2, one to drop the view if it exists and the other to create the view (you can't have multiple statements in one execute sql task)