SQL Azure followup

Oopps! Upgrade your browser pretty please. Oopps! Upgrade your browser pretty please.

I have received quite some attention after my previous blog post regarding SQL Azure and INSERT performance. Therefore I would like to comment on some of the questions and issues raised, as well as giving my opinion on SQL Azure amongst other things. I will divide it into two sections, an overall perspective on performance testing and general platform evaluation, and one section about the technical aspects of that particular test. If you are short on time, here is a short summary: Even if the test I performed and reported on in itself is valid and correct, it was a simple test. It tests only one of the many aspects that should be taken into consideration while evaluating performance on SQL Azure, or even indeed on any other SQL Server platform or server. And no, I do not recommend that you run your production SQL Server databases on a laptop 🙂

Overall perspective

I do a lot of consulting on SQL Server, and have done for about 16 years, helping customers in all types of projects regarding SQL Server. And in my experience, the more I can base my recommendations on fact, the better the results. Therefore I like to test and verify as many aspects of my recommendations as I can. However, a single test will not give you the answer to what road to take when choosing the database platform that is best for you. Besides from functionality, there are three main areas that should be considered while testing, evaluation and choosing a database platform for your application: Performance, Availability and Cost. Each customer have their own order of priority of course, but generally most of them want as much Performance and Availability as possible, with as little Cost as possible. Of course. The trick is to find the right mix of these three for each customer. And as with most things, it quickly comes down to tradeoffs between the different goals. And the three main areas are themselves of course divided into many sub-areas, which in turn needs to be balanced right for each customer.

Availability

This is the most critical factor, when it really comes down to it. Particularly the Disaster Recovery aspect of it. It can be really, really costly if your database server fails and you can’t recover your data. Every year,  companies goes bankrupt for that reason. The good thing is that a real disaster rarely happens, the bad news is that this tends to cause people to view it as less important. But I think that this should be your highest priority, in the sense that you define the maximal data loss and the maximal service loss (in minutes or hours) that you can accept, and design your database platform to meet that criteria. The money you have left in your budget can then go to increasing performance. However, sometimes availability comes at the cost of performance. This can be the case in for example SQL Azure, which always keeps 2 additional copies of your database and at least one of those outside your primary datacenter.

EDIT: The copies of the databases are all within the same datacentre. This is synchronous copies, and that adds some overhead. Other forms of High Availability or Disaster Recovery techniques that will add overhead, and therefore decrease performance is SAN discs, Mirroring, and Replication. Logshipping is actually the option that adds the least overhead. But it is also very important to test and verify the choices you make here, such as regularly restoring your backups and checking their consistency, and performing controlled Disaster Recovery scenario exercises.

Performance This area is almost too big to even get started on. But apart from our blog, another good sources of information is http://www.sql-server-performance.com/ . The most critical factor here is the application itself, that is from the database perspective the database model,  queries, indexes and design choices such as what data to put inside our outside the database, stored procedures vs ad hoc queries, OR mappers or not, and so on. Then there are the setup and configuration of SQL Server itself, and the server/infrastructure hardware. Once again, testing and verifying the choices you make are important. Make sure that you do it in your test environment before you go into production, it is so much easier and cheaper to remedy any problems in that stage. Many times, IO is the main bottleneck in the hardware. Therefore I usually verify that the discs performs as expected using for example SQLIO, and various scripts, to make sure I know that there are no problems with the setup of the discs. In general you will have to spend a lot more money on a SAN to get even close to the performance you can get from a locally attached array of discs. SSD is great in many respects, but still a bit too pricy for most customers. Adding RAM to your server can greatly offset many IO related bottlenecks, with one exception: Adding, Deleting or Changing data always puts pressure on your transaction log drive, as the operations have to be written to stable media (usually disc) before actually being performed on your data.  Evaluating performance of your particular SQL Server and Hardware combo setup, gives insight to what that platform can give you in terms of raw transactions per second, but in reality you will most likely have much to gain from looking long and hard at your application first. But it is good to know what your environment can deliver, after it has been set up and configured as good as it can be.

Cost This is an interesting aspect, that can be dissected in many ways. TCO and ROI are examples of different popular ways of looking at cost. The costs incurred in database platform related projects are usually first upfront costs from hardware, software and man hours and secondly it is the running costs of the platform. This is where SQL Azure can really shine compared to an on-site SQL Server solution. The startup cost for hardware and software are zero. And the running costs are low as well. My tests where run on a 1 GB Web Edition of SQL Azure that costs from $9.99 (https://www.windowsazure.com/sv-se/pricing/calculator/) Startup and running costs for on-premise SQL Server is far higher, even if there is a free version of SQL Server available ( http://www.microsoft.com/sqlserver/en/us/editions/express.aspx ) If you already have an IT infrastructure, or if you require many and large databases, your calculations might be different of course. As always, if you want the best possible solution in terms of performance and availability, be prepared to pay. On the other hand, it might be even more costly if you do not plan and test properly in advance, and implement a solution that actually fills your particular needs.

The technical details First I want to state that you will have to make some changes to the scripts in the previous blogpost to make them run on SQL Azure, as there where index options in there that is not supported on SQL Azure. But that was just due to the fact that I scripted out the indexes using SSMS from SQL Server 2008 R2. That makes no difference to the actual test however. So, to address some of the questions regarding the actual test, I have compiled a little list of Q&A: – Q: Use newsequentialid() instead of newid(). A: That is not possible, as newsequentialid() is not supported yet on SQL Azure – Q: Change the Clustered Index to to on the INT_COL column instead. A: I haven’t tested this, but I suspect it might impact the amount of transaction log that we create quite a lot (see http://sqlskills.com/BLOGS/PAUL/post/How-expensive-are-page-splits-in-terms-of-transaction-log.aspx) But in fairness, that improvement would also benefit the other system. The test was deliberately designed to be though, so that differences in results would be easier to spot. – Q: Wasn’t the test unfair, as your local where inserting over the internet to SQL Azure? A: No, I was using a Stored Procedure, so the Insert was local to both SQL Azure and my local machine. – Q:

But how can your laptop be faster? A: In this case, I think it depends on a few factors: My local disc has write cache enabled, which is of course not recommended on production systems (reference: http://msdn.microsoft.com/en-us/library/ms186259.aspx) SQL Azure also have quite a lot more redundancy built in, than my laptop ? Aside from the primary database, it also has 2 synchronous copies. This is excellent for Availability, but introduces overhead that impairs performance.  Since you are guaranteed to have at least one of the copies outside the primary datacenter, I believe that that will be the main issue here. In addition, SQL Azure is a shared environment, and I have no control over what was running on the other databases running on the same hardware. Additionally , SQL Azure has built in throttling but that was most likely not invoked in this case. (Lots of good info is here: http://social.technet.microsoft.com/wiki/contents/articles/sql-azure-performance-and-elasticity-guide.aspx)  

If you have any questions about anything in this article, feel free to contact me at Steinar<dot>Andersen<at>sqlservice<dot>se or via this page!