Hide Select Output from T-SQL - Databases

TopAnswers Databases

Meta

Databases

TeX

Code Golf

APL

C++

.net

db<>fiddle

Java

*nix

PHP

PowerShell

Python

Rust

टेक्-मराठी

Typst

Web Client Dev

Web Server Dev

Hide Select Output from T-SQL

sql-server add tag

gilliam (imported from SE)

I'm trying to get query execution time, but I also want to hide the query output. I just want the elapsed time - no output.

### Example

    DECLARE @Start datetime
    DECLARE @End datetime
    
    SELECT @StartTimeWA=GETDATE() 
    
    SELECT 
    	   [id] 
          ,[database_id]
    	  ,[proc_name]
          ,[exec_t] from
      [DB].[dbo].[STAT] 
    
    SELECT @End=GETDATE()
    
    SELECT DATEDIFF(MS,@Start,@End) AS [Duration]

At the moment, I get query output *and* at the bottom my duration, which is the only thing I want as far as the output goes. I haven't been able to do this and wondering whether anyone else has come across similar problems? This is something I want to do in T-SQL not Management Studio or anything like that.

I'm trying to monitor the time it takes to run the select statement and report back to a server. I have an external monitor server which will run that every minute and get the time back (duration it took) which I will use over time to trend / baseline. As the current query spits out the select results and my duration it skews it, and my monitor server gets confused. I just wanted the duration column. I will also be doing this for inserts, which will be straightforward as it won't need to perform a select.

I'm trying to do this *purely in T-SQL*. I don't want to use DMVs as I want to get the time it takes (snapshot) when I run a query and check whether this changes over time when the server goes through the various levels of load as this will give me a good idea as to whether query execution time changes.

Top Answer

meme (imported from SE)

There are a lot of different ways to do this. 

I don't usually recommend inserting into a `#temp` table, since any tempdb load or autogrowth may impact the results, and I definitely don't recommend using a `@table` variable, since modifications to those are forced serial (no parallel plan can be used), which may change actual query times.


Variable Assignment
--

You can declare a variable and assign your columns to it, like this:

    DECLARE @Start datetime
    DECLARE @End datetime
    DECLARE @blob_eater SQL_VARIANT;
    
    SELECT @StartTimeWA=GETDATE() 
    
    SELECT 
           @blob_eater = [id] 
          ,@blob_eater = [database_id]
          ,@blob_eater = [proc_name]
          ,@blob_eater = [exec_t] from
      [DB].[dbo].[STAT] 
    
    SELECT @End=GETDATE()
    
    SELECT DATEDIFF(MS,@Start,@End) AS [Duration]

Though doing this may prevent some parameter embedding optimizations. See [Parameter Sniffing, Embedding, and the RECOMPILE Options][1] under "An Embedding Restriction".

Note that this method may trigger plan warnings about implicit conversions, but they're not the kind you have to worry about. See this Q&A for background: What Triggers This Warning: [Type Conversion in Expression May Affect “CardinalityEstimate” in Query Plan Choice][2].


In SSMS
--
You can change the settings to discard query results.

[![NUTS][3]][3]


SQL Query Stress
--
[SQL Query Stress][4] is an open source tool that allows you to run queries against a SQL Server to simulate load. No query results are returned to the application when they're run.

You can read some instructions on it [here][5].

ostress (RML Utilities)
--
[ostress][6] is a similar tool, published by Microsoft, which also doesn't return results to the client, unless you choose to do it.

I've written some about it [here][7].

Plan Explorer
--
SentryOne's [Plan Explorer][8] is a free alternative to view SQL Server execution plans and deadlocks with. 

You can also use it as a client to query SQL Serve to some degree:

[![NUTS][9]][9]

This will also discard results.

[![NUTS][10]][10]

Hope this helps!


  [1]: https://sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options
  [2]: https://dba.stackexchange.com/questions/231948/what-triggers-this-warning-type-conversion-in-expression-may-affect-cardinalit
  [3]: https://i.stack.imgur.com/ILrKc.png
  [4]: https://github.com/ErikEJ/SqlQueryStress
  [5]: https://www.brentozar.com/archive/2015/05/how-to-fake-load-tests-with-sqlquerystress/
  [6]: https://www.microsoft.com/en-us/download/details.aspx?id=4511
  [7]: https://www.brentozar.com/archive/2017/02/simulating-workload-ostress-agent-jobs/
  [8]: https://www.sentryone.com/plan-explorer
  [9]: https://i.stack.imgur.com/35GzY.png
  [10]: https://i.stack.imgur.com/ppl4f.png

1 Answer