Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2004
    Location
    New Jersey
    Posts
    65

    Unanswered: [SQLS 2008] SP Fast When Run Direct, Slow As Job

    So I have a complicated SP that runs on SQL 2008 (64-bit). It queries data from SQL 2000 (32-bit) via a linked server. When I run the SP directly (EXEC proc_foo) in Management Studio on the server, it takes just under two minutes to complete. When I schedule a job, with one step, to run this same SP, the job just hangs. Last night, it took almost 4 hours to complete this job.

    In our test environment, it takes the same 2 minutes to run either direct or as a job. These are different servers, though - a 32-bit SQL 2008 linking to a development SQL 2000 32-bit.

    Why would a job that simply EXECs a single SP hang, when running the SP direct takes less than two minutes?
    Retired Moderator at Xtreme VB Talk, for all of your Visual Basic needs.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Reasons:

    1. The amount of data;
    2. Values distribution;
    3. Indexes and statistics differences, and their states.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Oct 2004
    Location
    New Jersey
    Posts
    65
    I'm not clear ... why would it be slow when executed via a job? A job itself doesn't affect the data handled by the SP being executed.
    Retired Moderator at Xtreme VB Talk, for all of your Visual Basic needs.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by HardCode View Post
    ...A job itself doesn't affect the data handled by the SP being executed.
    I didn't say it does or even can. In your original post you said:
    Quote Originally Posted by HardCode View Post
    In our test environment, it takes the same 2 minutes to run either direct or as a job.
    The 3 items I listed earlier directly respond to this situation. As per direct vs. through a job, - a different plan for one of the sections of your procedure may be selected, and again, - depending on the state of stats at the time of execution, and in addition to availability of an execution plan, along with the amount of data if you want to compare it to your test environment.

    But regardless of that, it's hard to tell you "why" without seeing your procedure, knowing a little about your data, and have a bit more detailed knowledge about your environment. Just stating "64-bit" doesn't really tell me much, other than the fact that your memory limitations may have a lot less impact on performance vs. 32-bit environment. But without knowing what is your memory I can't even be certain if edition even come to play here.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jan 2011
    Posts
    1

    plr seo ebook

    It seems you are having trouble trying to sell your books. The first piece of obvious advice I would like to give you, and most important is to - Upload a photo of yourself and set-up your profile. This is a critical step as other people want to see and talk to a REAL person.

    Secondly, there are more effective, and practical ways to advertise here on betternetworker.com and a boring sales pitch like that won't work. The good news for you is that there is TONS of excellent content on here and a bunch of great people that you can rely on when you need an extra hand.


    plr seo ebook

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •