SQL Azure Performance – Query Optimization
SQL Azure Performance Tuning is a much tougher task than SQL Server, – SQL Azure lacks some of the basic tools available to SQL Server DBA’s. There really isn’t too much to do tuning your SQL Azure instance as almost all the settings available for SQL Server are automatically managed by the SQL Azure platform. However, there is still some performance tuning to be done – mostly in the execution of the operations performed by the database. A major tool missing from SQL Azure is the SQL Server Profiler, despite this however there is still some optimization that can be performed on your SQL Azure queries – you can do this by either using SSMS or SET STASTICS.
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
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: