2

I have 3 instances of SQL Server 2008, each on different machines with multiple databases on each instance. I have 2 separate LUNS on my SAN for MDF and LDF files. The NDX and TempDB files run on the local drive on each machine. Is it O.K. for the 3 instances to share a same volume for the data files and another volume for the log files?

I don't have thin provisioning on the SAN so I would like to not constaint disk space creating multiple volumes because I was adviced that I should create a volume (drive letter) for each instance, if not for each database. I am aware that I should split my logs and data files at least. No instance would share the actual database files, just the space on drive.

Any help is appretiated.

hagensoft
  • 1,497
  • 13
  • 13

1 Answers1

2

Of course the answer is: "It depends". I can try to give you some hints on what it depends however.

A SQL Server Instance "assumes" that it has exclusive access to its resources. So it will fill all available RAM per default, it will use all CPUs and it will try to saturate the I/O channels to get maximum performance. That's the reason for the general advice to keep your instances from concurrently accessing the same disks.

Another thing is that SQL Server "knows" that sequential I/O access gives you much higher trhoughput than random I/O, so there are a lot of mechanisms at work (like logfile organization, read-ahead, lazy writer and others) to avoid random I/O as much as possible.

Now, if three instances of SQL Server do sequential I/O requests on a single volume at the same time, then from the perspective of the volume you are getting random I/O requests again, which hurts your performance.

That being said, it is only a problem if your I/O subsystem is a significant bottleneck. If your logfile volume is fast enough that the intermingled sequential writes from the instances don't create a problem, then go ahead. If you have enough RAM on the instances that data reads can be satisfied from the buffer cache most of the time, you don't need much read performance on your I/O subsystem.

What you should avoid in each case is multiple growth steps on either log or data files. If several files on one filesystem are growing, you will get fragmentation and fragmentation can transform a sequential read or write request even from a single source to random I/O again.

The whole picture changes again if you use SSDs as disks. These have totally different requirements and behaviour, but since you didn't say anything about SSD I will assume that you use a "conventional" disk-based array or RAID configuration.

Short summary: You might get away with it, if the circumstances are right, but it is hard to assess without knowing a lot more about your systems, from both the SAN and SQL perspective.

TToni
  • 9,145
  • 1
  • 28
  • 42
  • +1 Thanks TToni, The Logs are on a 2 15K disk array on a RAID5, while the Data are on a 4 15K disk array on a RAID10. The SQL servers have maximum memory on board (48GB). These DB's on the SAN are mostly read-only, the heavy I/O DB traffic goes thru some separate Fusion-IO disks which are faster. My main concern is that creating a volume on each array for each SQL Server instance would still be using the same LUN/spindle and may not notice any improvement on performance. I don't have much space on the SAN, If I only made 1 volume for all It would be more likely to not run out of space. – hagensoft Sep 29 '12 at 20:16
  • How do you do a RAID-5 on two disks? You sure it's not a RAID-1 (which would be the sensible choice for log-volumes anyway, logs are write-heavy)? If that's what you have and you don't have the option of adding more spindles, then go ahead and hope for the best. As you said it doesn't really matter if you have three files on one volume or one each on three volumes if the underlying disks are the same in each case. Just make sure to avoid small size increments in the DB-files, set the NTFS cluster size to 64KB and the RAID-stripesize to 128 KB. – TToni Sep 30 '12 at 14:19