Hide Select Output from T-SQLWhat Triggers This Warning: Type Conversion in Expression May Affect “CardinalityEstimate” in Query Plan ChoiceScript to query multiple instancesDuration Field in ProfilerAlter Time of DateTime FieldSql Profiler: what does Duration measure for exec sp_executeSQL Server Agent Jobs - Long running job delaying other jobs?Using GETDATE() in a variable returns 0 rowsTime taken to execute query seems longer than time measured in query…?The datediff function resulted in an overflowSQL Query: Print string (i.e. header) in output before returning table resultsSql Server - Execution Plan - Query Cost (relative to batch) calculation
Do I have a twin with permutated remainders?
Why is Collection not simply treated as Collection<?>
What about the virus in 12 Monkeys?
Why is the ratio of two extensive quantities always intensive?
Anagram holiday
Facing a paradox: Earnshaw's theorem in one dimension
If human space travel is limited by the G force vulnerability, is there a way to counter G forces?
Why doesn't H₄O²⁺ exist?
Decimal to roman python
intersection of two sorted vectors in C++
Why do bosons tend to occupy the same state?
Fully-Firstable Anagram Sets
Assassin's bullet with mercury
How to say in German "enjoying home comforts"
Watching something be piped to a file live with tail
How to blend text to background so it looks burned in paint.net?
How badly should I try to prevent a user from XSSing themselves?
What killed these X2 caps?
Is there an expression that means doing something right before you will need it rather than doing it in case you might need it?
What is the most common color to indicate the input-field is disabled?
Blender 2.8 I can't see vertices, edges or faces in edit mode
Dealing with conflict between co-workers for non-work-related issue affecting their work
Infinite Abelian subgroup of infinite non Abelian group example
Why can't we play rap on piano?
Hide Select Output from T-SQL
What Triggers This Warning: Type Conversion in Expression May Affect “CardinalityEstimate” in Query Plan ChoiceScript to query multiple instancesDuration Field in ProfilerAlter Time of DateTime FieldSql Profiler: what does Duration measure for exec sp_executeSQL Server Agent Jobs - Long running job delaying other jobs?Using GETDATE() in a variable returns 0 rowsTime taken to execute query seems longer than time measured in query…?The datediff function resulted in an overflowSQL Query: Print string (i.e. header) in output before returning table resultsSql Server - Execution Plan - Query Cost (relative to batch) calculation
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.
sql-server t-sql
add a comment |
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.
sql-server t-sql
add a comment |
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.
sql-server t-sql
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.
sql-server t-sql
sql-server t-sql
edited Mar 26 at 13:46
Paul White♦
54.1k14287460
54.1k14287460
asked Mar 26 at 11:13
GilliamGilliam
644
644
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
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.
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:
This will also discard results.
Hope this helps!
add a comment |
You could execute your query with SET STATISTICS TIME ON
and capture the output message by using your monitoring app on your external monitor server.
A way to capture the output message with .Net is explained in this Stack Overflow answer by AdaTheDev:
You can do this by adding an event handler to the InfoMessage event on the connection.
myConnection.InfoMessage += new SqlInfoMessageEventHandler(myConnection_InfoMessage);
void myConnection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
myStringBuilderDefinedAsClassVariable.AppendLine(e.Message);
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f233124%2fhide-select-output-from-t-sql%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
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:
This will also discard results.
Hope this helps!
add a comment |
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.
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:
This will also discard results.
Hope this helps!
add a comment |
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.
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:
This will also discard results.
Hope this helps!
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.
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:
This will also discard results.
Hope this helps!
edited Mar 26 at 12:50
answered Mar 26 at 12:45
Erik DarlingErik Darling
22.7k1269113
22.7k1269113
add a comment |
add a comment |
You could execute your query with SET STATISTICS TIME ON
and capture the output message by using your monitoring app on your external monitor server.
A way to capture the output message with .Net is explained in this Stack Overflow answer by AdaTheDev:
You can do this by adding an event handler to the InfoMessage event on the connection.
myConnection.InfoMessage += new SqlInfoMessageEventHandler(myConnection_InfoMessage);
void myConnection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
myStringBuilderDefinedAsClassVariable.AppendLine(e.Message);
add a comment |
You could execute your query with SET STATISTICS TIME ON
and capture the output message by using your monitoring app on your external monitor server.
A way to capture the output message with .Net is explained in this Stack Overflow answer by AdaTheDev:
You can do this by adding an event handler to the InfoMessage event on the connection.
myConnection.InfoMessage += new SqlInfoMessageEventHandler(myConnection_InfoMessage);
void myConnection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
myStringBuilderDefinedAsClassVariable.AppendLine(e.Message);
add a comment |
You could execute your query with SET STATISTICS TIME ON
and capture the output message by using your monitoring app on your external monitor server.
A way to capture the output message with .Net is explained in this Stack Overflow answer by AdaTheDev:
You can do this by adding an event handler to the InfoMessage event on the connection.
myConnection.InfoMessage += new SqlInfoMessageEventHandler(myConnection_InfoMessage);
void myConnection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
myStringBuilderDefinedAsClassVariable.AppendLine(e.Message);
You could execute your query with SET STATISTICS TIME ON
and capture the output message by using your monitoring app on your external monitor server.
A way to capture the output message with .Net is explained in this Stack Overflow answer by AdaTheDev:
You can do this by adding an event handler to the InfoMessage event on the connection.
myConnection.InfoMessage += new SqlInfoMessageEventHandler(myConnection_InfoMessage);
void myConnection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
myStringBuilderDefinedAsClassVariable.AppendLine(e.Message);
edited Mar 26 at 16:32
Paul White♦
54.1k14287460
54.1k14287460
answered Mar 26 at 16:19
Evandro MuchinskiEvandro Muchinski
705111
705111
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f233124%2fhide-select-output-from-t-sql%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown