SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Partitioning and the performance on writes.


Partitioning and the performance on writes.

Author
Message
ben.brugman
ben.brugman
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1054 Visits: 2391
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
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41167 Visits: 32666
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
ben.brugman
ben.brugman
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1054 Visits: 2391
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
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41167 Visits: 32666
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
ben.brugman
ben.brugman
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1054 Visits: 2391
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
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41167 Visits: 32666
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
ben.brugman
ben.brugman
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1054 Visits: 2391
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
Grant Fritchey
Grant Fritchey
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41167 Visits: 32666
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

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search