can I put more than one database per disk?

Question

can I put more than one database per disk?

dubem1-878067

Ten Centuries

Points: 1044
More actions
November 14, 2017 at 5:47 am

#326262

Actually I have one database
disk C : OS
disk D: tempDB
disk E : MDF fie
disk F : Ldf file
disk G : Backup disk
now I have to create two new databases
should I put them on new disks or use the disks above and put all the MDF files on disk E and all the LDF files on disk F?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

You can use the same disks. If the databases are heavily used or performance is crucial, you might want to keep them separate, though.

John

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

dubem1-878067 - Tuesday, November 14, 2017 5:47 AM
Actually I have one database
disk C : OS
disk D: tempDB
disk E : MDF fie
disk F : Ldf file
disk G : Backup disk
now I have to create two new databases
should I put them on new disks or use the disks above and put all the MDF files on disk E and all the LDF files on disk F?

Further to John's comment, I'd use the same storage structure for the new databases as for the existing one, to keep things consistent, unless you are forced to do otherwise for performance reasons.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 3

Phil Parkin - Tuesday, November 14, 2017 6:23 AM
dubem1-878067 - Tuesday, November 14, 2017 5:47 AM
Actually I have one database
disk C : OS
disk D: tempDB
disk E : MDF fie
disk F : Ldf file
disk G : Backup disk
now I have to create two new databases
should I put them on new disks or use the disks above and put all the MDF files on disk E and all the LDF files on disk F?
Further to John's comment, I'd use the same storage structure for the new databases as for the existing one, to keep things consistent, unless you are forced to do otherwise for performance reasons.

Also remember that, on a SAN, what you're calling "separate disks", probably aren't different PHYSICAL disks. There is no performance advantage unless you can guarantee that the disks are actual physically separated disks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Blake McNeill SSC Rookie Points: 31 More actions · Answer 4

If we have large databases or one that will be heavily used, then we try to put the data files on different disks. Otherwise, same disk.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 5

Blake McNeill - Tuesday, November 14, 2017 7:10 AM
If we have large databases or one that will be heavily used, then we try to put the data files on different disks. Otherwise, same disk.

Again, not likely to happen on a SAN even though you have different Drive Letters. The same "spindles" are frequently used across multiple Drive Letters. Check with your SAN administrator and find out.

As a bit of a sidebar, large or heavily used database benefit much more from writing good code rather than any hardware tricks. Good code can typically improve performance by 60 to 100X whereas the sum of all hardware tricks might get you a 2X gain and only if you're very, very lucky. Even if you go the route of MPP (Massively Parallel Processing), which also requires some code rewrites to support it, the MPP appliance vendor will usually only claim "UP TO 30X" and won't guarantee it.

Although hardware and hardware tricks do help, it's almost trivial compared to what you do with code. Bad code will still run poorly even on good hardware, Performance lives in the CODE. Spend more time and money there than on hardware tricks that don't end buying you much. If you feel compelled to update your hardware, buy as much memory as the system will hold and the fastest disks you can lay your hands on for the transaction log files. Just remember that will only give you some performance relief and generally won't help as much as writing correct code for properly designed databases.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Blake McNeill SSC Rookie Points: 31 More actions · Answer 6

Jeff Moden - Tuesday, November 14, 2017 8:29 AM
Blake McNeill - Tuesday, November 14, 2017 7:10 AM
If we have large databases or one that will be heavily used, then we try to put the data files on different disks. Otherwise, same disk.
Again, not likely to happen on a SAN even though you have different Drive Letters. The same "spindles" are frequently used across multiple Drive Letters. Check with your SAN administrator and find out.
As a bit of a sidebar, large or heavily used database benefit much more from writing good code rather than any hardware tricks. Good code can typically improve performance by 60 to 100X whereas the sum of all hardware tricks might get you a 2X gain and only if you're very, very lucky.

We try to work with our SAN admins to make sure that we do indeed have a separate disk although we do get push back from time to time. And I completely agree with you about good code. Life would be much easier if all code was good code, but that would be an unrealistic expectation.

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 7

Blake McNeill - Tuesday, November 14, 2017 8:41 AM
Jeff Moden - Tuesday, November 14, 2017 8:29 AM
Blake McNeill - Tuesday, November 14, 2017 7:10 AM
If we have large databases or one that will be heavily used, then we try to put the data files on different disks. Otherwise, same disk.
Again, not likely to happen on a SAN even though you have different Drive Letters. The same "spindles" are frequently used across multiple Drive Letters. Check with your SAN administrator and find out.
As a bit of a sidebar, large or heavily used database benefit much more from writing good code rather than any hardware tricks. Good code can typically improve performance by 60 to 100X whereas the sum of all hardware tricks might get you a 2X gain and only if you're very, very lucky.
We try to work with our SAN admins to make sure that we do indeed have a separate disk although we do get push back from time to time. And I completely agree with you about good code. Life would be much easier if all code was good code, but that would be an unrealistic expectation.

That's a bit fatalistic. 😉 It's not unreasonable at all to expect good code and, if you do it properly and with your "I'm a friendly DBA" hat on, you can instill some pride in the Developers by demonstrating some fixes without the implication that they're not good programmers or other inflammatory position. I actually sit with the Developers in my company and, after working WITH them in such a fashion, they now recognize and repair performance issues in legacy code, write some damned fine high performance new code, and aren't afraid to ask when there's an issue that they are having difficulty with.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)