I ran across something today I have never encountered in SQL Server 2000; and thought I would share it with the poor souls unfortunate enough to read my blog. We had a process at work that was taking several hours to run (mainly because the stored procedures are written like crap), and about 5 days ago just stopped working. It would just run for 3-5 hours and then stop, producing an error saying it was locked by another process.
There were absolutely no other processes running on this server to “lock” the process, including the process it said was locking it. I set up a profiler, turned on Performance Monitor, ran integrity checks, recompiled the stored procedure, reindexed the table, and hoped it would go away. IT DIDN'T. How incredibly vain and stupid of me to think it would. :)
I tried everything I could think of to solve the problem and accomplished minus 5 things. The SQL Server developer rewrote a copy of the proc to use a copy of the table he created and it ran in under 15 minutes. Sooooo, I figured I would drop the table, recreate it, and test the procedure. When I went to drop the table, it wouldn't drop. The drop ran for 30 minutes and was the ONLY process running consistently during that time. The only way I could get the table to drop was to restart the SQL Server and drop it. Earlier in the week, I got the process to start working by restarting SQL Server also.
On my last badly frayed nerve, I recreated the table and tested the procedure again. The process now runs consistently around 15-20 minutes. Before it laid over and died it was taking around 1.5-2.0 hours with nothing unusual showing up in profiler except the millions of reads the inefficient code produces.
Any great ideas? Beats me!!!!!!!!!!!!!
Now onto the task of rewriting the stored procedure. It was written by an analytics group. Why is it these people never analyze their code and realize it's a horrible, smelly, disgusting pile of
i
n
e eeh eeeeh eeeeeh
effffffffff
ish ish iiiish iiiiiiiiish iiiiiiiiiiiii
i
ii
iii