Query Profiling SQL Azure when using Entity Framework or Linq-to-SQL

One slight shortcoming in Microsoft’s SQL Azure (the SQL Server 2008 flavor that is hosted  in Microsoft’s cloud platform) is that users don’t have the trace privileges needed to use SQL Profiler to profile query and performance behavior. Fortunately there are other ways to extract performance data for individual queries; SQL Azure exposes IO statistics, timings, and execution plans in the same way as normal non-cloud editions of SQL Server.

I have recently done some testing against SQL Azure using my tools. Huagati DBML/EDMX Tools needed some minor adjustments to work against SQL Azure as outlined in a previous blog post: http://huagati.blogspot.com/2010/12/sql-azure-support-in-huagati-dbmledmx.html

Next up was testing the profiling / logging components for Entity Framework 4 and Linq-to-SQL in Huagati Query Profiler against SQL Azure. Fortunately I can announce that it works just fine; the techniques used by the Huagati Query Profiler’s logging components for capturing server-side timings, I/O statistics, query execution plans are all supported in SQL Azure. The log entries will look the same and the same performance data and filtering options that work against local SQL Server instances can be used against SQL Server in the cloud.

One interesting thing I noticed while testing is that the roundtrip times against the SQL Azure instance I am accessing is only 100-150ms above accessing a database on my local LAN. That is impressive considering I am in Thailand and the SQL Azure instance is in Singapore, and faster than what I have seen mentioned in some forums and blog posts from US SQL Azure users.

To get started, download and install the Huagati Query Profiler from http://huagati.com/l2sprofiler/ and take a look at these blog posts and/or the sample code that is installed together with the profiler:

Linq-to-SQL: http://huagati.blogspot.com/2009/06/profiling-linq-to-sql-applications.html
Entity Framework: http://huagati.blogspot.com/2010/06/entity-framework-support-in-huagati.html

Next, you can either try the sample projects that ship with the profiler against your own SQL Azure instance, or add profiling support to your own Entity Framework 4 or Linq-to-SQL based projects against a SQL Azure database.

SQL Azure support in Huagati DBML/EDMX Tools

Recently I have been asked by several users if Huagati DBML/EDMX Tools supports SQL Azure. Until now, the answer was no; SQL Azure has some limitations compared to normal SQL Server 2008/2005/2000. However, after the question was asked again in the support forum this morning I decided to take another look at it.

I came across two minor obstacles:

1) SQL Azure supports only a subset of SQL Server’s system views. The add-in rely on several of these to extract schema data, documentation, indexes etc from the database.

2) SQL Azure is technically not available from Thailand until August 2011, and being based in Thailand I couldn’t directly sign up for it.

The first one is simple to solve in code, if connecting to SQL Azure the add-in simply have to avoid using some SQL Server features such as extended properties until those are supported in SQL Azure. This means the documentation sync feature in the add-in will not be able to retrieve documentation information from SQL Azure, since SQL Azure can not store it.

The second one was solved by Microsoft Thailand’s developer evangelist, Khun Ekaraj, who was nice enough to set me up with a one month SQL Azure trial pass. That allowed me to validate that the add-in worked against SQL Azure with the necessary changes applied.

As of today’s release, version 2.12, SQL Azure is now supported from Huagati DBML/EDMX Tools when running under Visual Studio 2010.