First off, let me say that I'm a programmer not the DBA so please excuse any obvious stupidity. We have a moderately sized SQL2000 (SP4) environment (120GB) with a dozen databases the largest being about 30GB. All the MDF files are on our on one large SAN connected to the Server (Win2003) using fiber.
We are experiencing some serious performance issues. I don't know if this is related, but I have noticed something I can't explain. When checking for SQL process locks (using SQL Enterprise Manager) I often find something like:
spid 57 (Blocked By 57)
spid 57 (Blocking)
This can happen to almost any process. Almost everything in our environment runs as a stored proc and I know there isn't one or even a handful of procs that can exhibit this problem. Almost any proc appears exihibit this condition at one time or another. Most of our procs are written to run so quickly that a casual observer wouldn't notice the blocking. but it still happens occassionally to most procs.
The only commonality i can find is that all of the offending procs seem to cause some activity in tempdb as tempdb is usually listed in the objects associated with the process. I'm stumped. What the heck is going on here? Could this be because everything is on the SAN and we're I/O bound? Even if that's the case, how can a process block itself?