1

SQL Server best practice recommends separate drives for the the data and log files. Do you really get a speed increase if both logical drives are located on the same EVA?

kubanczyk
  • 13,812
  • 5
  • 41
  • 55
Andy
  • 13
  • 2
  • Maybe, or maybe not. This would depend on a lot of things, such as where the performance bottleneck is and how the EVA is constructed. The more important reason for this BCP is to stop log proliferation from causing out-of-disk for the data, making things go down. – Falcon Momot Aug 13 '13 at 09:03

4 Answers4

4

Ah - just my area of specialisation!

SQL 'best practices' assume that you use separate physical disk for the data and log yes, but that's in either a server with physical disks locally attached or via traditional SAN methods where a LUN was just part of a single SAN disk. EVAs are very different to this.

Depending on which model you have any given LUN will be spread across a MINIMUM of eight physical disks, if it's a P6xxx this can be 12 or more just to start with. So if you have your log spread across one bunch of eight or more disks and you data spread across another eight or more you're obviously going so see a hell of a lot better performance than if this same data were on only two disks. Given this is the lowest performing configuration and you may actually see better performance due with wider spreading I think you'll agree that this 'best practice' goes to hell with EVAs.

Basically just carve them up from the largest Disk Group you can (you really want all of the same type/speed of disks in single DGs) and let the EVA worry about the rest. I run many MSSQL clusters across multiple EVA/P6xxx/3Par boxes and there really is no need these days to get too specific in the LUN makeup as we used to back in the 2000's with older SAN arrays.

Chopper3
  • 101,299
  • 9
  • 108
  • 239
3

Possibly, yes... per-block-device queuing is one distinct area that may benefit from the separation, even when going to the same physical group of disks.

ewwhite
  • 197,159
  • 92
  • 443
  • 809
2

Totally agree with Chopper3. The theoretical disk separation of DB files / logs for performance that you did for good-old JBODs is long gone.

An EVA with a large disk group (plenty of spindles) is all you need.

Besides, if you actually watch what SQL Server or Oracle, for that matter, do at the hardware level, the amount of disk I/O on the DB files can be largely negated by having plenty of RAM. Logs, yes, they still write sequentially; just make sure you have your log rolling thresholds set to match your transaction rate.

We run some moderately sized (300GB) Oracle instances on VMware, running on EVAs. This adds another layer of abstraction; i.e.:

O/S volumes (NTFS) -> VMware VMDK files -> VMware datastore (VMFS file system) -> EVA vDisk (LUN) with it's associated vRAID level -> EVA disk group -> Physical FC/AL drive

So, if you asked a Computer Scientist to locate the disk block containing your database block, in order to apply some optimisation recommendations, he or she would just shrug their shoulders.

Simon Catlin
  • 5,232
  • 3
  • 17
  • 20
1

Generally, on older storage that has specific raids for specific volumes, they recommended keeping the logs and data on different raids. These days, most storage is installed with pools that span all IO across large groups of disks. In a big pool, there's no need to avoid having logs and data for any database on the same disks, but I'd still recommend keeping them on different volumes for the reasons mentioned by ewwhite in his answer (queue contention).

Basil
  • 8,851
  • 3
  • 38
  • 73