Troubleshooting and optimizing queries on SQL Azure.

In one of my previous blogs I wrote about some usefull DMV's to analyze SQL Azure performance.  SQL Azure is a cloud based relational database with SQL Server 2008 engine at its core. In the first release of SQL Azure most useful DMVs have been disabled. As part of the scheduled Service Updates (SUs) to SQL Azure, these DMVs are enabled in phases. Since SQL Azure is a shared infrastructure model, the DMVs have to be modified to filter the output and show information only as appropriate. In this effort, the following DMVs have been enabled in the first phase. These DMVs being released typically require VIEW SERVER STATE permissions in an on-premise SQL Server. The new permission level required on SQL Azure would be VIEW DATABASE STATE to query these DMVs.

Transaction related DMVs

• sys.dm_tran_active_transactions - returns information about transactions for the SQL Azure server 
• sys.dm_tran_database_transactions - returns information about transactions at the user database level 
• sys.dm_tran_locks - returns information about currently active lock manager resources. Each row represents a currently active request to the lock manager for a lock that has been granted or is waiting to be granted. The columns in the result set are divided into two main groups: resource and request. The resource group describes the resource on which the lock request is being made, and the request group describes the lock request. 
• sys.dm_tran_session_transactions - returns correlation information for associated transactions and sessions.

Execution related DMVs

• sys.dm_exec_connections - returns information about the connections established to SQL Azure and the details of each connection. 
• sys.dm_exec_query_plan - returns the showplan in XML format for the batch specified by the plan handle. The plan specified by the plan handle can either be cached or currently executing. 
• sys.dm_exec_query_stats - returns aggregate performance statistics for cached query plans. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view. 
• sys.dm_exec_requests - returns information about each request that is executing within SQL Azure. 
• sys.dm_exec_sessions - returns one row per authenticated session on SQL Azure.
• sys.dm_exec_sql_text - Returns the text of the SQL batch that is identified by the specified sql_handle. This table-valued function replaces the system function fn_get_sql.
• sys.dm_exec_text_query_plan - returns the showplan in text format for a Transact-SQL batch or for a specific statement within the batch. The query plan specified by the plan handle can either be cached or currently executing. This table-valued function is similar to sys.dm_exec_query_plan (Transact-SQL), but has the following differences:  1) The output of the query plan is returned in text format. 2) The output of the query plan is not limited in size.

Database related DMVs

• sys.dm_db_partition_stats - returns page and row-count information for every partition in the current database.

As you can see, the number of DMVs is growing but unfortunaltely still no SQL Azure Profiler available. At this moment I got 204 votes for my idea for a SQL Azure Profiler on mygreatwindowsazureidea.com. So let's hope that a SQL Azure Profiler will come available in one of the coming Service Updates (SUs).

Enjoy it!