SQL Azure - How to measure current workload to estimate when throttling will occure

First of all, I'm not a DB expert .. so I'm not sure if my terminology or anything is correct here, but if you can bear with me I hope you'll get the idea.

I have a SQL Azure database which powers a social network. Millions of transactions occur every day from very simple ones to complex SELECTS which sort through tens of thousands of users based on distance etc.

My user numbers grow daily and I know (believe) that at some point I'll need to implement sharding, or make use of SQL Azure Federation to keep my app up and running due to 1 SQL Azure having limited resources ... but my question is, how do I figure out when I'm going to need to do this?

I know that when I start to use too much resources, my queries will be throttled ... but for all I know this could start happening tomorrow, or be years away.

If I know I'm hitting 80% of what I'm allowed to开发者_运维技巧, then I know I need to prioritise a solution to help me scale things out, but if I'm only using 10% then I know I can put this on the back-burner and deal with it later.

I can't find any way, or even mention, of how to measure this?

Any suggestions?

Thanks,

Steven

---

I don't know of any inbuilt way to measure this. If somebody does then I would be very interested to hear about it.

However there is a great library from the Microsoft AppFabric CAT Best Practices Team which is a transient fault handling framework. See here

This does a number of things including handling retry logic for opening connections and running queries. You could use that but extend it slightly to log when you were being throttled by SQL Azure.

This probably wont give you as much warning as you want, but will help you know when you are getting closer to the limit. If you combined this approach together with some kind of application / database stress testing then you can find your limits now before your real usage gets there.

Based on the numbers you have given I would definitely start looking at sharding now.

---

I would recommend you read the article below if you haven't done so; it contains interesting information about the underlying reasons for SQL Azure throttling conditions. Understanding what is being monitored for throttling can help you figure out why your database is being throttled.

Technet Article: SQL Azure Connection Management

Thank you for mentioning the Enzo library by the way (disclaimer: I wrote it)!

However understanding the reason of the throttling would be my first recommendation because depending on the reason, sharding may or may not help you. For example, if the issue of the thottling is excessive locks, sharding may indeed reduce locks to a single database, but it could come back and bite you at a later time.

Thank you Herve

---

best practices to fight throttling 1) keep queries as short as possible 2) run workloads in batches 3) employ retry mechanisms.

I would also like to point you to couple of resources. 1) sql azure throttling and decoding reason codes: http://msdn.microsoft.com/en-us/library/ff394106.aspx#throttling 2) http://geekswithblogs.net/hroggero/archive/2011/05/26/cloud-lesson-learned-exponential-backoff.aspx