Query performance comparison

Question

Post reply

Query performance comparison

as_1234

SSCrazy

Points: 2865
More actions
July 4, 2023 at 7:16 pm

#4226920

All,
I'm trying to compare two queries in terms of actual CPU and IO usage.
In terms of IO the nearest I can find is set statistics IO on. However that only gives me data about reads and not writes. Is there a way to capture the amount of writes or in general a better way to capture I/O values? I'm not acually interested in which tables the IO is for, just a total comparison value.
In terms of time I can get the actual execution time from the actual execution plan but that's subjective to what else is happening on the system at the time. I think the nearest I can get is the estimated CPU cost in the actual execution plan as that is based on set values and therefore not affected by the variability of other queries/procesesses using the CPU?
If I can't get actual values I may need to use the estimates in the actual execution plan. A query plan will show me an estimated subtree cost. Is it possible to break that down into estimated I/O and estimated CPU without checking each sub node individually?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

DesNorton SSC-Insane Points: 24421 More actions · Answer 1

Take a look at this article from Phil Factor

Testing the Performance of Individual SQL Statements within a Batch using SQL Prompt

It has sample extended events snippets for testing batch queries as well as stored procedures.

How to post data/code on a forum to get the best help.

Jeff Moden SSC Guru Points: 1004421 More actions · Answer 2

This will make some people get all scratchy but I use SQL Profiler... usually with a SPID filter.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18377 More actions · Answer 3

If you use:

SET STATISTICS IO, TIME ON

you will also get the CPU usage for the query.

Jeff Moden SSC Guru Points: 1004421 More actions · Answer 4

Also, never use SET STATSTICS if the code involves ANY Scalar UDF. It'll make the code look a serious amount slower than it actually is. Please see the following article for proof of that.

https://www.sqlservercentral.com/articles/how-to-make-scalar-udfs-run-faster-sql-spackle

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

as_1234 SSCrazy Points: 2865 More actions · Answer 5

All,

Thank you for your help.

I was able to capture all the information I need using Extended Events.