SQL Server 2008 large table performance

Question

Hey all,

I have this relatively large table in a separate filegroup (2 GB, well, it's not THAT large but large enough I think to start thinking about performance as it's a heavy duty table).

This is the only table in this filegroup.

Right now the filegroup contains only one datafile.

Assuming the table is well-indexed and that index fragmentation is almost zero, would it increase performance (for select and insert statements) if I split the filegroup into two datafiles, BUT having those two datafiles reside on the same physical disk (as I don't have an array of disks at my disposal) ?

Or is a split into multiple files only an improvement when you can split those files over separate physical disks ?

Thanks for any replies.

ps: must add that we're using standard edition so table partitioning is a no-go

Mathieu

Answer 1

You really need to have separate spindles/LUNs if you're going to split index/data

For busting the "one thread per file" myth, read these from Paul Randall.

Answer 2

For the situation you have described, I doubt you could measure the difference accurately, since it would be insignificant. You would need a high end database with specific heavy workloads to entertain the thoughts that you are suffering SGAM / GAM contention. GBN is right in indicating that you need it on seperate spindles to see a suitable difference.