Sql-server – SQL Server Partitioning – what to use for partition key

partitioningsql server

I've never worked with SQL Server partitioning but I currently faced with designing a database for which the volumes probably warrant it. The system is for coupons. The coupons are to be issued periodically, usually every six weeks although there will also be ad-hoc issuance – eg for a special event. There are 15 million customers and for each issuance event, every customer will receive 6 different coupon types, giving a total of 90 million coupon instances. We need to track the coupon instance redemption data and maintain this for 6 months, although typically a coupon is only valid for six weeks. Any redemption request for an invalid coupon will not reach the database because it will be validated by the POS till.

Over a six month period we'll need to store up 360 million rows in the Coupon Instance table and up to 72 million (assuming max 20% redemption rate) in the Redemption table. I get the feeling that these numbers are too big for a single partition?

My question is – what to use as the partition key? One obvious candidate would be by issuance event, giving approximately 6 partitions. But then I think that maybe even that would give a partition size that is too large to allow for optimal performance? Would it be possible to partition by two keys eg by issuance event + last digit of the customer id? So the logic would be:

If issuance event = 1 and last digit of customer id < 5 then
    Store in partition 1
Else if issuance event = 1 and last digit of customer id >4 then
    Store in partition 2
Else if issuance event =2 and last digit of customer id <5 then
    Store in partition 3
Else if issuance event =2 and last digit of customer id >4 then
    Store in partition 4
Etc...

Also, I'm not sure of the spec of the database server that we'll need. Will 16gb and 8CPUs be enough? The db needs to be able to return a result from the coupon instance table, keyed on a numeric barcode value in less than half a second. The expected transaction request for validate (select) and redeem (insert) is expected to peak at approximately 3,500 per minute.

The SQL Server 2008r2 64bit db server will be provisioned as VM from a very powerful host with access to a high performance and large capacity SAN.

I'd be very grateful for any advice from those that have deployed a SQL Server solution to manage similar volumes.

Regards

Rob.

Best Answer

The server spec questions should be directed to either Serverfault or DBA.SE.

For the partitioning question, I don't think you necessarily need to partition for this.

360m rows is a lot but it's not too unwieldy.

Do NOT under any circumstances try to partition based on the last digit of a field. I'm not sure this would even work, but it's not SARGable which wouldn't be tenable.

If you only need to do a single row seek based on a numeric key, partitioning probably will not help.

If you do decide to pursue the partition route, bear in mind to be effective all your queries need to include your partition key(s) so the engine knows which partition to check. Otherwise it will check them all and you actually hurt performance.

Related Solutions

How to horizontally partition an oracle database table, and should I

To answer your second question first: yes you should partition. Oracle's query optimizer has a feature called partition elimination, which will check the predicate for the partition and only execute the SQL on the appropriate partitions.

Partitioning also leaves all the data in one space. Conceptually, think of it as many tables of identical structure, with an implicit UNION ALL between them if you were to do a SELECT from the entire table. Except "under the hood" Oracle sorts the actual rows into the right "table" based on the criteria you specify. Any rows that come in that don't match any of the criteria, go into what's known as the "default" partition.

For what you want to do, a "range partition" might be a good approach (so you can add more tenants later), e.g.:

create table transaction (id, tenant_id, a, b, c, d)
partition by range(tenant_id)
partition p_tenant1 values less than (2) tablespace ts_tenant1
partition p_tenant2 values less than (3) tablespace ts_tenant2
partition p_tenant3 values less than (4) tablespace ts_tenant3
partition p_tenantd values less than (MAXVALUE) tablespace ts_default;

Then later

alter table transaction 
add partition p_tenant4 values less than (5) tablespace ts_tenant4;

This will create something that looks and behaves just like a normal table, but actually rows where tenant_id=1 will be in a partition in tablespace ts_tenant1, and queries will ignore all other partitions. Queries across the entire table can run in parallel on each partition. If tenant_id=4 in this scenario, the row will live in ts_default unless you add the new partition as shown, but the INSERT won't be rejected because there's no partition for it!

FWIW At my site we use partitioned tables in our 40Tb DW, you don't need to worry about this approach scaling or performing, if you choose your partitioning strategy well (e.g. you could partition on tenant_id then subpartition on month perhaps), create the right indexes, and so on.

Sql-server – Multiple Partition Keys

You really don't need partitioning for this to work efficiently, which is what I told you on your last question as well...even if you have billions of rows.

If you cluster on the BarcodeID (which I am assuming is unique) and put a nonclustered index on LoyaltyCardID it should work just fine. These are NOT complicatd queries with a lot of additional logic from the sounds of things, and simple seek operations are extremely efficient on their own.

Are you getting pressure to partition or is it just something that you have decided to do?

Best Answer

Related Solutions

How to horizontally partition an oracle database table, and should I

Sql-server – Multiple Partition Keys

Related Question