March 3, 2009 at 6:38 am
Ola!
I try all Features from SQL Server and created a huge Table with 20 Million rows.
Now i want compare the performance between a "normal" Database, a Database with Filegroups and a Database with Partition and Filegroups. Therefor I use a normal Workstation with a Athlon 3 GH , 3 GB RAM and 2 IDE Drives.
I create Filegroups, i used Partitions but all result are nearly equal. In all cases the performance ist very good ( 50 ms).
Now to my Questions:
1) Are Filegroups and Partition only good to increase performance if i use a DB-Server (with Quad-Core and SCSI e.g)?
3) In which scenario Filegroups are good and why (in this scenario)?
2) In which scenario Partitions are good and why (in this scenario)?
Many Tnx for your awnsers,
teLLy
March 3, 2009 at 7:36 am
Using partitioned taboles and multiple file groups is all about allowing multiple operations in parallel without a hardware conflict.
For example, if you have one user querying data for the current year and another user querying data for last year, the best scenario you could have is to have different read heads on the drives gathering the data for each of the users. This would enable completely independant data reads. It would also mean that the read heads between operations would tend to hang out in the appropriate place on the drive (assuming your indexes are not fragmented) so they do not have to go far for the next operation.
Knowing when you need to partition like this is easy in theory, but can be complex in execution. It makes sense when you have lots of conflicting disk operations that can be separated. If everything you run is always going to hit the same disk array, the best use of money is to get the fastest array you can get rather than cutting it up across multiple slower arrays. You also want to make sure that the drives are the bottleneck. As you indicated, if you have a single-processor system, having 10 disk arrays may not help so much because reading the data is fast but it all gets held up at the processor.
I would recommend you pick up a SQL hardware performance tuning book. It is a complex subject and you will not get a complete answer in a forum post unless you are a bit more specific in what you want to know.
March 3, 2009 at 8:06 am
Ola!
Tnx for your detailed answer. Now i undenstand why neither Filegorups nor Partitions speed up my DB.
If i get another Question i will post here again! 🙂
GrretzU
teLLy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply