Azure SQL Top IO Queries

Azure SQL provides a library of dynamic management views that can offer insight into the performance of SQL queries behind the scenes.

The following query will extract details of all queries with cached plans and summarises the IO load for the top 10 queries:

--Top Queries by Total IO
select top 10 rank() over (order by total_logical_reads+total_logical_writes desc,sql_handle,statement_start_offset ) as row_no
			,       last_execution_time
			,       (total_worker_time+0.0)/1000 as total_worker_time
			,       (total_worker_time+0.0)/(execution_count*1000) as [AvgCPUTime]
			,       total_logical_reads as [LogicalReads]
			,       total_logical_writes as [LogicalWrites]
			,       execution_count
			,       total_logical_reads+total_logical_writes as [Aggregate IO]
			,       (total_logical_reads+total_logical_writes)/(execution_count+0.0) as [Average IO]
			,       case when sql_handle IS NULL
							then ' '
							else ( substring(st.text,
											 (qs.statement_start_offset+2)/2,
											 (case when qs.statement_end_offset = -1        
												then len(convert(nvarchar(MAX),st.text))*2      
												else qs.statement_end_offset    
											  end - qs.statement_start_offset) /2  
											 ) 
								 )
					end as query_text 
			,       db_name(st.dbid) as database_name
			,       st.objectid as object_id
from sys.dm_exec_query_stats  qs
cross apply sys.dm_exec_sql_text(sql_handle) st
where total_logical_reads+total_logical_writes > 0 
order by [Aggregate IO]  desc

The Aggregate IO column in the following results is the total of all reads and write requests incurred by a query:

IO can be one of the slowest resources in a database environment and tuning your SQL queries and reviewing your indexing strategy can dramatically reduce IO costs and improve SQL query performance.

If you would kike to know more about SQL query performance talk to us about our SQL Performance & Tuning training.

We also offer SQL performance and tuning mentoring and consultancy services.