2

SAN LUN ambiguity wrt performance

MSDN says to split read/write operations into a drive, sequential writes to another drive. (PRIMARY filegroup use typically data with read, update, write activity while LOG filegroup is sequential write mainly) Documentation predates Storage Area Networks.

I get that grouping storage to read/write activity would optimise the heads on disk - minimise disk thrashing, sort of thing... but how is this dealt with when a "Disk" on the OS is an allocation to a storage pool derived from many disks?

The "bottleneck" of IO is then the IO controller on the SAN, and it controls the data flow from the data on striped groups of disks? Meaning, LUN1 exposed to Server1 OS as 2 500GB drives, each split into 2 logical drives D:, E:; F:,G: of 250GB each: A database having a 750GB table created on a a filegroup of 4 files (D:\f1.mdf, E:\f2.ndf, F:\f3.ndf, G:\f4.ndf) will have parrallelism across all the logical drives (OS file driver optimised?) and SQL can use more file handler threads? but all the IO still comes through the LUN controller maxing at 1GB/sec.

Or is the DISK seperation then implied to be LUN level splits? Meaning LUN1 250GB, LUN2 250GB, LUN3 250, LUN4 250... each exposed to the OS as a seperate physical drive, mapped to a logical drive each, and the SAME FILEGROUP split would ONLY then realise the benefit of IO optimised throughput? I guess my question is then dependent on whether the SAN storage controller can sustain a 1GB/sec throughput on each LUN.

Or does it mean a storage controller for each LUN?

Can someone more experienced advise whether the gotcha of aligning the striping to the NTFS blocksize is participant in this discussion? OS NTFS allocation size blocks... oh! and the network packet size - is this also an influencing factor in terms of data throughput to the client? IPv6 vs IPv4 - is this a factor also?

  • 1
    The questions only tangentially have to do with databases. It is probably better suited for the Server Fault stack: Server Fault is a question and answer site for managing information technology systems in a business environment. It's built and run by you as part of the Stack Exchange network of Q&A sites. With your help, we're working together to build a library of detailed answers to every question about server, networking, or related infrastructure administration. – hot2use May 25 '18 at 5:44
  • @hot2use - is there a Migrate Question function? I agree that it is applicable to Server Fault, but contextually the database performance and SQL Service itself's internal workings are what I want to ensure are catered to - which a storage specialist can't advise. – Alocyte May 30 '18 at 12:04
0

Talk with your SAN administrator. That's the source that should be able to explain you with detailed information what kind of SAN you've got and what it is capable of. Have you got MPIO? What's the structure of your fabric?

Sometimes it makes sense to use multiple LUNs for different files. Behind the scenes, there might be different types of storage available.

A SAN with some SSD capacity might offer tiering. That is, the hottest data is elevated to SSD -backed part of the system whilst colder data is persisted on HDDs. Some models provide autotiering, in which the system decides what's hot and what's not. Some models require administrative action.

HP EVAs provide VRAID levels 0, 1 and 5. Using those will spread a LUN on all the disks in an array. This increases reliability, but the cost with VRAID 5 is increased controller CPU usage. The SAN admin needs to balance increased storage costs against increased CPU usage.

Aligning NTFS blocks with storage is dependent upon the SAN. If you are in addition using virtualization (Hyper-V, VMWare, whatever), that's additional complexity introducec. Your storage vendor is likely to offer white papers, best practices and consulting services about how to configure the storage for optimal performance.

Network packet size is relevant - if you use iSCSI. For FibreChannel, IP settings are not relevant.

  • The question I am posing is how SQL Server performance is optimally catered to given MSDN nomenclature "disk" is now "logical disk" which may be a disk partitioned by OS or multiple disks presented as a single logical disk... Filegroup files split across logical disks for performance considerations' efficacy if one LUN, each seperate LUN - best practice recommendation; case study... :) So vague and I don't have a local SAN to do evaluations personally - hoping experienced DBA has insight into this. – Alocyte May 30 '18 at 12:00
  • Thanks for your response. I accept it is as the answer. Hope you got the bounty. – Alocyte Jun 4 '18 at 12:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.