October 4, 2026 at 5:58 pm
Hello,
I would like to get some feedback regarding the best storage optimization strategy for a large SQL Server database.
Option 1: Add multiple data files within the same filegroup and distribute them across different storage LUNs/disks, allowing SQL Server to spread allocations using the Proportional Fill algorithm.
Option 2: Create a separate filegroup on a different storage tier/LUN and rebuild the clustered and nonclustered indexes of the largest tables onto this filegroup, effectively separating data and index structures across different storage paths.
In your experience, which approach provides the greatest benefit in terms of I/O performance and scalability, especially in modern SAN environments?
Have you observed measurable performance gains with either approach, and under what workload conditions (OLTP, data warehouse, large databases, etc.)?
Thanks in advance for sharing your experience and recommendations.
October 5, 2026 at 6:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
October 5, 2026 at 6:51 pm
Yes.
I haven't necessarily looked at all indexes, but I have separated out some tables/indexes that get heavier use and can benefit from different storage paths.
The former, using multiple files, has helped as well.
However, it depends on your storage. If you have this on a SAN, then multiple paths mapped on the SAN might or might not get better throughput. This is where you test. It's entirely possible that you get 3 LUNs for your server and they end up being carved from the same 24 disks and 3 v. 1 filegroups/disks isn't better.
It's also possible that it is, as the SAN might parallelize requests for different LUNs and give them different cache's and paths to storage that are faster.
It's really hard to generalize here across hardware.
October 6, 2026 at 9:17 am
I think this is in the "advanced setup" area.
Multiple files: mostly for management as large files can be difficult to move.
Placing files on a different lun: maybe for hot/cold data (or even in a separate database).
We are somewhat accustomed to SAN's which do a lot of tricks on their own so it's hard to monitor changes / impact.
October 10, 2026 at 3:13 am
Option 2: Create a separate filegroup on a different storage tier/LUN and rebuild the clustered and nonclustered indexes of the largest tables onto this filegroup, effectively separating data and index structures across different storage paths.
Clustered Index is the table so you wouldn't separat anything.
October 11, 2026 at 3:06 am
Honestly, I think the biggest payback will be from page compressing the data (whether you use separate filegroups or not).
As always, it's vital to have the best clustered index on the table (hint: most often NOT on an identity value). Use missing_index and index_usage_stats, and critical query plans if applicable, to determine what the clus key column(s) should be. Do not be afraid to use multiple keys if that is best for the table.
Then, rebuild at 90% (decent default if you're not sure what else to use) with DATA_COMPRESSION = PAGE. Likewise for nonclus indexes with significant saving from page compression, or using row compression if that is more appropriate for that index.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply