August 8, 2018 at 2:45 am
These days, is there actually any point ?
Using VM's and SANS, is there any real point in separating data, logs and tempdb out onto separate drive letters
Been pondering this for a while with regards to SQL storage, we separate out the data, log and tempdb files diligently but to me, it seems a bit pointless as you end up creating a drive for each and pegging it with a disk space size, 60 Gb for data, 30 for logs, 10 for tempdb, so you have 100 gb in total, but not sure nowadays, on SANs whether there is a benefit, used to be for performance and splitting the data and logs onto different spindles, we dont even always have spindles nowadays, It just gets put on the NetAPP and it sorts it all out with its clever algorithms and stuff, storage tiers and all that.
My thinking is you have your nominal 100 GB sized server and its split out into separate "pots", if the log drive fills, things grind to a halt, despite the tempdb drive having 9.3 GB spare and the Data drive having 40 GB spare, so your server potentially stops due to the logfile being full, despite having almost half the disk free. You monitor the disk space anyway.
I am not saying just stick everything on one drive now, you are doing it wrong, far from it, I just have to make some decisions and want my thinking to be spot on and not miss anything.
August 8, 2018 at 5:02 am
I think it can be helpful for triaging issues because it tells you immediately where to start looking if there are problems. So if you have a monitoring dashboard and a particular disk is alerting, you could look at it and go Ok that's the drive that always houses my transaction logs, then immediately go and look at log_reuse_wait_desc, whereas if you can see it's where your log backups always go, you might want to jump on and look at the SQL Agent jobs to check they're deleting old backups as expected (or whatever). If they're all on the same drive it might take longer to figure out what's suddenly blowing up.
August 8, 2018 at 5:53 am
Cheers, my thinking is I am going to have to log on anyway and see what is up, so would just look at the SQL Logs anyway, or just looking on the disk at where the space is used, the potential benefit being that if your logs grow and there is still 50GB free, then it wont cause a problem anyway, unless of course its not doing a T log backup and continues to grow until it blows the Single disk but that is then down to your monitoring I guess.
August 8, 2018 at 6:07 am
mark 52160 - Wednesday, August 8, 2018 2:45 AMThese days, is there actually any point ?Using VM's and SANS, is there any real point in separating data, logs and tempdb out onto separate drive letters
Been pondering this for a while with regards to SQL storage, we separate out the data, log and tempdb files diligently but to me, it seems a bit pointless as you end up creating a drive for each and pegging it with a disk space size, 60 Gb for data, 30 for logs, 10 for tempdb, so you have 100 gb in total, but not sure nowadays, on SANs whether there is a benefit, used to be for performance and splitting the data and logs onto different spindles, we dont even always have spindles nowadays, It just gets put on the NetAPP and it sorts it all out with its clever algorithms and stuff, storage tiers and all that.
My thinking is you have your nominal 100 GB sized server and its split out into separate "pots", if the log drive fills, things grind to a halt, despite the tempdb drive having 9.3 GB spare and the Data drive having 40 GB spare, so your server potentially stops due to the logfile being full, despite having almost half the disk free. You monitor the disk space anyway.
I am not saying just stick everything on one drive now, you are doing it wrong, far from it, I just have to make some decisions and want my thinking to be spot on and not miss anything.
If you are using a SAN and put the files on different drives there is a good chance that the same physical drives on the SAN are being used to store data on both logical drives. I've personally found very little difference in performance when using a SAN if you put files on the same logical drive or put them on different separate dives. The performance is usually very good. Though our server management team prefer the log, transaction and data files to be put on on different separate logical drives.
August 8, 2018 at 6:22 am
I still split the drives, mostly as a logical separation. As Beatrix pointed out, it can make troubleshooting a bit easier. Another possible reason for doing it though, is if you're in a virtualized environment (such as I am,) there may be limits imposed on the maximum size of a disk (not due to limitations on the SAN, but either configuration choices with the virtual server or limitations of the same.) So it becomes easier to say "I need a 75GB disk, a 200GB disk, a 100GB disk, and a 50GB disk in addition to the normal OS C:\ drive," and get it, versus "I need a C:\ drive of whatever size you normally use, and a 2.5TB drive." At my work, the latter would first result in them looking at you to see if you're joking, then telling you "no, we can't do that."
August 8, 2018 at 6:32 am
I've had this discussion with a lot of people, MCMs, MVPs, consultants and gurus. The general consensus is pretty straight forward. If you're not experiencing I/O waits and bottlenecks, leave everything alone. If you are experiencing I/O waits and bottlenecks, you're going to need to determine why and where in order to address them. The way to address them? The old fashioned solutions of separating storage, multiple drives, multiple controllers. Just because something is old fashioned doesn't mean it's wrong. If you know as you're building a system that is going to be very big, you may want to start working on the details of I/O up front, but that's a pretty rare occurrence. Most of us, most of the time, the new disks are going to work great. Some of us, some of the time, depending on the situation, they won't.
"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
August 8, 2018 at 6:40 am
jasona.work - Wednesday, August 8, 2018 6:22 AMI still split the drives, mostly as a logical separation. As Beatrix pointed out, it can make troubleshooting a bit easier. Another possible reason for doing it though, is if you're in a virtualized environment (such as I am,) there may be limits imposed on the maximum size of a disk (not due to limitations on the SAN, but either configuration choices with the virtual server or limitations of the same.) So it becomes easier to say "I need a 75GB disk, a 200GB disk, a 100GB disk, and a 50GB disk in addition to the normal OS C:\ drive," and get it, versus "I need a C:\ drive of whatever size you normally use, and a 2.5TB drive." At my work, the latter would first result in them looking at you to see if you're joking, then telling you "no, we can't do that."
Yes, very good point.
August 8, 2018 at 7:16 am
Grant Fritchey - Wednesday, August 8, 2018 6:32 AMI've had this discussion with a lot of people, MCMs, MVPs, consultants and gurus. The general consensus is pretty straight forward. If you're not experiencing I/O waits and bottlenecks, leave everything alone. If you are experiencing I/O waits and bottlenecks, you're going to need to determine why and where in order to address them. The way to address them? The old fashioned solutions of separating storage, multiple drives, multiple controllers. Just because something is old fashioned doesn't mean it's wrong. If you know as you're building a system that is going to be very big, you may want to start working on the details of I/O up front, but that's a pretty rare occurrence. Most of us, most of the time, the new disks are going to work great. Some of us, some of the time, depending on the situation, they won't.
Cheers, so basically build it simple, and if you see performance issues, then think about splitting it out but dont necessarily do it as a matter of course, unless you know up front its a hefty sort of workload ?
August 8, 2018 at 7:49 am
mark 52160 - Wednesday, August 8, 2018 7:16 AMCheers, so basically build it simple, and if you see performance issues, then think about splitting it out but dont necessarily do it as a matter of course, unless you know up front its a hefty sort of workload ?
That's the approach I'd take, yeah. I'm consulting with this organization right now (light, on the side). They're hitting huge I/O issues. They have everything, and I mean everything, on the C:\ drive. We're working on getting them, first, logically split (they're on virtual, on a san, we're not looking at splitting up san storage... yet) so at least the os is in one place, tempdb is in another, data and logs in another (although I'd prefer two more). But, all this because we know they have I/O problems.
"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
August 9, 2018 at 5:45 am
I have started wondering the same thing honestly especially with the newest tech of SANs. By having 3 diff drives, one for mdf, one for ldf and one for TEMPDB you then limit yourself for free space spikes.
August 9, 2018 at 7:52 am
If you have the ability to define a profile for the logical drives in a SAN, one thing you could do is put log files on a drive that has caching turned off. The log files are read when starting the server or when doing a transaction rollback or log backup, but those are very infrequent operations compared to the normal writing. So filling the SAN cache with pages of log files is a waste.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply