SQL Server Performance

Spid hung on SQL2005

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by Moonwalker2000, Mar 13, 2007.

  1. Moonwalker2000 New Member

    Hello,
    Yesterday I ran a sp_who2 and viewed a job running with multiple spids each with 0 DiskIO and low CPUTime. This sat like this for hours as we killed other jobs running to see if it would start. We eventually killed the job and rebooted the server last night. The developer restarted the job (an ETL job running 4 tasks in parallel) but still to no avail. He redesigned the job to run 2 tasks in parallel and it started and finished. We've had other less complicated jobs hang also and restarting them seemed to fix the problem. No blocking or anything is happening.

    I'm like to be more proactive than just telling devs restart the job, by running some analysis on the spid to see what state it's in other than sp_who2.

    FYI: My MAXDOP = 16

    Any ideas where I start to try and get a view of what's going on?

    Thanks
    Patrick
  2. MohammedU New Member

    Are you using MAXDOP OPTION in your queries or your "max degree of parallelism" set to 16?

    How many physical CPUs you have? Is your server is HT enabled?


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  3. Moonwalker2000 New Member

    We have a 20 CPU server with "max degree of parallelism" set to 16 at the SQL side.

    64 Bit 1.5 GHz itanium. No HT.

    Thanks
  4. joechang New Member

    i am curious as to what your query is doing
    my tests 2 years ago showed SQL 2005 (beta 2) having excellent parallel op scaling to 16 procs

    it might be a good idea to leave mdop at 16,
    but put OPTION (MAXDOP x) on problem queries
  5. MohammedU New Member

    Check the waittype of the hung SPID...


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  6. Moonwalker2000 New Member

    Not exactly sure what the job / query was doing other than it had hung. We have very good parallelism on most jobs like queries, index builds etc. I just want to query the DMV#%92s or something to see what the status of the job was because it had hung.

    I'm looking into waittypes now and might have few questions on the subject.

    Thanks
  7. Moonwalker2000 New Member

    I have a large update running single threaded but it usually runs multi threaded. The job is taking a lot longer to finish today. SP_WHO2 is showing slow disk io for the job and it us usually in a suspended state but shows runnable every so often.

    The server is quite busy now with 2-3 large multi threaded report builder jobs running.

    Any reason why the update job is running single threaded and is there a way to estimate the time it will take to run in its current state?

    thanks

Share This Page