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