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.
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] 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]. In SSMS -- You can change the settings to discard query results. [![NUTS]] SQL Query Stress -- [SQL Query Stress] 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]. ostress (RML Utilities) -- [ostress] 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]. Plan Explorer -- SentryOne's [Plan Explorer] 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]] This will also discard results. [![NUTS]] Hope this helps! : https://sqlperformance.com/2013/08/t-sql-queries/parameter-sniffing-embedding-and-the-recompile-options : https://dba.stackexchange.com/questions/231948/what-triggers-this-warning-type-conversion-in-expression-may-affect-cardinalit : https://i.stack.imgur.com/ILrKc.png : https://github.com/ErikEJ/SqlQueryStress : https://www.brentozar.com/archive/2015/05/how-to-fake-load-tests-with-sqlquerystress/ : https://www.microsoft.com/en-us/download/details.aspx?id=4511 : https://www.brentozar.com/archive/2017/02/simulating-workload-ostress-agent-jobs/ : https://www.sentryone.com/plan-explorer : https://i.stack.imgur.com/35GzY.png : https://i.stack.imgur.com/ppl4f.png