I recently upgraded a SQL 2000 server from a single Pentium III to dual Xeon processors. Data is accessed through ASP pages that run slower after the uprade than before, but only for certain pages. All other pages load much faster, except for the few that take several serial numbers from the user, and then checks them against a record set of valid serial numbers before inserting them into a table.
I have isolated the problem to a contention issue for the tempdb. The processors are waiting for a series of short writes, then longer reads on the temp db. It occurs in a cycle: processor utilization will spike, then there will be a short write to the temp db, then a longer read, then the cycle start over with a spike in processor utilization. Each cycle takes about three seconds and continues to cycle until the ASP script times out.
If I limit the SQL server to use only one processor, everything goes back to working the way it was before, (processor utiliztion stays at 100% for twenty seconds then the page loads, no contention for the tempdb) but we loose the goal of performance benefits through the upgrade.
If you choose the server properties, Processor - what is the minimum query plan threshold setting? Default is 5 seconds......
Sometimes if this is set too high, it will cause data to surge through the server.
Also , have you altered the server settings to be much different to the default settings? It sounds suspiciously like you have a resource locking problem, as tempdb is usually used for hashing work ( temp tables etc ) and tempdb cant be locked as such. However - putting incorrect locking hints in your code will cause "lockups" similar to what you describe ( especially table or database level locks ). What made me curious is: "but only for certain pages..." . Sounds like a locking issue.
However, I'm keeping an open mind.....
Also, is Tempdb & user database files ( .mdf & .ldf ) on the same drive?
Post back if still probs,
-- "No one expects the Spanish Inquisition"
-- Monty Python