Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

To avoid premature optimization, I don't want to implement SQL Azure's Federation too early. Is there a rule of thumb for how much data a table would need to have before seeing performance benefits from sharding? I know there won't be a precise answer as there are too many variables to consider, especially with much of SQL Azure's resources being hidden/unknown.

To put it into several, more concrete examples, would Federation improve performance in any of the below table scenarios:

  • 100,000 rows (~ 200 MB)
  • 1,000,000 rows (~ 2 GB)
  • 10,000,000 rows (~ 20 GB)
  • 100,000,000 rows (~ 200 GB)

For the sake of elaboration, we can assume this is the largest table that would be federated, which consists of order details, which is joined to an orders table with a 'customer_id' foreign key, which would be the distribution key.

This is a fairly standard multi-tenant, CRUD order entry system, with a typical assortment of reporting needs (customer order totals by day/month/year, etc).

share|improve this question

2 Answers 2

It is really going to depended and you probably won't get a good answer here because there are so many variables. On the 100M row example you'd want to shard. On the lower numbers probably not.

Now that said it'll probably be easier to set the sharding up now rather than later.

share|improve this answer

If you have that much data running, I would consider running SQL Server in a dedicated VM. You have full control over: Enterprise features, format allocation of the data disks, striping the data disks, using trace flags, full SKU features etc.

A single SQL Server dedicated on a VM will net you more performance than sharing a SQL Azure Database and it will give your more of a thresh hold, before having to optimize.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.