sql4gnt (1/13/2014)
I have a question to add to this...would separating a database into multiple files on the same storage group have much of an impact? (Not considering SAN caching capabilities like EMCs fast cache)
Are the files part of the same database filegroup (PRIMARY for instance)?
Once you start adding multiple files to a filegroup (a technique used for tempdb to overcome allocation contention) SQL server has to start performing extra ops against those files to track the free space and increase the file sizes. SQL Server applies a proportional fill method and the more files you add the more SQL server has to file switch. This will negate any performance you gained (if any) and cause the opposite reaction, sloooooowwwwwwwwww!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉