How can I figure if a query is CPU-bound or I/O-bound?
-2
votes
1
answer
267
views
I have a query that runs regularly. It runs in under a second, but I consider it too slow and I would like to tune it. I can either monitor it as it runs live or copy the query text and run it myself. The query plan is a classic "[Wide, per-index updates](https://www.sql.kiwi/2013/01/optimizing-t-sql-queries-that-change-data/#1.-wide%2C-per-index-updates) " plan. It is almost identical to the below, with the only difference being that I have inserts rather than deletes.
I am sure that my bottleneck is during the index-modifying steps. It is either due to CPU usage (the indexes are

PAGE
compressed) or the reads required to insert into the indexes (they are billions of rows). But I would like to know which it is!
I can easily find the total time the query runs for as well as the time spent on CPU (sp_WhoIsActive
and SET STATISTICS TIME ON
both present this readily), but how can I specifically find the **time** spent on logical reads during this query? Ideally, I would specifically like to know the time spent on logical reads during the index modification steps.
SQL Server 2022. Compatibility level 140.
Asked by J. Mini
(1235 rep)
Dec 12, 2024, 07:45 PM
Last activity: Dec 24, 2024, 11:27 PM
Last activity: Dec 24, 2024, 11:27 PM