Share via

Performance tuning methodology of SQL Azure

  • Article

Designing for high performance is becoming more important when using a database running in the cloud because development objectives tend to minimize roundtrips and the amount of data being returned.

Performance tuning is a very complex topic,in this article,I will only introduce some selected techniques.Many techniques presented here are similar to the ones available in on premise SQL Server,though some of the tools aren't supported in SLQ Azure.

 

Methods and Tools

Because SQL Azure is a hosted and shared infrastructure ,it's importnat to understand which tuning methods are available and which aren't.The below table outline some of the key methods traditionally used by developers and DBAs in tuning a database system.

Method or Tool  Available?  Comments
 SQL Profiler  No  Tools using server-side traces,such as most auditing tools,SQL Profiler,and the Index Tuning Wizard,aren't supported
 Execution Plan  Yes  SQL Server Management Studio can display actual execution plans against a SQL Azure database.
 Perfmon   No  Any Windows monitoring tool that is typicallly used for performance tuning is unavailable
 DMVs  Limited  A few dynamic management views(DMVs) are available and provide insightful information about running sessions and statements previously executed
 Library metrics  Yes  ADO.net provides library-level statistics that offer additional insight to developers ,such as the processing time from the consumer standpoint and bytes transferred

 

Coding implications

Because we have no access to the server-sise configuration settings of SQL Azure,such as disk configuration,memory allocation,CPU affinitization,and so forth,we need to place more emphasis on the quality of your SQL statements-and,now more than ever,our network traffic.Indeed,the number of network roundtrips our code generates and the number of packets returned have an impact on performance because the connection to SQL Azure is a far link and the communication is encrypted. Our performance-tuning exercise should include the following areas:

  • Connections pooling.

Because establishing a new connection requires multiple network roundtrips by itself and can affect our applicaions's performance,we should ensure that our connections are pooled properly.In addition,SQL Azure will throttle us if we stablish too many connections.

  • Packet count .

Because the time spent to return data is greater than we may be used to,we need to pay attention to SQL code that genereates too many packets.For example,'print' statements generate more network traffic than necessary and should be removed from our T-SQL if at all possible.

  • Indexing.

SQL Azure may throttle our connections if it detects that our statement is consuming too mny resources.As a result ,proper indexing becomes critical when tuning for performance.

  • Database Design.

Of course,certain database designs are better than others for performance.A heavily normailzed design improves data quality,but a loosely normailzed database typically improves performance.Understanding this tradeoff is also important when we're developing in SQL Azure.

 

Here I only give a brief introduction about the tools,methods,coding implications that can be used in SQL Azure. I will give more details in my next blogs..

Comments

  • Anonymous
    August 23, 2015
    Hi Ran, Did you take this off any book, or is this something by your own?