I've noticed something which i'm not sure about. can it be that running queries directly with SSMS is much faster than running queries with SQLCMD? I've noticed that the results of queries that ran with SSMS are written much faster to the disk.

What are the differences?


  • Could you expand upon what you mean by the query is faster? The database engine itself does not care what client is connecting to it. What type of query are you running? If it's a select, what data types are you returning and how much data? What version and edition of SQL Server?
    – billinkc
    Aug 5, 2012 at 16:59

1 Answer 1


The differences are probably due to SET settings, such as SET ARITHABORT. What happens is that these settings are taken into account when deciding on the plan to use, and sometimes you can get stuck with a really bad plan with one setting (and this probably happened because that application used non-optimal parameters), and the opposite setting has a good plan.

You can discover what settings are in play by checking sys.dm_exec_requests and sys.dm_exec_sessions when the query is running. The latter has columns for various settings (but you'll have to figure out the session_id from the former). Once you've discovered the differences, you can manually set the setting to the "better" setting from sqlcmd, and hope that you now get the better plan. Or, you can simply issue WITH RECOMPILE to avoid the parameter sniffing issue in the first place.

A really useful resource to read thoroughly is:

Slow in the Application, Fast in SSMS?

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.