Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Partitioning and the performance on writes. Expand / Collapse
Author
Message
Posted Thursday, January 10, 2013 8:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, August 2, 2014 1:47 AM
Points: 246, Visits: 1,168
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
Post #1405503
Posted Friday, January 11, 2013 5:44 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:43 PM
Points: 15,663, Visits: 28,061
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1405941
Posted Friday, January 11, 2013 7:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, August 2, 2014 1:47 AM
Points: 246, Visits: 1,168
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
Post #1405991
Posted Friday, January 11, 2013 8:17 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:43 PM
Points: 15,663, Visits: 28,061
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1406062
Posted Friday, January 11, 2013 8:58 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, August 2, 2014 1:47 AM
Points: 246, Visits: 1,168
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


Post #1406109
Posted Friday, January 11, 2013 12:27 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:43 PM
Points: 15,663, Visits: 28,061
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1406228
Posted Monday, January 14, 2013 7:51 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, August 2, 2014 1:47 AM
Points: 246, Visits: 1,168
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
Post #1406740
Posted Monday, January 14, 2013 8:34 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 12:43 PM
Points: 15,663, Visits: 28,061
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 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1406766
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse