Q

Separate physical hard drives for each database on one machine?

I have seen the performance benefits yielded by creating the SQL Server Transaction Log file on a physically separate disk or RAID device. Because the log file is written serially, using a dedicated disk allows the disk heads to stay in place for the next write operation. This is all fine and great for a SQL Server machine that is dedicated to one database.

What should I do if I have a machine with six or eight databases all on one machine (say in a Web application hosting

type of scenario). Do I need to have a separate physical hard drive for each database on the machine?

VIEW MEMBER FEEDBACK TO THIS ASK THE EXPERT Q&A.

In practice with the server cost in mind, my recommendation would be to group the databases on a RAID array and the transaction logs on a separate RAID array. Properly configured hardware is a core component for SQL Server performance and based on the criticality; stay tuned for additional tips relative to hardware configurations.


MEMBER FEEDBACK TO THIS ASK THE EXPERT Q&A:

I agree with the premise that in the real world you are not going to have enough physical drives or RAID adapters to provided separate physical arrays for each database. But Jeremy doesn't justify his advice here. I'd like to understand his logic. Why this approach and not just lump everything into one large array? Or, if one of the databases is doing significantly more insert/update/deletes than the others, maybe you'd see better performance by isolating the transaction log of that one database on its own array, and lumping everything else together on the other array.
—Mike G.

******************************************

Thank you for the questions. As far as configuring the hardware is concerned, from a high performance perspective, it is important to split the different types of SQL Server processing (random, sequential, etc.) onto different drives. This enables the various types of I/O to be split across as many drives as possible. With a single RAID 5 set the I/O will all be contending for the same resources and cause disk queuing.

Regarding isolating the transaction log of one database on its own array, and lumping everything else together on the other array -- that could definitely be the case based on unique business needs. From a processing perspective, it is still important to split the database from the transaction logs.

Thank you again for the questions and I hope this clarifies the additional items.
— Jeremy Kadlec, Performance and tuning expert


Do you have comments on this Ask the Expert Q&A? Let us know.
This was first published in June 2005

Dig deeper on Microsoft SQL Server Performance Monitoring and Tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close