Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question
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

3 Answers 3

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 version of SQL Sentry 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.

Disclaimer: I work for SQL Sentry.

share|improve this answer
    
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
share|improve this answer
    
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
up vote 0 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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