Throttle rate for insert operations

Question

I am currently running a large generated script wherein 10,000 phone numbers (one header record, one number record, so 20,000 insert operations in total) are being run.  I am using SQL Server Management Studio connected to an azure database.

It is taking a VERY long time (over 30 minutes), which is making me worried.  At peak capacity, I could see us generating 50-100 transactions a second (adding in logs, updates, etc.) per database (and we have multiples per sql azure server).

Granted, I am starting out small, with a 1GB web, to be upgraded as demand increases (from day one, bigger is overkill).  Is that going to slow things down?

What I'm really asking is:  am I barking up the wrong tree thinking that SQL Azure can be a replacement for a dedicated SQL Server machine?  I'd like it to be, because it is easy (and I can shard like crazy to keep transactions to around 100 per second), but would like to know if SQL Azure can handle that.

---

John Carroll

Answer 1

Granted, I am starting out small, with a 1GB web, to be upgraded as demand increases (from day one, bigger is overkill).  Is that going to slow things down?

What I'm really asking is:  am I barking up the wrong tree thinking that SQL Azure can be a replacement for a dedicated SQL Server machine?  I'd like it to be, because it is easy (and I can shard like crazy to keep transactions to around 100 per second), but would like to know if SQL Azure can handle that.

---

John Carroll

Hi John,

You can start small (with 1GB Web Database) and then go for 5 GB, etc.

As for the 2nd question: It depends on each case, but maybe in most of them it can serve as a good enough replacement for SQL Server.

Keep in mind that... There are connection (and other important) constraints on SQL Azure that you should be aware of. You can find more information about it here. Keep in mind that SQL Azure provides a large-scale multi-tenant database service on shared resources. In order to provide a good experience to all Windows Azure SQL Database customer your connection to the service may be closed due to the following conditions: Excessive resource usage, Connections that have been idle for 30 minutes or longer, Failover because of server failures.

Therefore, having a dedicated SQL Server box is quite different. I also encourage you reading this nice article on Compare SQL Server with Windows Azure SQL Database.

Hope this helps!

---

Best Regards,
Carlos Sardo

Answer 2

Hello, carlos

What isn't clear to me is what constitutes "excessive resource usage". Is there a specific transaction per second limit or target I should know about.

And regarding my 20000 record problem...what could be the cause of it taking so long? They were simple insert statements, and on a dedicated machine will complete in seconds. If allowed to run straight it would have taken over an hour on SQL azure.

---

John Carroll- Director of Technology, ForgetMeNot Software

Answer 3

Hi John,

Please review this link for information about what constitutes exessive resource consumption in SQL Database: http://social.technet.microsoft.com/wiki/contents/articles/1541.windows-azure-sql-database-connection-management-en-us.aspx

Then take a look at this one to understand the internals of SQL Database. This will basically explain why Inserts are relatively slow (2-phase commit): http://social.technet.microsoft.com/wiki/contents/articles/1695.inside-windows-azure-sql-database.aspx

With this in mind, you have certain options available to you to improve performance, but overall you are bound by the throttling limitations. Here is a similar post on stackoverflow that discusses ways to go around the limitations: http://stackoverflow.com/questions/11879431/ways-to-overcome-the-limitation-of-concurrent-operations-in-sql-databaseazure/11884548#11884548

---

Herve Roggero, MVP SQL Azure Co-Author: Pro SQL Azure http://www.herveroggero.com