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
No comments:
Post a Comment