Execute a query but not return results to client

I am testing the performance a query and trying to fix a parameter sniffing issue. Based on the parameter sometimes it returns 3 rows and sometimes it returns over 5K rows. I want to test the impact of the change I am proposing on execution time. But since the number of rows returned is so wide the execution time is skewed as it takes into account the time taken by the client to consume the rows. I want to remove the time taken to return the rows and jut measure the actual execution time on the server.

I have looked at SET FMTONLY ON but it looks like it does not run the query.
I also tried dumping the rows into a table/temptable but that adds cost to the estimation and causes the plan to change in a direction that is not what I want.

Any other way we can achieve this?

Cheers

You can use SET STATISTICS TIME ON to get the exact amount of time the server spends executing the query.

And Client Statistics can help determine the amount of time the client spends consuming it.

From Brent Ozar:
https://www.brentozar.com/archive/2012/12/sql-server-management-studio-include-client-statistics-button/

Some really valuable information can be found here, such as the number
of INSERT, DELETE, UPDATE, and SELECT statements – think of how useful
that can be when tuning a complex stored procedure. You can see the
number of rows affected or returned, and the number of transactions
executed.

The network statistics lets you see how much traffic is moving from
your client to the server and back.

The time statistics tells you how much time was spent processing on
the client versus how much time was spent waiting for the server.
These figures are in milliseconds.

I’ve often used it as a way to compare stored procedure performance executed with different parameters, especially useful when changing those parameters affects the volume of data processed and returned.

From SQL Server Management Studio

Once selected, every execution using the same session, will add a new column with performance results for that execution.

Some more info here as well: https://blog.sqlauthority.com/2009/10/01/sql-server-sql-server-management-studio-and-client-statistics/

Test using sysjobhistory

use msdb
go

set statistics time on

select * from dbo.sysjobhistory
go

From message tab:

(18 rows affected)

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 3 ms.

From Client Statistics Tab

3 milliseconds to execute from SET STATISTICS TIME ON and 5 milliseconds Client processing time from the Client Statistics Tab.

Dan Guzman: I think you’ll need to consume the results in a SQLCLR proc on the server side. Although there will be some overhead, it will be less without the network IO.

Personally, I would include the time it takes to consume the results as the elapsed time and use other metrics like logical reads and CPU to measure resource usage.

If you want to suppress rows from the result set then, possibly you should try Discard Results After Execution at SSMS(SQL Server Management Studio) as shown below:

You can still enable statistics IO and time and can see number of pages read as well as time consumed while executing the query in question as below:

Similar question is discussed in below thread:

https://stackoverflow.com/questions/6386587/how-to-execute-sql-query-without-displaying-results

One more note here about SET FMTONLY ON that this should not be used and has been replaced by the following items:

• sp_describe_first_result_set (Transact-SQL)
• sp_describe_undeclared_parameters (Transact-SQL)
• sys.dm_exec_describe_first_result_set (Transact-SQL)
• sys.dm_exec_describe_first_result_set_for_object (Transact-SQL)

as described at MS site.

Hope above helps.