Sql server table: create partitions?

Question

I have an sql datadas, where among other things I have a prices table, where I have one price per product per store.

There are 50 stores and over 500000 products, so this table Will easily have 25 to 30 million records.

This table is feed daily over night with prices updates, and has huge read operations during day. Reads are made with readonly intent.

All queries contain storeid as part of identifying the record to update or read.

I m not able yet to determine how this Will behave since I m expecting external supply of prices but I m expecting performance issues at least on read operations, even though indexes are in place for now...

My question is if I should consider table partition by store since it is always part of queries. But then I have indexes where storeid is not the only column that is part of the index.

Based on this scenario, would you recommend partitioning? The alternative I see is having 50 tables one per store, but it seems painless and if possible to avoid the better

Answer 1

Hi all and thank you for your replies.

I was able to generate significant information on a contained environment where I was able to confirm that I can achieve excelent performance indicators by using only the appropriate indexes.

So for now we will keep it "as is" and have the partition strategy on hand just in case.

Thanks again, nice tips guys

Answer 2

if I should consider table partition by store since it is always part of queries

Yes. That sounds promising.

But then I have indexes where storeid is not the only column that is part of the index.

That's fine. So long as the partitioning column is one of the clustered index columns, you can partition by it. In fact with partitioning, you can get partition elimination for a trailing column of the clustered index, then a clustered index seek within the target partition.