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!

Usefull DMV's for SQL Azure to analyze if you miss SQL Profiler.

I have started a research project to look to the current possibilities of SQL Azure Reporting Services. One of the key areas I will focus on is the performance of SQL Azure Reporting services. To use SQL Azure Reporting Services you need to create a SQL Azure database and upload content to it. This can be done by making use of the Import and Export wizard.  After that I created my first SQL Azure Reporting Server report in SQL Server Business Intelligence Development Studio (BIDS) and deployed it to my SQL Azure Reporting server. To run the report, it took a 5 seconds to show the results. I was a little bit suprised why this report took 5 seconds to generate. Normally I use the SQL Profiler to analyze this performance issue. However, SQL Profiler is not available for SQL Azure. It is still a feature request on  mygreatWindowsAzureidea.com
with already 96 supporters for this idea.

Up till now, we need to do it with the available DMV's. More and more DMV's will come available in future releases. In this blogpost I will share some usefull queries on these DMV's which you can use to analyze the performance of your application on a SQL Azure database. In my situation the Azure Reporting Server.

Of course these queries can't replace the powerfull features of SQL Profiler, but at least it will help you and it is better than nothing.

--1)  Last executed queries with used query plan.

SELECT TOP 5 query_plan,q2.[text],
  (total_logical_reads/execution_count) AS avg_logical_reads,
  (total_logical_writes/execution_count) AS avg_logical_writes,
  (total_physical_reads/execution_count) AS avg_phys_reads,
  execution_count,
  (total_elapsed_time/execution_count) AS avg_Duration,
  last_execution_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
CROSS APPLY sys.dm_exec_sql_text(Sql_handle) AS q2
ORDER BY last_execution_time DESC

In the column Query_Plan you can click on the hyperlink.

This will show the query plan which was used during Query execution.

-- 2) Running queries.
SELECT q2.[text],database_id, user_id,session_id,
  transaction_id,status,start_time
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(Sql_handle) AS q2

-- 3) Blocking queries
SELECT q2.[text],session_id, blocking_Session_id,database_id, user_id,transaction_id
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(Sql_handle) AS q2
WHERE blocking_session_id <> 0 AND Blocking_Session_ID <> Session_ID

-- 4) Queries generating the most IO in SQL Azure.
SELECT TOP 5 query_plan,q2.[text],
  (total_logical_reads/execution_count) AS avg_logical_reads,
  (total_logical_writes/execution_count) AS avg_logical_writes,
  (total_physical_reads/execution_count) AS avg_phys_reads, execution_count,
  (total_elapsed_time/execution_count) AS avg_Duration,
last_execution_time
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
CROSS APPLY sys.dm_exec_sql_text(Sql_handle) AS q2
ORDER BY total_logical_reads DESC

-- 5) Enable Client Statistics in SSMS
By enabling Client Statistics in SSMS you can see the number of Bytes received from the server, the total execution time, Wait time on server replies.





Enjoy, using these DMV's till a good replacement is available for SQL Profiler on a SQL Azure database.

Tweet