10

I have a server with cheap slow disk and an expensive fast disk.

I want to use the expensive disk for all the things where it's important that it's fast, such as my databases.

To save money, I want to use the slow disk for anything where it doesn't make much difference whether it's fast or slow, such as backups.

Now, my question is, should I install my Microsoft SQL Server on the slow or the fast disk?

(To be clear, I'll place my databases on the fast disk no matter what, so my question only pertains to the location of the installation itself)

Niels Brinch
  • 413
  • 5
  • 16
  • 3
    Why would you even consider this given that a 120gb low write ssd is (a) CHEAP and (b) super fast and (c) good enough for everything OS + programs wise? I moved all operating systems to 120gb ssd 2 years back and the cost did really not matter - at that time. Now it is even less relevant. – TomTom Oct 08 '15 at 11:04
  • You want to trust a mission-critical database to an SSD disk? Remind me never to do business with you... – Shadur Oct 08 '15 at 14:38
  • 2
    @Shadur what a flamebait. Yes, I'll place it on an SSD based RAID 10 configured disk that's replicated to 2 other disks locally and backed up nightly to a remote location. Welcome to the decade! – Niels Brinch Oct 08 '15 at 17:32
  • @TomTom you're right of course, but I am in a rather cost sensitive situation in this case, so that's why I'm even bothering with this type of hyper optimization. My question will become more and more irrelevant as each year passes, as it's the case with most questions in here I suppose. – Niels Brinch Oct 08 '15 at 17:38
  • @NielsBrinch Cent Smart and Pound Foolish. Seriously. – TomTom Oct 08 '15 at 17:39
  • Yes, you made your point and I agree. – Niels Brinch Oct 08 '15 at 17:42

3 Answers3

11

This is kind of opinion-ish, but I would put the SQL Server binaries on the slow disk. It's quite common to put the binaries on the OS disk (although some people hate that), or on a slower disk.

You definitely want to remember to put your system databases, especially tempdb, on the faster disk, however. In fact, it's also common to put tempdb by itself.

This is in line with a couple of articles I found that might be useful to you.

There's also transaction log backups to think of, and I'm torn on this because you want the LDFs on the faster disk and you also want backups on a different disk from where the databases live, but it would be better if they were on a faster disk. You'll need to make a judgment call, but I would probably back up to the slower disk and complain about it. ;)

Community
  • 1
Katherine Villyard
  • 18,510
  • 4
  • 36
  • 59
  • Thankyou. Are you saying it does not affect performance notably whether the sql server installation (binaries etc.) is on the slow or fast disk? – Niels Brinch Oct 07 '15 at 20:07
  • 1
    Not that I've noticed. And it's a fairly common configuration. – Katherine Villyard Oct 07 '15 at 20:08
  • 6
    Katherine is right, because the binaries themselves are not particularly IO bound. In general putting binaries on a fast disk will improve load times but seldom affects general operation speed since the code is running from memory. Unless you are restarting the server frequently it won't hurt significantly to have the binaries on slower storage. – Corey Oct 08 '15 at 00:22
  • @Corey thanks a lot for the very on point explanation. This is what I was looking for. – Niels Brinch Oct 08 '15 at 17:33
6

I'd like to follow up on the pretty good answer Katherine Villyard already put up.

It somewhat depends on the intended usage of your database.
If you're expecting a lot of write operations, go ahead and put your .mdf and .ndf files on the faster disk.

If however your database is either one that is generally quite static (serving web content for example). And the queries don't vary much, chances are you'll get a large amount of the queries in your memory, or even cached on the application side. At which point you are better off using the faster disk for your .ldf, tempdb and backups.

Likewise, if you're expecting a lot of large queries, such as for an OLAP database, you're better off storing your .mdf, tempdb on the faster disk. And putting the .ldf on your slower disks as it won't often be part of the bottleneck.

In any case, don't bother with putting the binaries on the fast disk, we generally put those on a slow (not the system if it can be avoided) disk.
Also, don't get hung up on trying to get both the .ldf and .mdf files on the fast disk, generally they're separated whenever possible.

So in summary, review your load to see what will be your most likely bottleneck.

Community
  • 1
Reaces
  • 5,547
  • 4
  • 36
  • 46
3

You have things backwards. I know it's counter-intuitive, but you want the backups (especially including transaction log backups) on the fast disk, and the mdf/ldf files (with the notable exception of tempdb) on the slow disk.

You can think of it as if Sql Server keeps two representations of your data. The MDF+LDF files represent the current state of the database, while the backup (including transaction log backups since the last full backup) represent what you need to restore the current state of the database in the event of a failure. You want to keep these two representations separated from each other, so an event that destroys one representation will not also damage the other representation.

It turns out the Sql Server performance tends to depend a LOT more on how fast you can write transaction log files and their backups over how fast you can access mdf files. This means you need to strongly consider putting backups on the fast drive (ideally you would add a small SSD to the server that you can use for ldf files, to give them speed while still preserving separation from your backups). Unfortunately this leaves the slow drive for your MDF files, but again: it won't matter as much as you think.

It's worth noting the above assumes that you have sufficient RAM, that you follow typical workloads, and that you plan to use Full recovery mode, rather than simple. Additionally, the operating the system and installed Sql Server program itself can be placed on the slow drive, though of course you probably want as much as you have space for to live on the fast drive.

Joel Coel
  • 12,910
  • 13
  • 61
  • 99
  • By backups I mean files that are not in use, but simply stored. I would put both mdf and ldf files on the fast disk. It's news to me that mdf is OK to put on the slow disk, that was an interesting and unexpected piece of information. – Niels Brinch Oct 07 '15 at 20:02
  • 1
    You don't want the mdf on the same disk as the backups/logs. MDF represents the database current state. Backup + LDF represent what you need to recover the database to it's current state. You want the two representations to be separated from each other, so an event that destroys one will not also damage the other. And since the logs and backups should be on the fast disk (performance depends a **lot** more on how fast you can write to the ldf file than how fast you can write to the mdf file), that means mdf should go to the slow disk. – Joel Coel Oct 07 '15 at 20:16
  • I'm going to edit most of the above comment into the answer. – Joel Coel Oct 07 '15 at 20:17
  • 1
    I'm not sure why you're saying that about the `.ldf` and `.mdf` needing to be separated in case of disaster... It's not generally assumed that you will use either one for disaster recovery, that's what backups are for. If you wan't as near 0 data loss as possible you get extremely frequent log backups, you don't rely on the log file itself. – Reaces Oct 07 '15 at 20:21
  • @Reaces You're right. I had a brain fart and was writing LDF files with my fingers while thinking about TRN backups in my head. The general thoughts hold, but I'll need to revise significantly to clarify that (working on it now). – Joel Coel Oct 07 '15 at 20:25