February 17, 2015 at 11:33 am
Just wondering how you would setup your SQL server.
Assume you have two physical drives in the server, and let's ignore any RAID setups for simplicity. Also assume, the databases are 80% read (business hours mostly) & 20% write (after hours mostly). Which option would go with:
Option A:
Drive 0 = data files, index files
Drive 1 = log files
Option B:
Drive 0 = data files only
Drive 1 = index files & log files
Personally, I am leaning towards option B, since the databases are heavy on reads...log files won't be getting too much action.
What do you think?
Michal.
February 17, 2015 at 1:05 pm
Ideally you want to be able to recover the db if either drive fails, or is completely destroyed even.
Currently only "A" gives you that.
In theory "B" might work, if every table had a clustered index and Drive0 had all the clustered indexes, with Drive1 only having nonclustered. But in practice I don't think you can realistically do that.
I suggest a hybrid, where you put all types on files -- data, index, log -- on both drives, but insuring that the data&index files for a single, given db were always on a different drive from the log for that db.
That allows you full recoverability, and the ability to balance both read and write I/O between the drives.
Everyone wants the simple "this drive is only this, that drive is only that", but, as a practical matter, that's not best with a limited number of drives.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 17, 2015 at 1:11 pm
Adding to what Scott says, where do backups go?
Personally I would do "A" with backups on the second drive, away from data. So, if I had for a database
Drive 1 : data, indexes( with two drive I wouldn't bother. One mdf)
then
Drive 2: Log and backups.
I could have some databases with 1 and 2 switched, but ideally I wouldn't. I want the head on #2 moving minimally.
February 17, 2015 at 1:34 pm
Backups are being stored on a network drive, so they are not on the same server in case of complete meltdown.
And this server will have a RAID10 setup, so we're all set there too. I just tried to keep my question simple by not involving RAID type, size, layout etc. I was just trying to gauge the concept of storing the index files on a different i/o from the data files, to see if there are performance benefits.
February 17, 2015 at 1:39 pm
Not mentioned is the OS. If I only have two drives, the OS on one and everything else on the other.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 17, 2015 at 1:54 pm
I assumed backups were going to a different drive.
But the backup only gets you to the last backup time. To recover forward to the current time, you need either: (1) all existing data files _or_ (2) full backup, log backups, and the current log file.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 17, 2015 at 2:11 pm
Grant Fritchey (2/17/2015)
Not mentioned is the OS. If I only have two drives, the OS on one and everything else on the other.
Any ref here? I don't worry too much about the OS anymore, with lots of memory, it shouldn't swap much or read from disk if this is a SQL Server. I'm always leaning towards backups and logs w/ the OS.
February 17, 2015 at 2:14 pm
Michal-469362 (2/17/2015)
Backups are being stored on a network drive, so they are not on the same server in case of complete meltdown.And this server will have a RAID10 setup, so we're all set there too. I just tried to keep my question simple by not involving RAID type, size, layout etc. I was just trying to gauge the concept of storing the index files on a different i/o from the data files, to see if there are performance benefits.
Good to hear, then we'll ignore backups.
In terms of perf, you could get some benefit from separating out some data from indexes, but not generically. Often what you think about is separating out two different types of data from each other. So I might keep, as an (extremely gross) example.
Disk 1: customer CI + Customer NCIs
Disk 2: Orders CI + Orders NCI.
This way when I need to hit too large sets of data, I can separate out the work to separate files. However, this hasn't necessarily proven to be really beneficial unless you can see a good pattern here in your tables and queries.
The other issue is that if I need to restore, I can't come up partially (Enterprise Edition only), with the mdf of data and restore the indexes later because the app just doesn't work well and my query plans don't execute.
I tend to not worry about the separation of data from indexes anymore, but others might have other experiences.
February 18, 2015 at 12:19 am
Steve Jones - SSC Editor (2/17/2015)
Grant Fritchey (2/17/2015)
Not mentioned is the OS. If I only have two drives, the OS on one and everything else on the other.Any ref here? I don't worry too much about the OS anymore, with lots of memory, it shouldn't swap much or read from disk if this is a SQL Server. I'm always leaning towards backups and logs w/ the OS.
I don't have a ref for this either but that's the way I do it as well. I've run into problems in the past where the OS drive becomes corrupted or the OS has lost it's mind and the disk needs to be slicked and reinstalled. Keeping "the good stuff" and the OS separate has saved my butt more than once.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 18, 2015 at 5:34 am
Jeff Moden (2/18/2015)
Steve Jones - SSC Editor (2/17/2015)
Grant Fritchey (2/17/2015)
Not mentioned is the OS. If I only have two drives, the OS on one and everything else on the other.Any ref here? I don't worry too much about the OS anymore, with lots of memory, it shouldn't swap much or read from disk if this is a SQL Server. I'm always leaning towards backups and logs w/ the OS.
I don't have a ref for this either but that's the way I do it as well. I've run into problems in the past where the OS drive becomes corrupted or the OS has lost it's mind and the disk needs to be slicked and reinstalled. Keeping "the good stuff" and the OS separate has saved my butt more than once.
I don't have one other than the talk I had with Alan Hirt and some other MVPs a couple of weeks ago when we had a round table discussion at Boston SQL Saturday. The OS came up as one of those things that still needs it's own disk.
Interestingly enough, as a part of the talk, the whole concept of disk separation gets weird because of SANs. So you create two "disks" for the OS, but in reality they're a bunch of shared disks, possibly even sharing with each other, you don't know unless you work with the SAN admin directly. So you separate your logs and your data onto these two disks, but, in reality, things are split at all. Further, separating logs and data is done because of the implication of the types of writes, logs will be sequential, data is random. And then you put 75 log files on the same drive so they get... well, not sequential writes because the heads have to bounce all over the place to write sequentially for 75 different locations.
This is all a lot tougher than it seems at first blush. It's not as easy as the old days of straight physical disks.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 18, 2015 at 7:45 am
And all this even more muddy when you start getting into the realm of VM's and one set of discs might be shared across not only multiple drives on a single server but multiple servers across multiple VM's.
February 18, 2015 at 9:33 am
I hate shared disks. SAN admins usually get upset at me because I want the underlying architecture. Sales reps aren't thrilled either that I complain when they put all the disks in a pool that gets carved up. Can't have that.
I think the OS on it's own drive is fine, though I haven't had those issues. I'll defer to Mr. Hirt here. If he does it, I'd do it.
February 23, 2015 at 4:11 am
Why should your server only have 2 disks? If this is an exam question you have already seen there are many right answers. If this is for a real server, then there is no reason to be limited to 2 disks.
IMHO you should request the number of disk volumes that you need for optimum data placement. They may all relate to the same set of spinning disks in a SAN today, but by specifying different disks for different roles at the OS level you have some chance of improving the placement and performance of where they are located in the SAN. If you restrict yourself to 2 disks at the OS level then you are also restricting any improvements that can be made.
Personally I would as a minimum specify the following 6 volumes: System, Data, Logs, Backup, TempDB, Miscellaneous. If you need more IO than can be provided by a single disk, then increase the number of disks for a given role.
Where I work now the SAN has a SSD layer and will automatically move data into this where allowed and where performance will be improved. Currently the plan is to make the Data, Logs and Tempdb volumes SSD enabled, but keep the others to spinning rust only. We are also looking at making the tempdb volume a RAM disk, but this is not yet finalised. Having the data split over multiple volumes enables this type of improvement to be done.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply