I am testing the behavior of a SELECT statement that returns 170,000,000 rows using SQLCMD. My goal is to cut out the time spent receiving the result-set on the client end (the time the process shows the ASYNC_NETWORK_IO waittype while an output file is being written to disk). I would like to have SQLCMD run my SELECT statement but not actually save or output the result-set. Just returning the number of rows affected would be fine. Is this possible?

sqlcmd -E -t 0 -S server\instance -d databaseName -i hugeselect.sql -o none?
  • 2
    -o nul: – Remus Rusanu Oct 22 '12 at 20:47
  • The above commment is the best solution. While this didn't completely eliminate the client time, it cut it down to being as fast as possible. – rayrayrayraydog Oct 23 '12 at 17:23

This isn't an answer for SQLCMD specifically (I don't know of an option to discard results), but an option for testing the performance of a query and ignoring the network overhead or client-side rendering is to use the free SentryOne Plan Explorer. By design we drop the results at the server and don't bring them across, allowing you to focus on the actual performance on the query rather than the performance of your network or the power of your desktop.

  • I'm using the free version of your tool already to examine query plans while I'm doing this. Are you describing something I could do with the Pro version specifically? – rayrayrayraydog Oct 22 '12 at 21:51
  • No, the free version does this. If you generate an actual execution plan from within the tool, it will return actual runtime metrics (duration, CPU, reads, etc.), but it does not return any query results. It discards those on purpose. – Aaron Bertrand Oct 22 '12 at 22:03

You could try piping it to nul, see if that helps, eg:

sqlcmd -E -t 0 -SyourServer -Q"SELECT * FROM sys.objects" > nul
  • Piping to nul won't accomplish what he is after. The result set will still go from the server to the client over the client. – Justin Dearing Oct 22 '12 at 22:19

Remus Rusanu had the best answer in his first comment.

sqlcmd -E -t 0 -S server\instance -d databaseName -i hugeselect.sql -o nul:

While this didn't completely eliminate the client time, it cut it down to being as fast as possible.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.