Keep It Simple and Fast refers to my focus during development of applications. In my opinion, applications should be very simple to use and do not have dozens of options. Every application should perform very fast, even in high transactions volume or high multi user environments.

Tuesday, January 31, 2012

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 OFFSET STATISTICS TIME OFFPRINT '----------------------------------------------------------------------'

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 DateTimeSET @STARTTIME = GETDATE()Query 1Query 2….Query XAt the bottom of the script add next syntax

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.

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 .

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.

No comments:

About Me

My name is André van de Graaf, I'm working for Exact Software in the Research team as Principal Research Engineering. I'm located in Delft, The Netherlands. In my work i have a strong focus on performance of applications. Beside performance I want to keep everything as simple as possible. What is the perfect balance between performance and functionality?