January 11, 2010 at 6:14 am
Here's just a general question - I've found, somewhat unusually, that some jobs run slower as an agent job than from management studio query window.
I'm running everything in an rdp session, so no networks, it's sql 2008 ent on w2008 ent. x64,16 cores, 64gb ram, dedicated san.
To explain the history - I have a series of tests which run in a loop - well 20+ job steps which endlessly repeat - these are used to benchmark various setups and such. What I've discovered is that some tests run about 20% quicker from the query window.
Each test is run after a database restore ( same database each time ).
Wonder if anyone else has experienced this or have any plausible suggestions as to why.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 11, 2010 at 6:42 am
Can I ask if you have run Profiler traces to capture the execution plans?
It would be good to know that the plans are exactly the same when run from the Agent and from SSMS, the connection SET options are identical, the same account is used in both cases, and the server is in the same state (e.g. DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS are run before each run).
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 11, 2010 at 6:59 am
actually the job which shows the main difference is an index rebuild.
there are no clearing dbcc commands.
Process is restore database
checkpoint
and that's it.
other tests seem to show some slower execution, as these are run over several days constantly I'm pretty sure it's not just an odd difference but a consistent one, which is about face to waht I'd expect. Everything run as syasdmin/god as you might guess.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 11, 2010 at 7:10 am
Interesting. I'm going to run some tests on my home instances to see if I can replicate this.
Oddly, this is slightly reminiscent of something I half-remember from years ago when it turned out there was a good reason why a stored procedure would run faster/slower (forget which) from Query Analyzer/SSMS compared to running it from an application. Something related to TDS packets and stuff but I've never been able to re-find the details for that.
Anyway, I digress...off to set up a test.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply