January 5, 2016 at 2:38 pm
We have a single SAN used for everything in the company (about 80 users). Just a small HP P2000 with RAID5. I've got no reason to get a dedicated SAN for SQL at this point.
Since all of the disk operations go through the same controller, cable, disks, etc. is there a reason to keep the OS on C:, Data on D:, Logs on E: and TempDB on T:? It seems the traditional wisdom goes back to the days when disks were installed on the same box as the physical server. Does that still make sense since a controller going out on our little SAN is going to take out all of the drives. I'm pretty sure C:, D:, E: and T: are basically just sections of the same set of hardware.
Any one have convincing arguments either way?
Thanks!
Norman
January 5, 2016 at 4:57 pm
There may be some performance advantages, there are plenty of blogs that talk about it, you may never notice a difference in your environment. There are too many variables to answer that without digging into your system, assuming everything is configured correctly.
Are you having any issues currently? Have you benchmarked the san? Have you monitored any latency or throughput issues with your workload?
January 6, 2016 at 8:40 am
I would say it depends. If you have a very lightly used system I/O wise with not much updates then you are probably OK to have mdf/ldf and TEMPDB on the same drive.
I have a few systems like this here. Like I said though it depends. If you have 50 databases and quite a few are quite large with heavy sorting, heavy delete,updates and selects then you will need to split them out. For small databases with a small group of people with light updating you are OK to have it all together. You do need to know how the systems work though.
January 6, 2016 at 8:53 am
I'll say yes, but not because of performance. Chances are you won't notice, but you might. It depends on how the physical drives are carved up to LUNs in your SAN.
However, having separate drives means
- you'll monitor space better
- You will have things separate if you want to move to new drives or back to local storage later
- you can have standard drives for purposes and ensure that a big log file doesn't impact the ability of the ETL jobs to drop files, or other databases to run.
January 6, 2016 at 1:36 pm
I'm not seeing any problems, at least none of the users are realistically complaining. The monitors say the disk IO is good and latency is under 15ms or so. Occasionally higher but nothing sustained.
What brought up the question is this. This server is running under Hyper-V and there is a push to cluster the VM host machines. When the sys admin checked the configuration with MS, the response was to move all of the files to a single drive letter. The rational was it was simpler and more reliable to only have to mount one drive and keep it connected than several drives.
That sort of made sense, simpler is generally always better. But really flies in the face of everything that I've been told about how to configure SQL Server.
What made me stop and think was how this SAN is constructed. Basically there is only 2 controllers and I have no influence on how the drives are created. The SAN is shared with file storage, RDS desktops, client VM's, server VM's, you name it in a small to medium company. In other words, I cannot specify the data files are on one controller and the log files are on the other. So if the data and logs are on different drive letters but the drive letters all point back to the same controller and disks, how is that going to help protect from a disk failure or make IO faster?
The servers are, in my opinion, lightly loaded in spite of one having 75 databases installed. Some have data updated all the time, others are basically updated nightly and used for reporting, some are for SharePoint, etc. Total disk space is less than a TB, so not really all that big.
I know, the only answer is "It Depends". But looking for some reason to make the change or insist on keeping things the same.
Thanks!
Norman
January 6, 2016 at 4:04 pm
n.heyen (1/6/2016)
So if the data and logs are on different drive letters but the drive letters all point back to the same controller and disks, how is that going to help protect from a disk failure or make IO faster?
It won't.
But it is nice, if say TempDB grows massively and fills its drive to not have the transaction logs and Windows all out of space as well.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 7, 2016 at 11:03 am
Thanks for all the responses. I agree there won't be a noticeable improvement in performance either way. But from a management standpoint, having everything on one disk would be difficult, brutal at times.
We were able to contact another support person at Microsoft that didn't think the first person was right. And said there shouldn't be any issues with having several mount points on the VM. So we are going to retain the current configuration of multiple drive letters.
Best to all of you in 2016,
Norman
January 7, 2016 at 12:09 pm
You can set different policies for different data stores on the same hardware, if your SAN (and/or SAN administrator) is flexible enough. So even though they share hardware with other traffic you can (for instance) create drives for SQL logging with a No Cache policy and SQL data drives with a 64K block size while OS and file share drives have different settings.
This can only be done by creating separate drives and putting them in separate data stores, I'm not aware of any storage system that can deduce the optimum settings on an application-by-application basis on a single drive.
January 7, 2016 at 4:01 pm
Another advantage of separate drives is that you can dedicate the fastest, most expensive solid state storage to data & log files and your backups can go on slower but less expensive spinning disks.
January 8, 2016 at 7:10 am
it depends on how the LUNs and storage back end are setup. The LUNs may use separate cache which can be beneficial but for the most part it won't make any difference
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply