Post reply

SP4 TSQL Issue

  • November 9, 2005 at 3:52 am

    #166886

    Hi,

    Not sure which group to put this under, but as it does involve SP4 this seems the obvious place.

    I have a job that 'cleans' data every night. This is added to on an infrequent basis. However the procedure now takes forever to run (10hrs and counting). Fortunately this is on one of our test environments....and the server is running SQL 2000 SP4.  However, on another test server (SP3) running the same procedure the time taken is about 15 minutes..which is about normal.

    My question is, does anyone know of any TSQL coding issues with SP4, do any procedures likely to need rewriting.

     

    Cheers...Graeme

  • November 9, 2005 at 6:35 am

    #602997

    1. Are the servers identical (hardware versions, software, etc. except for SP4) this is to include CPUs, memory, 3rd party software...
    2. When the processes run are the other processes similar i.e. no antivirus running, mem usage similar, code equal...
    3. Is the amount of data the same? 
    4. Is the data the same?
    5. ....



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • November 9, 2005 at 7:50 am

    #603019

    Yeah, the data is almost the same, the jobs run the same time on each server and the processes are virtually the same. The server with sp4 is a lower spec but certainly good enough to run the same code and data as the other server.

    The reason I ask is that the problem only started when a procedure was modified with a little bit extra code to clean some tables. This is the same on both servers and like i said the sp3 server runs the procedure in say 20 mins, the sp4 machine hasn't yet completed because it takes all night...and I have to stop it.

    This an error I've got.

    Intra-query parallelism caused your server command (process ID #51) to deadlock. Rerun the query without intra-query parallelism by using the query hint option (maxdop 1). [SQLSTATE 42000] (Error 8650).  The step failed.

    Any thoughts?

  • November 9, 2005 at 5:59 pm

    #603150

    Sounds like there is bad juju in the stored-proc.  can you post it so it can be eyeballed for possible areas of improvement?



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • November 9, 2005 at 8:05 pm

    #603156

    Hi,

    The databases were rebuilt yesterday and the jobs have been re-run. It looks like the procedure is now running normally ????....seems very odd.

    I'll keep an eye on that.

    Thanks for your time guys.

    Graeme

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply