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'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?

Thanks.

share|improve this question
    
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 '12 at 16:59

1 Answer 1

up vote 3 down vote accepted

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?

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.