Optimizing SQL Azure Database

1. Dynamic Management Views

SQL Azure provides a few handy system views called dynamic management views (DMVs) that are also available in SQL Server. SQL Azure exposes a subset of the DMVs, but all those related to query execution are available.

sys.dmexecconnections 

sys.dmexecqueryplan 

sys.dmexecquerystats 

sys.dmexecrequests 

sys.dmexecsessions 

sys.dmexecsqltext 

sys.dmexectextquery_plan

2. Connection pooling

Connection pooling is an important consideration for performance. Although this statement is generally accurate in the world of database programming, it becomes critical for SQL Azure. A poorly designed application may create too many connection requests, which can end up flooding SQL Azure. If too many connection requests are established, your connections will be throttled, meaning that you can no longer connect for a period of time.

3. Indexes

Creating the right indexes can be complex; it can take a long time to fully understand indexing and fine-tune database queries. One of the most important things to remember with indexing is that its primary purpose is to help SQL Azure find the data it needs quickly.

Indexes are like smaller tables that contain a subset of the primary table. The tradeoff is that indexes consume space and must be maintained by the SQL Azure engine as the primary data changes, which can impact performance under certain scenarios.

Remember, you must have a clustered index on each table in SQL Azure Database. Windows Azure SQL Database does not support tables without clustered indexes. A table must have a clustered index. If a table is created without a clustered constraint, a clustered index must be created before an insert operation is allowed on the table.

4. Indexed views 

Indexed views are an excellent alternative when you absolutely need to JOIN data, and traditional indexing doesn't yield the performance you're looking for. Indexed views behave like tables; the data covered is materialized to disk so it can be retrieved quickly. Before jumping on indexed views, understand that they have certain limitations and that due to their nature, you may incur a performance hit through the usual Insert, Delete, and Update statements.

5. Stored procedures

You've seen various ways to tune your statements and improve execution plans. However, keep in mind that you also have stored procedures at your disposal. Stored procedures can give you an edge if you need to execute logic that requires a large volume of data. Because you know that returning lots of data turns into a performance problem in SQL Azure, you can place the business logic that needs the data in a stored procedure, and have the procedure return a status code. Because you aren't charged for CPU time, this becomes an affordable option.

Stored procedures can also be an interesting security tool, allowing you proxy the calls to underlying tables through a procedure and never allowing direct access to the tables.

There are a few restrictions in SQL Azure Stored Procedures. You cannot use the following in CREATE PROCEDURE statement;

• FOR REPLICATION option
• EXTERNAL NAME ( external methods)
• ENCRYPTION option

Also, following XML commands doe not work in SQL Azure;

• sp_xml_preparedocument
• sp_xml_removedocument
• OpenXML

We have the workaround to tackle with these commands but that is not in the scope of this article.

NOTE: Reference taken from http://programming4.us/