Hide Select Output from T-SQL












11















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.










share|improve this question





























    11















    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.










    share|improve this question



























      11












      11








      11


      2






      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.










      share|improve this question
















      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






      share|improve this question















      share|improve this question













      share|improve this question




      share|improve this question








      edited 2 days ago









      Paul White

      53.8k14286459




      53.8k14286459










      asked 2 days ago









      GilliamGilliam

      644




      644






















          2 Answers
          2






          active

          oldest

          votes


















          28














          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!






          share|improve this answer

































            0














            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);
            }






            share|improve this answer


























              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
              });


              }
              });














              draft saved

              draft discarded


















              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









              28














              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!






              share|improve this answer






























                28














                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!






                share|improve this answer




























                  28












                  28








                  28







                  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!






                  share|improve this answer















                  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!







                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited 2 days ago

























                  answered 2 days ago









                  Erik DarlingErik Darling

                  22.3k1269111




                  22.3k1269111

























                      0














                      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);
                      }






                      share|improve this answer






























                        0














                        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);
                        }






                        share|improve this answer




























                          0












                          0








                          0







                          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);
                          }






                          share|improve this answer















                          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);
                          }







                          share|improve this answer














                          share|improve this answer



                          share|improve this answer








                          edited 2 days ago









                          Paul White

                          53.8k14286459




                          53.8k14286459










                          answered 2 days ago









                          Evandro MuchinskiEvandro Muchinski

                          705111




                          705111






























                              draft saved

                              draft discarded




















































                              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.




                              draft saved


                              draft discarded














                              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





















































                              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







                              Popular posts from this blog

                              數位音樂下載

                              When can things happen in Etherscan, such as the picture below?

                              格利澤436b