I've written a T-SQL routine that computes medians in a real estate application. When I execute the routine from Query Analyzer, it computes about 500 medians/sec for my circumstances. I want to include the routine as one step in a nightly scheduled job, but the code exceeds the size limitation for a single step in SQL Server's job scheduler. And re-formulating the routine to fit isn't feasible.
So, I turned the routine into a stored procedure which I intended to call from the appropriate job step. To my amazement, the 500 medians/sec slowed precipitously to about 35 medians/sec!! Terrible! Then when I executed the stored procedure from Query Analyzer, it ran at 500/sec?!
Does anyone know why the identical code run as a step from within a scheduled job would perform so much more poorly than when run from Query Analyzer?
Assuming precautions to be certain you were not just looking at cached vs. non-cached performance were taken, there may still be reasonable explanations (forced recompile, transient locks being waited on, etc.,), particularly if this is on a busy production server / DB. You may wish to schedule several runs within a minute or two and monitor the performance results, and / or investigate further, for example with profiler.