January 10, 2013 at 8:39 am
Hello All,
I get some results I am not expecting.
The situation is something like :
Full saturation test.
Start with clearing the cache.
11 tables are involved.
All data from a single week of februar last year is used.
Variables:
Number of concurrent processes. (29)
Clustered tables or Heap tables. (11 tables all heap or all clustered)
(Clustering is done on clientnumbers)
No partitioining Or
Partitioning, 30 partitions on the clientnumbers modulo 30
No partitioning.
Heap performance is around 1500 - 2500 rows a second.
Cluster performance is around 2500 - 3500 rows a seconds.
(Cluster has less indexes)
Partitioning.
Heap performance is around 250-320 rows a second.
So because of the huge difference I decided to use the same partitioning scheme, but do the inserts all on a single partition.
This gave only a very slight improvement.
Partitioning on clientnumber (30 partitions).
Using 30 of the 30 partitions gave 289 rows / second
Using 05 of the 30 partitions gave 204 rows / second
Using 01 of the 30 partitions gave 316 rows / second
What I was expecting that the partitioning and using only one partition would generate similar results to using no partitions.
I expected (and hoped) that partioning would be slower than non partitioning, because the writes are done over more locations. But the difference is much larger than expected.
And I would expect that this difference would be smaller is only one partition would be used. This does not make a lot of difference.
So my questions are:
Why is partioning this slow?
Why is writing to a single partition so much slower than the non partitioned situation?
Thanks, any thoughts are welcome.
Offcourse I do understand that there are a load of aspects I have not described and that a lot depends on those aspects. All tests are done on a Single instance of SQL-server, I have used 3 databases which have the same dataset and the same set of data is written.
Thanks for your time and attention,
Ben Brugman
January 11, 2013 at 5:44 am
Are the partitions on a single disk? If so, you've just added the overhead of having to filter your data but you're not benefitting from having multiple disks to offset that cost. On a guess, I'd say that's it. Always remember, partitioning may, may, increase performance but it's primarily a data management tool so it frequently doesn't help performance at all. But that doesn't matter if you're able to manage data sets in a way you never could before.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 11, 2013 at 7:04 am
Grant Fritchey (1/11/2013)
Are the partitions on a single disk? If so, you've just added the overhead of having to filter your data but you're not benefitting from having multiple disks to offset that cost. On a guess, I'd say that's it. Always remember, partitioning may, may, increase performance but it's primarily a data management tool so it frequently doesn't help performance at all. But that doesn't matter if you're able to manage data sets in a way you never could before.
(Disk is a San (42 disks))
Most of my wondering came from;
Using a non partitioned database, should give about the same results as using ONE single partition in a partitioned database.
(Number of diskaccesses is very similar, actions are similar etc., cpu should be similar.)
The difference in Throughput though is a factor of about 10.
So there is a flaw in my reasoning, or there is a flaw in my setup.
Any help would be welcome.
At the moment I am looking in causes for these differences, but haven't found any explanation yet. (The databases are not totally the same, but the dataset I am writing is the same). Timings do reproduce very well so my conclusion is that I do not get interference from others. (On the SAN which is not completely my own and on the instance).
Ben
January 11, 2013 at 8:17 am
Yeah, but is the SAN set to multiple, isolated LUNs for the partitioning? If not, partitioning is going to suffer more than just a straight access to the same set of disks.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 11, 2013 at 8:58 am
Grant Fritchey (1/11/2013)
Yeah, but is the SAN set to multiple, isolated LUNs for the partitioning? If not, partitioning is going to suffer more than just a straight access to the same set of disks.
As far as I know the San and the partitioning are 'independend', so no specific drives are designated to specific partitions.
Ben
January 11, 2013 at 12:27 pm
Right, so as far as SQL Server is concerned you're partitioning to a single drive. Performance is pretty much guaranteed to be poor. Yours does seem abnormally poor, but poor would be my assumption.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 14, 2013 at 7:51 am
Grant Fritchey (1/11/2013)
Right, so as far as SQL Server is concerned you're partitioning to a single drive. Performance is pretty much guaranteed to be poor. Yours does seem abnormally poor, but poor would be my assumption.
The results I got where gathered during a benchtest.
We are going to make decisions based on these results.
But if we cannot explain the results, is will be difficult to use these results in arguments.
If 'Performance is pretty much guaranteed to be poor.', I would like to have a source (or evidence) for that.
As the results are (10 times) slower they will probably be rejected because they are redicules. (Or the testing is not done properly or something like that).
Ben
January 14, 2013 at 8:34 am
ben.brugman (1/14/2013)
Grant Fritchey (1/11/2013)
Right, so as far as SQL Server is concerned you're partitioning to a single drive. Performance is pretty much guaranteed to be poor. Yours does seem abnormally poor, but poor would be my assumption.The results I got where gathered during a benchtest.
We are going to make decisions based on these results.
But if we cannot explain the results, is will be difficult to use these results in arguments.
If 'Performance is pretty much guaranteed to be poor.', I would like to have a source (or evidence) for that.
As the results are (10 times) slower they will probably be rejected because they are redicules. (Or the testing is not done properly or something like that).
Ben
Sorry, online forum and all, I didn't mean that performance was guaranteed to be poor with partitioning. I meant, if you only have a single LUN where you are not separating out and isolating your I/O in order to use the full strength of partitioning, then performance will be poor. It just makes sense. One LUN with a straight I/O statement or one LUN that must determine partition location then do the I/O, the second option is slower because of the processing. The performance wins, when there are any, from partitioning, all come at the I/O part of the process. Your setup didn't allow for that to occur.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply