SQL Azure Federation - how much data before performance benefits?

Posted by Donald Hughes on Server Fault See other posts from Server Fault or by Donald Hughes
Published on 2012-10-26T15:22:02Z Indexed on 2012/10/27 23:05 UTC
Read the original article Hit count: 335

Filed under:
|

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).

© Server Fault or respective owner

Related posts about sql-server

Related posts about Azure