Systems Engineering and RDBMS

Finding Top worst IO SQL statements

Posted by decipherinfosys on April 17, 2008

Here are two scripts – one for Oracle and the other one for SQL Server which show the top SQL statements that have the worst IO characteristics:

a) In the case of Oracle, the SQL goes after the V$SQLAREA view and presents the top SQL statements ordered in a descending order based on the addition of the buffer gets and disk reads.  It spools those into the topsql.lst file for a review.

b) In the case of SQL Server, the script presents the Average_IO, the query statement, the object name as well as the name of the database.

/************************************************************************************
Top SQL statements order by buffer gets and disk reads DESC order
*************************************************************************************/
set pagesize 60
set linesize 80
col sql_text word_wrap

spool topsql.lst
SELECT HASH_VALUE, BUFFER_GETS, DISK_READS, ROWS_PROCESSED, EXECUTIONS, SORTS,ADDRESS, A.SQL_TEXT
FROM V$SQLAREA A
WHERE BUFFER_GETS > 10000000
OR DISK_READS > 1000000
ORDER BY BUFFER_GETS + 100*DISK_READS DESC
/
spool off

/************************************************************************************
Top 10 statements by IO
*************************************************************************************/

SELECT TOP 10
(qs .total_logical_reads + qs.total_logical_writes) /qs.execution_count as AVERAGE_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)
as query_text,
db_name(qt.dbid) as Database_Name,
object_name(qt.objectid, qt.dbid) as Name_Of_Object
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text (qs. sql_handle) as qt
ORDER BY AVERAGE_IO DESC
GO

Sorry, the comment form is closed at this time.

 
%d bloggers like this: