- 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.
- 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.
SELECT Columns FROM MYtable
Add next command after every query:
SET STATISTICS IO OFF
SET STATISTICS TIME OFF
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
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()
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:
- The on premise database
- SQL Azure database
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]
- 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.