Following up on my previous testing of table partitioning, I wanted to see if I could identify any notable overhead when using partitioned tables other then in selects, meaning delete, update and of course inserts. At the same time I thought I could test out with a partitioned table but where the files was located on the same physical drive.
So I created a new database, 2 partition functions and schemas and 3 new tables. TestOne was partitioned as my last time, and so was TestTwo. TestThree table was partitioned exactly like TestOne except it utilized file groups located on the same physical drive, whereas TestOne had its filegroups split across two physical drives.
I found out that selects provided no noticeable performance difference between TestOne and TestThree tables. I would think the main area where one would see performance differences here is dependent on hardware and load on the drives, and less so with the SQL Server performance. So further testing into this will be outside the scope of at least this blog entry.
So – onwards to testing other SQL then selects. The first I tested was ordinary inserts for each of my 3 tables.
INSERT INTO <TABLE>
VALUES (1, 'test')
GO
When running this it provided me with this execution plan:
(click to enlarge)
Not surprisingly there is an overhead visible when it comes to inserting into a partitioned table compared to a non-partitioned one. Plus the plans do not look the same. I would simply attribute the overhead to the fact that SQL Server needs to look up which file group to locate the data in based on the partition function.
The difference in this instance does however look relative small. The entire subtree cost for inserting into a partitioned table (TestOne and TestThree) were 0.010471 whereas for TestTwo it was 0.01.
So while the specific numbers might differ from system to system etc, it does show an overhead, albeit a small one.
When it came to both delete and updates, I found that the same issues as with select statements were in effect.
When I only needed to delete or update on a specific "grouping" of data, the partitioned tables were actually faster than the non-partitioned one. When I had to update across "groups" then the non-partitioned one was fastest.
For example:
UPDATE TestOne
SET MyValue='Test2'
WHERE MyID = 1
GO
was faster then
UPDATE TestTwo
SET MyValue='Test2'
WHERE MyID = 1
GO
by a factor around 20%, whereas
UPDATE TestOne
SET MyValue='Test2'
WHERE MyID = 1 OR MyID = 2
was about 10% slower then
UPDATE TestTwo
SET MyValue='Test2'
WHERE MyID = 1 OR MyID = 2
This leads back to my conclusion in the last piece that if you in fact can group your data in a manner which makes sense and avoids “cross-grouping data”, then it is faster performance wise to do so. The only time it looks to be noticeable slower to have partitioned data is with inserts.
This of course is not an ever valid conclusion, and I’m sure more concrete and scientifically gathered data could present cases against. But if your data abides to some rules which makes a grouping possible and the main focus on the data is reading versus inserting; then there looks to be mainly advantages when it comes to performance with table partitioning. And especially this can run transparent for normal users and database developers it is definitely something worth a thought.