Top Query for IO usage on SQL Server

Try something like:

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_physical_reads, qs.last_physical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_physical_reads DESC

Select allOpen in new window

Bear in mind that if the table is cached, then physical reads will be 0.

Tks.

For performance study, which way should I review, CPU (worst CPU)  , I/O (long wait IO)..  ?

It depends on what the query is doing, and you also have to take into account what other workloads may be running on the server at the same time.

You really need to be looking at those numbers in tandem with a specific query plan to be able to identify any potential areas for improvement.

High CPU could indicate an excessive load on the server in general, or it could represent excessive calculations, or function calls, within the query itself.

Same for IO. It could be high because of a bottleneck in the disk controller, or it could be caused by excessive reads due to a lack of RAM (causing excessive paging).

The query plan itself should indicate which area is likely to be the problem. You can then use the values from the DMV to gauge if any changes you make actually improve anything.

For your query, what's the different between total_physical_reads & total_logical_reads ? What's the execution_count ?

Tks

Physical reads is the number of pages that had to be read from disk into memory prior to querying. Logical reads is the number of pages that were already in cache, and therefore didn't need to be read from disk.

Execution_count is the number of times the query has been executed since the server was last restarted.

Tks. Is there a way to clean up these data in the morning without reboot the server such that I can retrieve the value for a day ?