SQL Server 2008 large table performance
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 repl开发者_运维问答ies.
ps: must add that we're using standard edition so table partitioning is a no-go
Mathieu
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.
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.
精彩评论