1

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

0

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.

|improve this answer|||||
0

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:

Discard Results

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:

enter image description here

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.

|improve this answer|||||
  • 1
    I understand Parameter sniffing. It is a stored procedure. I takes in a parameter and returns results based on it. It varies based on the value passed to the parameter. – jesijesi Nov 24 '19 at 9:43
  • Thank you for clarification. Please check if "Discard Results After Execution" could be useful in your case. – Learning_DBAdmin Nov 24 '19 at 9:50
0

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

enter image description here

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

enter image description here

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

enter image description here

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

enter image description here

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

|improve this answer|||||
  • based on test the server execution time includes the time it takes for the client to consume the results. – jesijesi Nov 24 '19 at 9:44
  • @jesijesi I beg to differ. From MS docs: "Displays the number of milliseconds required to parse, compile, and execute each statement." Don't see anything in there about consuming results. – Craig Efrein Nov 24 '19 at 10:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.