File placement on server with 2 instances of SQL

I have inherited a stand alone server with 2 instances of SQL installed.
There are 6 physical disks in the server which have been made into 3 mirrors.
The first mirror has the OS on it. Currently, the 2nd disk has all the
database and transaction log files from both instances of SQL.
I plan to make use of the 3rd disk. My question is: is it better to move the
database and transaction log files from the second instance to the new disk
so that all the files for the first instance are on disk 2 and all the files
for the 2nd instance are on disk 3 OR is it better to keep all the database
files from both instances on disk 2 and move all the log files for both
instances to disk 3?
I'm sure I have read somehwere that in this situation, the disks should be
separated by instance rather than seperating by file type.Seen from the databases' perspective, same or different instances doesn't really make a difference.
As usual, only testing with a realistic workload can give you a definitive answer. My guess, though,
is to have the tlog files separated.
Unless you have some pretty unusual workload. Like only reads during day and modifications during
night. And it doesn't matter that modifications take a bit longer. Then you might consider having db
1 (all db files) on one raid and db 2 (all files) on another. That would mean that the read
operations during day would not compete between db1 and db2.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Stu" <Stu@.discussions.microsoft.com> wrote in message
news:3C5F2B99-EC15-4CBC-9BB7-54E14582227F@.microsoft.com...
>I have inherited a stand alone server with 2 instances of SQL installed.
> There are 6 physical disks in the server which have been made into 3 mirrors.
> The first mirror has the OS on it. Currently, the 2nd disk has all the
> database and transaction log files from both instances of SQL.
> I plan to make use of the 3rd disk. My question is: is it better to move the
> database and transaction log files from the second instance to the new disk
> so that all the files for the first instance are on disk 2 and all the files
> for the 2nd instance are on disk 3 OR is it better to keep all the database
> files from both instances on disk 2 and move all the log files for both
> instances to disk 3?
> I'm sure I have read somehwere that in this situation, the disks should be
> separated by instance rather than seperating by file type.
>|||+1 to both of Tibor's sentiments.
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e7sqUlyXIHA.5348@.TK2MSFTNGP03.phx.gbl...
> Seen from the databases' perspective, same or different instances doesn't
> really make a difference. As usual, only testing with a realistic workload
> can give you a definitive answer. My guess, though, is to have the tlog
> files separated.
> Unless you have some pretty unusual workload. Like only reads during day
> and modifications during night. And it doesn't matter that modifications
> take a bit longer. Then you might consider having db 1 (all db files) on
> one raid and db 2 (all files) on another. That would mean that the read
> operations during day would not compete between db1 and db2.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Stu" <Stu@.discussions.microsoft.com> wrote in message
> news:3C5F2B99-EC15-4CBC-9BB7-54E14582227F@.microsoft.com...
>>I have inherited a stand alone server with 2 instances of SQL installed.
>> There are 6 physical disks in the server which have been made into 3
>> mirrors.
>> The first mirror has the OS on it. Currently, the 2nd disk has all the
>> database and transaction log files from both instances of SQL.
>> I plan to make use of the 3rd disk. My question is: is it better to move
>> the
>> database and transaction log files from the second instance to the new
>> disk
>> so that all the files for the first instance are on disk 2 and all the
>> files
>> for the 2nd instance are on disk 3 OR is it better to keep all the
>> database
>> files from both instances on disk 2 and move all the log files for both
>> instances to disk 3?
>> I'm sure I have read somehwere that in this situation, the disks should
>> be
>> separated by instance rather than seperating by file type.
>|||I also second Tibor's response. Especially the part about the only
difinitive answer will come from testing.
And don't forget there is a third option. That's to put the data from DB1
and the log from DB2 on Disk2 and the log from DB1 and the data from DB2 on
Disk3. That might help in a situation where you have primarily reads on
both databases during the day and updates during the night and almost all
the updates are done to one database.
Tom
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e7sqUlyXIHA.5348@.TK2MSFTNGP03.phx.gbl...
> Seen from the databases' perspective, same or different instances doesn't
> really make a difference. As usual, only testing with a realistic workload
> can give you a definitive answer. My guess, though, is to have the tlog
> files separated.
> Unless you have some pretty unusual workload. Like only reads during day
> and modifications during night. And it doesn't matter that modifications
> take a bit longer. Then you might consider having db 1 (all db files) on
> one raid and db 2 (all files) on another. That would mean that the read
> operations during day would not compete between db1 and db2.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Stu" <Stu@.discussions.microsoft.com> wrote in message
> news:3C5F2B99-EC15-4CBC-9BB7-54E14582227F@.microsoft.com...
>>I have inherited a stand alone server with 2 instances of SQL installed.
>> There are 6 physical disks in the server which have been made into 3
>> mirrors.
>> The first mirror has the OS on it. Currently, the 2nd disk has all the
>> database and transaction log files from both instances of SQL.
>> I plan to make use of the 3rd disk. My question is: is it better to move
>> the
>> database and transaction log files from the second instance to the new
>> disk
>> so that all the files for the first instance are on disk 2 and all the
>> files
>> for the 2nd instance are on disk 3 OR is it better to keep all the
>> database
>> files from both instances on disk 2 and move all the log files for both
>> instances to disk 3?
>> I'm sure I have read somehwere that in this situation, the disks should
>> be
>> separated by instance rather than seperating by file type.
>

File placement for optimal performance?

What is the best performance for this configuration:

Files:

Data

Log

Indexes

tempdb

Disk:

A - RAID 10

B - RAID 10 (or should this be RAID 1?)

Whats best?:

A - Data and Indexes

B - tempdb and Log

? Thanks.

check these links

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx

http://sql-server-performance.com/Community/forums/t/2337.aspx

Madhu

File Placement

Can anyone point me to any Microsoft articals giving reccomendations for file placement for SQL server? We are trying to convince our Hardware guys that we need separate disks for data/log/tempdb files and need some ammo.

Thanks,

Jason

This article may be a good starting point:-

http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx

HTH!

|||

This article reccomends RAID 1, but lots of others say RAID 5?

What's your opinions on what is better?

|||

It really does depend on your data (and what you're willing to spend!!).

For read/write intensive operations (eg tempdb) you want to go with RAID1 as you get the benefit of reading from 2 disks without a heavy penalty of writing. For mainly read intensive you should go for RAID5.

Check out the different RAID levels explained in Books Online.

HTH!