[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Top Query for IO usage on SQL Server

Posted on 2014-07-14
Medium Priority
Last Modified: 2014-07-16
How to list out the top 10 queries for physical IO usage and shows its queyr plan on SQL 2008 ?

Question by:AXISHK
  • 4
  • 3
LVL 52

Expert Comment

by:Carl Tawn
ID: 40193928
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.

Author Comment

ID: 40194111

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

Expert Comment

by:Carl Tawn
ID: 40194118
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.
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  


Author Comment

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

LVL 52

Expert Comment

by:Carl Tawn
ID: 40198742
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.

Author Comment

ID: 40198869
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 ?
LVL 52

Accepted Solution

Carl Tawn earned 2000 total points
ID: 40198941
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.

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question