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

Table patitioning / partition switching Expand / Collapse
Author
Message
Posted Monday, October 28, 2013 10:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 11:29 AM
Points: 28, Visits: 491
Hi,

I'm fairly certain that I know the answer to this question, but would like some validation nonetheless. A large table, with roughly 7 billion rows and 360GB of storage, receives daily updates via a MERGE statement. Some rows are updated, some are inserted, but none are currently deleted. In my mind, such a table is NOT a candidate for partition switching. Do you concur?

Thanks,
Bennett

Post #1509025
Posted Monday, October 28, 2013 3:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 19, 2014 11:54 PM
Points: 24, Visits: 384
If you can't guarantee that the data to be merged is focused at the most recent data, you have to consider the following example:
Data2010
Data2011
Data2012
Data2013

What happens in your system if you now try to merge more 2010 data (for whatever reason)?

New rows would appear of course.

Keep in mind: these new rows would appear regardless of if the table is partitioned.

Partitioning might put them into the 2011 dataset (depending on how you set up your partition functions)

But with sizable data like you mention (360GB) - you will need a sufficient maintenance window to process logged deletes. If you don't have that window, partitioning will be your only way to "keep up" with rolling purging.

In the example above, it's really not that bad to "add" more 2010 data. It may hang around for a year, but it will eventually purge.

The merge issue you give would not stop me from partitioning this table if otherwise partitioning was a good fit.


Partitioning does add complexity to managing the system. But it's hard to beat for rolling purges of data if the table qualifies to be partitioned.

Are there other concerns?
Post #1509123
Posted Monday, October 28, 2013 8:38 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 11:29 AM
Points: 28, Visits: 491
Hi Jim,

Thank you for your reply. I have a fairly good understanding the process of switching in and switching out. I agree that partitioning would be useful for switching out the old data. What I cannot see is any way to switch in new data since we're bringing it in via a MERGE statement rather than an INSERT statement - or am I overlooking something?

Thanks,
Bennett
Post #1509173
Posted Tuesday, October 29, 2013 9:46 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, September 19, 2014 11:54 PM
Points: 24, Visits: 384
Hey Bennett,

For switching-in partitions - you're dead on.
As soon as you're doing "Updates" (via the MERGE) partition switching won't help.

I you have a way to split the "new data" into two sets there may be value in...
Split 1: just yesterday's data enters the table via partition switching-in.
Split 2: data older than yesterday enters the table via MERGE (hopefully just a handful of rows)

But just doing the MERGE is a lot less complex. The added complexity of the above logic and adding partitioning would be weighted against the speed value provided. It may not be worth it.

-Jim

Post #1509437
Posted Sunday, November 10, 2013 12:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:38 PM
Points: 35,371, Visits: 31,912
SPAM reported.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1512977
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse