Spid hung on SQL2005 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Spid hung on SQL2005

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

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
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
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
Check the waittype of the hung SPID…
MohammedU.
Moderator
SQL-Server-Performance.com
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
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

]]>