July 8, 2009 at 8:40 am
Guys,
I have 20 million row table which I split into 4 files in a single file group based on Clustered primary key, basically I recreated the primary key on a
file group. The data is now spread across 4 files, while this improved the performance of queries against this table I am trying to understand how does the
SQL process all the record inserts across the files in file group. In addition how is this file group different from partitioning using a partion function in terms of SQL Engine.
Any suggestions or inputs would help.
Thanks
July 8, 2009 at 10:52 pm
A filegroup with multiple files is using a proportional fill algorithm i.e. striping your inserts across the files. Performance benefits come from being able to push/pull data to/from the disks at a higher rate (assuming those files are on different drives).
Partitioning works by allocating each record to (potentially) a different filegroup based on the partition function/scheme. Depending on how the partition storage is set up and the choice of partition key it could give the same benefits as the first approach, or it could be totally different.
The multifile approach is simpler and more predictable as it only impacts storage whereas partitioning benefits can also be derived from differences in the SQL plans. It is possible for partitioning to make performance worse if not thoroughly understood and tested; at the same time partitioning can have other benefits aside from query performance (e.g. fast archiving).
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply