Query performance in Management Studio vs. Task Scheduler

• trigonom

  Mr or Mrs. 500

  Points: 523

  More actions

  January 8, 2014 at 8:54 am

  #396071

  Hello.

  We are running SQL Server 2008 on a Windows Server 2008 R2 SP1 box. I have a SQL query that reads through the database and writes output to a flat file.

  When I run the query directly in SQL Server Management Studio and choose Query --> Results to File, the query runs in a little less than 2 minutes.

  However, when I run the same query, writing to the same output file, but run the query in a batch job through Task Scheduler, it takes over 3 hours to run!

  The command in the batch file is:

  sqlcmd -s <SQL Server instance> -i <the name of the file that is the SQL query itself> -o <the name of the output file> -d <the name of the database> -h -1 -W -r 0

  Why does it take so much longer to run through the Task Scheduler? Are there parameters that I am missing and should be setting to increase the priority of the job or should I be attacking this in some other way? Any suggestions would be greatly appreciated.

  Thanks!

• ScottPletcher

  SSC Guru

  Points: 101192

  More actions

  January 8, 2014 at 4:03 pm

  #1678620

  My guess would be that somehow task schedule is using a batch size of 1 row (or some very small number of rows). See if you can change the batch size.

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".