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?