Analyze performance between SQL Azure and SQL Server on premise.

You need to be convinced that the performance in SQL Azure is acceptable for your end users before you can move you ron premise databases to SQL Azure. In the on-premise environment you have a lot of tools which you can use to measure the SQL performance of your application. However, in SQL Azure the tools are not so good as the on–premise versions. For instance:

• You can’t connect with SQL Profiler to a SQL Azure database.
• You can’t connect with Windows performance monitor (Perfmon) from an Azure worker role to your SQL Azure database server.

I strongly hope that this will be improved by Microsoft in the future. In this blog I will describe what you can do to analyze performance of your application in SQL Azure. Most of the methods requires a lot of manual work, but it is better than nothing.

 

First of all you need to upload a version of your on premise database to SQL Azure. Use the SQL Azure Migration Wizard. The SQL Azure Migration Wizard is an open source application, which is designed to help you to migrate your SQL Server 2005/2008/2008R2/2012 databases to SQL Azure.  SQL Azure Migration Wizard will analyze your source database for compatibility issues and allow you to fully or partially migrate your database schema and data to SQL Azure.  SQL Azure Migration Wizard requires SQL 2008 R2 SP1.

 

 

After uploading your database to SQL Azure we can start comparing query performance between the on-premise database and the SQL Azure database. Take into account that latency between your test load application and the SQL Azure database should be minimized. This can be done in 2 ways:

• Use queries for which the result set is minimal. For instance  SELECT COUNT(*) FROM TABLEX will result in one number. This is a minimum number of bytes to transfer to the client. SELECT * FROM TABLEY will result in a lot of data transfer from SQL Azure server to the client.
• Execute queries from a Azure worker role which is hosted in the same data center as your SQL Azure server.

Record with SQL profiler some queries from your on premise solution. Store these queries in a SQL script file. In this SQL script file add next command before every query.

 

PRINT 'Query: Cashflow entries to be allocated 1'

SET STATISTICS IO ON

SET STATISTICS TIME  ON

SELECT Columns FROM MYtable

Add next command after every query:
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
PRINT '----------------------------------------------------------------------'

The SQL Script will be executed in SQL Server Management Studio (SSMS) . Enable Include Client Statistics. (Shift-ALT-S)

Result of the query is printed on the Results tab in SSMS

The IO and Time statistics are printed on the message tab in SSMS

SET STATISTICS IO ON: Will generate  ‘SQL Profiler’ read statistics per query.
SET STATISTICS TIME ON: Will generate ‘SQL Profiler’ CPU Time and total elapsed query time.
The client statistics are printed on the Client Statistics tab.

To measure the total of all queries in one script add next command to the script.
DECLARE @STARTTIME DateTimeDECLARE @ENDTIME DateTime
SET @STARTTIME = GETDATE()
Query 1
Query 2
….
Query X
At the bottom of the script add next syntax

SET @ENDTIME = GETDATE()
SELECT GETDATE(),DATEDIFF (ms, @STARTTIME, @ENDTIME) AS QueryTime

After executing the script the last result set in the Results tab will display the execution time and exection time of the total script.

Now your script is READY for testing. Execute the script on:

1. The on premise database
2. SQL Azure database

Compare the results between the on premise results and the SQL Azure results.

In the Management Portal for SQL Azure you can get an overview of the query performance.

Thinks to take into account:

• Use only SELECT queries which enables you to redo test a lot of times on the SQL Azure database without the need to restore the database.
• If you plan to use INSERT, DELETE and UPDATE statements, you need to have a backup of your SQL Azure database.  Backup and Restore is not supported in SQL Azure at this moment but you can use the CREATE DATABASE  XXX AS COPY of YYY statement. This will create a copy of your database using a new database name.
  
  CREATE DATABASE destination_database_name
  AS COPY OF [source_server_name.]source_database_name
  
  To copy the Adventure Works database to the same server, I execute this:
  CREATE DATABASE [AdvetureWorksBackup]
  AS COPY OF [AdventureWorksLTAZ2008R2]

Observations:

• SQL Azure execute queries using one processor  (MAXDOP 1). Parallelism is not possible. 
• Dynamic Views in the manage portal contain history for a small period.  It’s difficult to see long running queries for a longer period. This happens because you will be connected to one of the 3 copies of your database.  You never know to which of the copies you will be directed. Every copy will have it’s own content in the DMV’s .
• Performance is not guaranteed on SQL Azure.
• In the tests I have executed so far, the SQL Azure database (8 GB Business Edition) is significant slower in comparison with a SQL database on my laptop. (DELL Latitude E6410).  One of the reasons is the single processor usage of SQL Azure.