Solved

SQL Server Disk Configuration Question

Posted on 2013-11-18
3
190 Views
Last Modified: 2013-11-19
SQL Server 2012 running on Server 2012.

Multiple instances on the same SQL Server.

I have enough hard drive configuration to give each instance EITHER:
a) it's own log file hard drive
b) it's own data file hard drive

I cannot give each instance its own log file hard drive AND its own data file hard drive.

So which would be optimal for performance?  Individual data file hard drives (with all log files on a single shared drive), or individual log file hard drives (with all data files on a single shared drive)?

Thanks.
0
Comment
Question by:gateguard
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 25

Assisted Solution

by:Mohammed Khawaja
Mohammed Khawaja earned 200 total points
ID: 39658127
My recommendation would be to distribute data files and log files equally across all disks and I am assuming all drives are of similar speed.  As an example I would configure instance 1 data files to be on D: and logs on E: and it would the opposite for instance 2.
0
 
LVL 1

Accepted Solution

by:
BradySQL earned 300 total points
ID: 39658360
If you have to choose I would put the logs on their own volume for a few reasons.

1. Data files can easily be separated later by creating additional datafiles and moving high IO items such as a specific table or Indexes to the secondary datafile.

2. Logs have a lot of writes that happen where the data volume doesn't necessarily. This can be different depending on your situation.

Other thoughts, you may want to consider the impact of sharing some volumes with your logs and separating tempDB to its own volume. This has a lot to do with transactions per second and where you see your bottlenecks happening.

There is not golden answer here, it might also involve you setting up one scenario and watching to see what your IO throughput looks like. And making some changes as you go.
0
 

Author Closing Comment

by:gateguard
ID: 39659331
Thanks!
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question