TSQL to get TOP 10 queries that are using I/O generation, SQL Server 2005

Published 14 July 08 03:26 AM | SQL Master 

DMVs are most helpful to find instant information on SQL instance without causing further delays, in any performance degradation time you can execute following TSQL to get top 10 queries that are generating lots of I/O on the server:

SELECT TOP 10
       total_logical_reads,
       total_logical_writes,
       execution_count,
       total_logical_reads+total_logical_writes AS [IO_total],
        st.text 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 [IO_total] DESC

Comments

# Other SQL Server Blogs around the Web said on July 14, 2008 3:43 AM:

DMVs are most helpful to find instant information on SQL instance without causing further delays, in

# SQL Server Transact-SQL (SSQA.net) : TSQL to get TOP 10 queries that are using I/O generation, SQL Server 2005 said on July 14, 2008 4:53 AM:

PingBack from http://sqlserver-qa.net/blogs/t-sql/archive/2008/07/14/4581.aspx

Anonymous comments are disabled

About SQL Master

**__________________________________** SQL Server MVP, Sr. DBA & industry expert. - Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.

Search

Go

This Blog

«July 2008»
SMTWTFS
293012345
6789101112
13141516171819
20212223242526
272829303112
3456789

Syndication