SQL Azure Query Optimization Using SSMS

The first step to query optimization is determining the efficiency of the query. In SSMS, the Execution Plan graphically shows the cost of excuting a query. To see the Execution Plan, first toggle on “Include Actual Execution Plan” by selecting Query >  Include Actual Execution Plan or from the toolbar as shown below:

Then when you execute the query, you will also be presented with an Execution Plan tab besides the Results and Messages tabs:

A good resource on reading Execution Plans is the MSDN resource here.

In the above query I have made the most basic mistake – reading more data than will be required, it is rare we will need to return all the columns in a table, it’s also rare that we will need every row in a table (so always use WHERE to minimize the rows that are read) .

SQL Azure Query Optimization Using SETSTASTICS

The TSQL SETSTATISTICS command will monitor the execution of a query and then provide statistics on either the time taken to execute the query or the I/O expense of the query.

To test the time of the query execution use the Command SET STATISTICS TIME ONE at the start of a query. This will then output the parse, compile and execution times for the query to the Messages tab:

To test the I/O expense of the query execution use the Command SET STATISTICS IO ONE at the start of a query. This will then output the IO performance for the query to the Messages tab:

To learn about using this data to optimize the performance of you SQL Azure queries a great resource is Simple Query tuning with STATISTICS IO and Execution plans .

Overall Stability, Security and Performance :  A-

Azure definitely confounded some of its harsher critics by registering a very good track record for uptime, performance and most important – security. Since its launch there have been no major security issues and no large outages. I’ve run Azure since April and the only issue was two short periods (under one hour) of sluggish performance while OS patches were being applied, it seems that most users experiences have been similar to mine.

SQL Azure : D

While the performance of SQL Azure has been good, I can only register my bitter disappointment at the progress of adding features. I noted that probably the biggest weakness of SQL Azure at launch was the lack of any backup facility, we were promised two backup functions (continuous and clone) with one to appear in the first half of 2010 but still no sign of anything, no sign of encryption or compressions either. The features that have been added can only be described as basic – such as a 50GB sized database, or  the ability to automatically upgrade to a larger database size (although we still have to execute a TSQL Alter statement for this). 
SQL Azure itself is a solid product but it is still too expensive and lacking in even the basic features SQL Server users require.

Windows Azure Features : C-

Azure is still not heavy in terms of features, which is fine for a product is its first year so it would be merited a B- or C+ had it not been for the omission of .NET 4.0 support (6 months and counting since .NET was released).  Unfortunate for Microsoft the last year has been one of heavy innovation for its main rival – AWS. Most notably, AWS Simple Notification Services (SNS) allows AWS users to send notifications via several formats (even email and SMS) to alert a user of an application of an event (this is a heavily requested feature on Azure but no plans have been confirmed to add it).

Azure Tooling : C+

.NET developers are used to best in class dev tools and so we’d expect great tooling for deploying to Azure. We now have an vastly improved Visual Studio experience which allows for direct deployment  to Azure (which is great since the Azure developer’s portal is still slow and generally a poor user experience). We can also directly connect to SQL Azure databases to view and interact with database objects from SSMS 2008 R2 and generate scripts to create an SQL Azure database. But this is pretty much the minimum we would expect using a Microsoft environment.

In the minus column the Azure portal is still slow and lacking in features, monitoring of the cost is very basic and the are no tools for monitoring the   various running instances of an Azure service. Also, migration tools are pretty lacking – the SQL Azure migration is good to troubleshooting a migration to SQL Azure but won’t be able to solve many of the incompatibilities. No tool exists for migration an ASP.NET app (there are several tutorials on migration in which the process looks relatively simple but there are enough gotchas to make the migration of a reasonably sized ASP.NET app a real headache).

Pricing : D

I’ve given a D for pricing as it is the one area where improvement could easily have been made. Probably the most persistent complaint about Azure is the high cost of the entry. With a single SQL Azure database and only a single compute instance an Azure plan will cost $60 – $90 per month depending on any discounting given if you are a member of BizSpark or MSDN etc. Even worse if you need to rely on Azure’s 99.95% uptime SLA you are required to have 2 compute instances which will comfortably bring the cost to over $100 (imagine your hosting company informing you that you needed a second server if you wanted to have good uptime ). This is a relatively high barrier to entry for small developers who are building new apps which will only initially use a fraction of the small compute instance and the 1GB SQL Azure database allocation. AWS meanwhile now offers an ultra-small instance which costs only $15 per month and in addition they are offering a full free year to new users.

I would have given an E except for the fact that Azure pricing matches AWS whilst offering a lot more.  The Azure platform handles all the OS patching, scaling and security without the need for user intervention. AWS by contrast with its infrastructure as a service model merely provides the OS and offers some tools the user can implement (such as the elastic load balancer) to manage scaling, patching and security updates is left to the user.

Conclusion

Overall the grades might not have been too high but it is definitely the grade for stability, security and performance which is the most important since a poor performance would surely mean the death of the Azure platform and the only one area where Azure should definitely have been better is in pricing. We are definitely lacking in tools and features (especially for SQL Azure) but since it is just a year we shouldn’t be too demanding – the real test of Azure will be whether it can innovate over the next 1 -2 years.

The TSQL command structure below provides will produce a copy of a database:

CREATE DATABASE  database_destination_name
AS COPY OF [source_server_name.]database_source_name

Note that this TSQL syntax allows for copying to a different Azure server. This is not necessary to protect the data from a disaster recovery point of view since the data is replicated across 3 geographic locations, but it might be required if you wish to copy the database to a different admin account or perhaps a different Azure account that would be billed separately to the source database account. If the database is copied to a new server the exact same login/password pair executing the command must exist on both the source server and destination server.

The copying process will not result in any downtime for the source database, and the copy will be a full copy complete with transactional data (note that this will be the transaction data as at the end time of the copying process not the start time of the copying process).

To monitor the copying process and determine if there were any copying errors you can query the sys.dm_database_copies view. The below command will retrieve all the data regarding the copying process:

Select * from sys.dm_database_copies

A major drawback to using Database Copy for backup is cost – each copy/backup will be charged as a new SQL Azure database.

Note that Azure deployment is not available in Beta 1, but is promised in a subsequent Beta.

See this LightSwitch Tutorial for details on developing and deploying a simple LightSwitch app.

Currently, there are 22 CDN locations:

United States

• Ashburn
• Bay Area
• Chicago
• San Antonio
• Los Angeles
• Miami
• Newark
• Seattle

Europe

• Amsterdam
• Dublin
• London
• Paris
• Stockholm
• Vienna
• Zurich

Asia-Pacific

• Hong Kong
• Singapore
• Sydney
• Tokyo
• Seoul
• Taipei

South America

• São Paulo

• $0.15 per GB for data from European and North American locations.
• $0.20 per GB for data from other locations.

In addition there is a flat charge of $0.01 per 10,000 transactions.

The CDN works by caching content at its 20 worldwide ‘edge’ locations, the first time that a file is requested at an edge location it must be transferred from Azure Storage and will thus incur Azure Storage costs in addition to the CDN pricing.

Azure CDN pricing is broadly comparable to Amazon’s competing CloudFront CDN, except that CloudFront prices transactions at $0.0075 per 10,000 and also offers discounts for larger data transfers (down to $0.03 for 1,000TB per month.

The update is available now in in the AppFabric LABS environment.

There is an undoubted need for a hybrid architecture for many larger corporations since migrating existing apps to the cloud is not a simple as lot of demos show and there is a perception (whether real or not) that the data is less secure on the cloud. Enter hybrids apps – maintain the data on premise or consume on-premise apps from a cloud service.

Of course it is possible to communicate between on-premise data sources or apps and cloud-based apps using SOAP/REST communication protocols, however there are two major obstacles – discovering the service endpoints (since these may change due to dynamically assigned IPs)  and navigating through firewalls. These problems can be overcome by allowing apps to selectively open ports which is inherently insecure, and using relay systems that sit between the firewall and the apps and act as a bridge, thee systems tend to be very complicated and hard to implement.

The Azure Service Bus attempts to solve this issue by proving a service which allows applications which need to communicate with eachother to register with it. The requesting app is given a Service Bus endpoint to communicate with the data source/service app. Essentially the services are provided by service apps run behind the firewall, and the connection endpoints are provided by the Azure Service Bus. It should be noted that the Service Bus allows communication with non-.NET services , so Linux/UNIX hosted apps can register with the Service  Bus and be consumed by .NET apps.

Security is provided by the Azure AppFabric Access Control, which applies user-defined rules to ensure security when an app claims tokens via the STS service provided by the Access Control.

Thus the Service Bus can be used to build hybrid apps which span both on-premise and cloud services.

Compute Instances

The standard compute instances sizes are very similar on both platforms – Small (1 core processor, 1.7GB RAM) , Large (4 core processors, 7GB RAM), Extra Large (8 core processors, 15GB RAM ). The only differences being that Azure offers slightly larger drive space on the Large and Extra Large instances, on the Large instance Azure provides 1TB of space vs 0.85TB on AWS , and on the Extra Large instance Azure offers 2TB vs 1.8TB on AWS. However this is probably a minor issue to most cloud platform users who will be more interested in the persistent storage offered (see below).
Azure includes a Medium instance which provides 2 core processors, 3.5GB RAM, 500GB hard disk and is a useful bridge between the Small and Large instances for growing applications.

In addition, Amazon offers a variety of larger instances. High Memory instances provide 17GB – 64GB of memory and High CPU Instances provide up to 20 ‘compute units’ (which equates to 20 cores of processing power).

Pricing for compute instances is identical on AWS and Azure.

Compute instance pricing on AWS and Azure:
Small : $0.12 per hour
Medium : $0.24 per hour  (Azure only)
Large : $0.48 per hour
Extra Large :  $0.96 per hour

(NB : All AWS pricing is based on Windows instances, Linux based instances are typically 30% cheaper)

Amazon is currently running a pilot program to transfer Windows Server licenses to EC2. Under this program, pricing for the EC2 instances drops 30% to the Linux EC2 levels. See full details of the Windows Server Mobility program.

The basic pricing models for both AWS and Azure are very similar, however in addition to the standard pricing model Amazon offers Reserved Instances. A reserved instance involves payment of a one time up-front fee that ‘reserves’ an instance which is then available at a lower hourly cost. Instances can be reserved for one or three year terms and pricing varies according to instance size and length of the reservation.

For an assessment of the pricing impact I have amortized the upfront payment equally over the term of the reservation (assuming 12 months usage at 730 hours per month). For example, the one time fee to reserve a Small Instance is $227.5 and thereafter usage on the instance is charged at $0.05 , thus assuming full utilization of the instance the $227.5 payment is $0.026 over the year (227.5 / 12/ 730) and the total hourly cost of the instance is $0.076 (a saving of 37% over the normal charge of $0.12 per hour). If the instance was reserved for three years the charge would be $350 and the final cost would be $0.0.61 per hour.

Amazon AWS Reserved Instance Pricing (Assuming Full Utilization) :
Small : $0.076 (1y term) , $0.061 (3yr term) 
Large : $0.304 (1y term) , $0.253 (3yr term)
Extra Large :  $0.608(1y term) , $0.507 (3yr term)
All charges are per hour

Amazon also offers spot pricing, which is hourly updated pricing for instances that cannot be booked for any period longer than an hour which  is essentially live market pricing for instances. This is likely only to have very niche appeal for apps which can be run at any time – large batch processing tasks could conceivably use this pricing service.

Storage

Cloud apps need to use persistent storage for files (such as images, videos etc) which are then referenced in the app. Both Amazon’s S3 (Simple Storage S0lution) and Azure Storage price storage at $0.15 per GB, however S3 offers discounts for very large data amounts stored (for example, $0.140 per GB for 50TB – 400TB of storage). 
Transactions on both Aaure and AWS are billed at $0.01 per 10,000 transactions, it should be noted there is a slight difference in this as S3 only charges for requests for stored items whereas Azure charges for inward storage transactions as well.

For the Azure CDN versus Amazon’s CloudFront, see our separate article on Azure CDN Pricing .

Data Transfers

Under traditional hosting this would be called bandwidth but the convention for cloud providers is to use the term Data Transfers.

F0r both Azure and AWS, Data Transfer is charged at $0.10 per GB of inbound traffic and $0.15GB per GB for outbound traffic. Azure pricing is constant for all volumes whereas AWS provides discounts for large volumes of outbound traffic – $0.11 per GB for > 40TB ,  $0.09 per GB for >100TB , $0.18 per GB for >150TB.

Prior to June 30, 2010 AWS offers free inbound data transfer and Azure offers free inbound off-peak data transfer.

Databases

Database offerings for Azure and AWS are structured very differently and for that reason are not directly comparable. Amazon’s AWS does not offer  per  database plans and fro SQL Server  you can only use a SQL Serve license to install a full instance of SQL Server on an EC2 instance , or you can use the free SQL Server Express. Azure, by contrast, only offers individual databases of the SQL Server based SQL Azure.

Amazon offers relational database (MySQL) instances which are comparable to EC2 instances with a full MySQL instance installed (and can therefore have numerous databases).

SQL Azure databases are priced at $0.014 per hour for a  database with 1GB of storage and $0.14 per hour for a 10GB database. A 50GB database for SQL Azure has been announced and will be available from June 2010.

By contrast, Amazon’s Small Relational Database Instance has 1.7GB RAM and a single core processing unit which is charged at $0.11 per hour. Storage is charged at $0.1 per GB per month and an instance can have between 5GB and 1TB of storage. 
AWS database instances can scale all the way up to the Quadruple Extra Large DB Instance which has 64GB RAM ,  with 26 compute units and 1TB storage.

Both Amazon and Azure offer non-relational databases which are considered persistent storage and charged accordingly (see above).

Packaged Pricing

The obvious question any user will have is what will be the total cost to run the app on the cloud. To this end Microsoft has two offerings which provide a package of resources available at a single monthly charge (overages are charged at the standard rates). By contrast Amazon only offers al-la-carte pricing.

The Azure Development Accelerator Core is charged at $59.95 per month for a six month subscription and provides:

• 1 month of utilization for a Small compute instance
• 10GB of persistent storage plus  1,000,000 storage transactions
• Data Transfer – 14GB outward, 7GB inward

A comparable setup on Amazon would be $95 if the EC2  instance is not reserved, $61.5 if the instance is reserved for one year and $52 if the instance is reserved for three years.

The Azure Development Accelerator Extended is charged at $109.95 per month and is identical to  the Accelerator Core package with a 10GB SQL Azure database included, and this package therefore doesn’t lend itself to a comparison with Amazon AWS.

Conclusion

Across all the services the pricing for Azure and Amazon AWS is very similar, however there are three key differences :

• Databases - Azure only offers a per database pricing, whereas Amazon offers full database instances. As Azure offers the SQL Server based SQL Azure database this is a more full featured and more expensive alternative to Amazon’s MySQL based offering.
• Reserved Instances – Amazon allows for an upfront payment to reserve an instance for one or three years which results in a substantial (37% – 50%) saving if the instance is used for the full period.
• Packages - Azure offers a package of services at a single monthly charge which results in a 50% saving of the regular prices.

NB In this review I have ignored services which have no natural equivalent such as the Azure AppFabric services, or the Azure Asian bandwidth charges for Azure instances location in Asia (AWS instances do not have a geolocation feature).

Here are my observations in no particular order:

• Sign-up process : Although it has been cleaned up a bit it is still not a clean process, Microsoft should take a look at how some of the other cloud providers such as RackSpace handle signups.
• Web interface/portal : The web portal is a very sluggish meaning that deploying an application is slow. But at least it is intuitive and easy to navigate. Could use more AJAX (or even Silverlight) to enhance the user experience as it looks quite dated right now.
• Familiarity : A big question a lot of .NET developers have is how close is Azure to the standard Windows platforms they are used to developing for.  In short, if you developed for ASP.NET you will find Azure a very familiar environment. The big changes will be working with Azure Storage, utilizing the cloud facilities (such as scaling) and building more distributed apps with different components of the apps communicating with eachother (most Azure apps will probably have at least two separate roles such as a Web Role and a Worker Role) .
• SQL Azure :  This really deserves a review all of its own as it is a key pillar of the Azure platform. The SQL Azure web portal does very little except allow for the creation of databases and setting up firewalls but SSMS 2008 R2 now connects to SQL Azure databases and shows the objects in the objects browser (previously these were invisible rendering the SSMS GUI of little use). Many of the features of SQL Server 2008 are supported in SQL Azure, but unfortunately it is the features that are missing which cause huge issues – backups, compression and encryption are all absent (they are promised for 2010 but looks as though they are still some way off). 
  SQL Azure comes in two editions – 1GB priced at $9.99 per month and 10GB priced at $99 a month (50GB is coming in June). For starters the 1GB is too large for small developers and 10GB is a big jump from 1GB. The database size should start lower and be charged incrementally in 500MB or 1GB increases in the same way as storage.
  With Update 1, SQL Azure now has the ability to automatically move between database editions (ie the standardized sizes offered by Microsoft) however this is still not ideal. Changing between editions requires running an ALTER statement , why can’t the database just scale up and down and be billed according to the size.
• Azure storage : Blobs, queues and tables all work very well and the APIs are comprehensive and simple to implement (I had a simple storage app for uploading and displaying blobs through the  CDN working within 30 minutes) . The addition of the CDN is great and it is well integrated into the platform (in contrast with Amazon’s CloudFront which feels like a bolt on to the S3 storage service).  
  Tables are a non-relational database and a very good solution for developers looking for a simple database. One issue with tables is the current throttling limit of 1000 records per recordset returned from a Table, this means developers have to write additional code to  handle this limitation which actually turns out to be a significant increase in the total amount of code to handle simple Table operations.
  Windows Azure Drives were recently announced as a fourth storage medium. These analogous to  NFTS volumes on standard servers (and the APIs are very similar) , their inclusion in Azure is to ease the pain of migrating legacy apps to the Azure platform but otherwise it doesn’t offer many advantages versus the standard three Azure storage mediums.
• Worker Roles : Worker roles are very similar to windows services and are used to perform background processing for Azure apps. Although Worker Roles don’t offer much new in terms of their features, it is the implementation and integration into Azure apps that is a big step forward. Previously, windows services were not a native project type in Visual Studio and there wasn’t much documentations on using them to perform background tasks for web apps, they also had to be deployed on a Windows Server box separately to the web app (which also meant that developers using shared hosting couldn’t use them). With Windows Azure, Worker Roles are tightly integrated into the Azure Cloud solution, there are plenty of APIs and code samples to integrate it with the core web app and it is deployed in the same package as the web app.
• Deployment : Simple but slow! After apps are tested and debugged on the development environment they are packaged up into two files which are then uploaded in the Azure web developer portal. The deployment process is quite sluggish (20 minutes to deploy my relatively simple app) but it is very neat and uncomplicated.
• Local Development Environment : With the exception of the lack of a local SQL Azure database, the local development environment is a very solid tool. It supports almost all aspects of the Windows Azure platform including multiple instances and Azure storage. The ‘Dev Fabric’ is the location version of the Azure AppFabric (which is a service which monitors and controls the Azure application) . The Dev Fabric is launched automatically when an Azure app is run or debugged from Visual Studio and it gives full access to each instance such as the ability to attach a debugger to an instance. 
  Deploying an app which is developed and tested locally is a snap, the storage connection string just needs to be changed (and maybe the database connection string if a local SQL Server was used for testing).
• Tools : Almost non-existent. There are only a couple of third party monitoring tools plus several tools to work with Azure storage. In addition there are some open source tools on CodePlex but overall there isn’t much out there currently…
• Migration : This is still a big weakness of Azure and probably means that most apps running on Azure will be custom built for Azure and not migrated to it.  There are enough inconsistencies between SQL Azure and SQL Server to mean that a lot of work may have to be done to migrate some databases, there is a migration tool on CodePlex but it doesn’t do too much beyond modify some of the syntax of the database creation script. There is no automatic migration tool for ASP.NET , meaning the changes have to be done manually. This may not always involve a huge modification since  Azure Web Roles are very similar to ASP.NET apps but some areas such as storage may need a complete code rewrite.

I will keep this review updated as I use Azure more and the platform matures, please do get in touch if you have any comments or experiences on Azure.