Top Query for IO usage on SQL Server

How to list out the top 10 queries for physical IO usage and shows its queyr plan on SQL 2008 ?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Carl TawnSystems and Integration DeveloperCommented:
Try something like:
SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.total_physical_reads, qs.last_physical_reads,
qs.total_logical_writes, qs.last_logical_writes,
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

Open in new window

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

For performance study, which way should I review, CPU (worst CPU)  , I/O (long wait IO)..  ?
Carl TawnSystems and Integration DeveloperCommented:
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.
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

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

Carl TawnSystems and Integration DeveloperCommented:
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.
AXISHKAuthor Commented:
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 ?
Carl TawnSystems and Integration DeveloperCommented:
You can reset them by doing a DBCC FREEPROCCACHE. But you want to be careful about doing this on a production system as it will cause a temporary performance hit as it will cause all queries/stored procs to be recompiled on next execution.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.