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() 
          ,[exec_t] from
    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() 
           @blob_eater = [id] 
          ,@blob_eater = [database_id]
          ,@blob_eater = [proc_name]
          ,@blob_eater = [exec_t] from
    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].

You can change the settings to discard query results.


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:


This will also discard results.


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

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.