August 19, 2013 at 3:39 pm
Ugh, every time I think I have my head wrapped around SAN technology; I realize I don't.
SQL Server 2008 R2. Database in question is 3.9 TB.
Here's what I know about my storage setup for my data files: I have 6 - 1 TB mount points. They are NetApp SCSI LUNs spread across 160 back-end spindles.
I've read (in the NetApp documentation) that the Microsoft recommendation on multiprocessor servers is to have at least one LUN per logical processor.
1) Does LUN equate to Mount Point? Or can I have several mount points but it really only be one LUN?
2) In the case of a SAN; is there really any benefit to spreading data amongst multiple files in a single file group (placing each file on a different mount point)? Clearly if your SQL Server is using local hard disks the answer is yes; but I can't find anything conclusive on LUN setup. One one hand the data is already being spread across a bunch of drives so it seems like it wouldn't matter. On the other hand does having two specifically different files possibly help with performance.
August 20, 2013 at 3:17 am
BobMcC (8/19/2013)
I've read (in the NetApp documentation) that the Microsoft recommendation on multiprocessor servers is to have at least one LUN per logical processor.
Hmmmm, no, that's only really valid for Tempdb and only really valid for SQL Server 2000 too.
If you must use multiple files for your tempdb its recommended to use 1\4 - 1\2 files per CPU and should never exceed the number of CPUs unless exhaustive testing has proved it's required.
BobMcC (8/19/2013)
1) Does LUN equate to Mount Point?
Yes a mounted volume will essentially be a separate LUN exposed to the server
BobMcC (8/19/2013)
Or can I have several mount points but it really only be one LUN?
The root drive will have separate folders for mounting the volumes but the volumes essentially will be separate LUNs exposed to the server.
BobMcC (8/19/2013)
2) In the case of a SAN; is there really any benefit to spreading data amongst multiple files in a single file group (placing each file on a different mount point)? Clearly if your SQL Server is using local hard disks the answer is yes; but I can't find anything conclusive on LUN setup. One one hand the data is already being spread across a bunch of drives so it seems like it wouldn't matter. On the other hand does having two specifically different files possibly help with performance.
If your LUNs for Logs and Data are carved from the same spindles then it's really a futile exercise, about the only benefit of separate volumes here is separation and management of the files at the OS level (i.e. on different logical drives in Windows).
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 20, 2013 at 9:06 am
Thanks Perry.
So in the case of my six luns; I guess the best thing to do is create one FileGroup with a data files on each LUN (of the same size), allowing for distribution of data evenly across the available space.
August 20, 2013 at 11:06 am
BobMcC (8/20/2013)
Thanks Perry.So in the case of my six luns; I guess the best thing to do is create one FileGroup with a data files on each LUN (of the same size), allowing for distribution of data evenly across the available space.
Here it is, do you really need multiple I\O queues hitting your disks?
When configuring multiple files per filegroup sql server will attempt to proportionately fill the files. It has to swap between files and track free space etc.
Its probably more suitable to create multiple filegroups and separate objects out.
as I said the multiple files per filegroup scenario is more useful to fix a specific problem found in tempdb allocation contention.
keep it simple.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
August 20, 2013 at 12:56 pm
So it looks like you're saying I'm still making things too complex.
Rather than have one file group with files on six LUNS; perhaps have multiple file groups;
Perhaps a file group for each LUN with a sub segment of my data in each one.
i.e. FG1(on LUN1) -> F1.ndf -> TableA, TableB, TableC
FG2(on LUN2) -> F2.ndf -> TableD, TableE, TableF
etc. etc.
August 20, 2013 at 10:59 pm
BobMcC (8/20/2013)
So it looks like you're saying I'm still making things too complex.Rather than have one file group with files on six LUNS; perhaps have multiple file groups;
Perhaps a file group for each LUN with a sub segment of my data in each one.
i.e. FG1(on LUN1) -> F1.ndf -> TableA, TableB, TableC
FG2(on LUN2) -> F2.ndf -> TableD, TableE, TableF
etc. etc.
Only create filegroups if you need them, would your databases benefit from multiple filegroups?
1 or 2 files per filegroup is plenty unless you truly have the underlying disk structure to support more. Restores become a complete headache when you start creating multiple files per filegroup. I've seen databases with 50+ files in the primary filegroup because a new files was added when space ran low instead of extending the existing files. Restoring this db is a nightmare especially when you have to move file on all those datafiles!
As i said keep it simple, think also of the guys who will be supporting what you are implementing.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply