Largest IO queries.
To improve query performance by creating an index is to avoid doing large amounts of IO for the query in question.
To find the largest IO queries, use the following script:
USE MASTER
--- top 50 statements by IO
SELECT TOP 50
(qs.total_logical_reads + qs.total_logical_writes) /qs.execution_count as [Avg IO],
substring (qt.text,qs.statement_start_offset/2,
(
case
when qs.statement_end_offset = -1
then
len(convert(nvarchar(max), qt.text)) * 2
else
qs.statement_end_offset
end
- qs.statement_start_offset
)/2+1)
as query_text,
qt.dbid,
qt.objectid
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text (qs.sql_handle) as qt
ORDER BY [Avg IO] DESC
Why do we use statement_start_offset and statement_end_offset?
creating a batch and running it:
DECLARE @aa INT;
SET @aa=1000
SELECT top 5 * FROM Person.Address
WHERE AddressID<500 size="2">DECLARE @aa INT; SET @aa=1000 SELECT top 5 * FROM Person.Address WHERE AddressID<500
USE AdventureWorks
GO
SELECT qs.sql_handle,
substring (qt.text,qs.statement_start_offset/2,
(case when qs.statement_end_offset = -1
then len(convert(nvarchar(max), qt.text)) * 2
else qs.statement_end_offset end - qs.statement_start_offset+1)/2)
as query_text
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY qs.sql_handle
It will catch the following:
SELECT top 5 * FROM Person.Address WHERE AddressID<50
Observation:
sys.dm_exec_sql_text( ) function returns the TEXT of the QUERY, including variable definitions. Using the statement_start_offset and statement_end_offset retrieves only the query statement.
What is the sql_handle?
For batches, the SQL handles are hash values based on the SQL text. For database objects such as stored procedures, triggers or functions, the SQL handles are derived from the database ID, object ID, and object number.
