Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
richardps
Starting Member
33 Posts |
 Posted - 2007-03-13 : 12:34:02
|
| Here's an interesting one....1) A SQL Agent Job has a single step: exec sp_myproc2) sp_myproc has one line: xp_cmdshell "call mybatch.bat"3) mybatch.bat has one line: OSQL /Ssameserver mysql.sqlWhen I start the job it takes 30 minutes to execute.But if I simply run the mybatch.bat it runs in 15 minutes! Why?Answers on a postcard.... |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-03-13 : 13:46:53
|
| Network traffic?Difference between executing locally on server and executing locally on your client???Cache influencing the 2nd execution?Other activity on the server?I think you need to callup Profiler! |
 |
|
|
richardps
Starting Member
33 Posts |
Posted - 2007-03-14 : 09:42:37
|
| Thanks for your reply Andrew.Network Traffic - it's all on the same server as is the data being processed.Executing locally - my understanding is that OSQL executes entirely on the /S server referenced - which is again the same server.Cache - the executions are 30 days apartOther activity - There is none whatsoever (thought this might be it myself originally)Profiler - I think this won't help but by your mentioning it I think my personal theory is this:You have 1 SPID for the EXECUTE of the XP_CMDSHELLYou then have a 2nd SPID created by the OSQL within the Batch.SQL Server is monitoring these and splitting the CPU / IO time by 50% between these two SPIDs. Which is poor - so does this theory even sound plausible? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-03-14 : 09:48:41
|
| "does this theory even sound plausible?"Don't thinks so. If SPID-1 is "idle" (waiting for the XP_CMDSHELL task to finish) it will either not be given any access to the CPU until that happens, or it will forfeit each CPU time-slice [well, 99% of it we hope!] it is given because the event hasn't happened yetThe second of those "styles" is how I did mucking-about-multi-tasking in the 70's; I think its more likely that something more efficient, like my assumed first example, is happening in reality.So I expect the power-drain is elsewhere - memory trashing/restriction perhaps?SQL Profiler is the likely route to putting-your-finger on teh cause, I reckon.Kristen |
|
|
|
|
|
|
Execution Speed!