I'm running an SP that works just fine until it has finished about 95% of its queries (inserting about 4.6 million rows in about 9 minutes), then it just sits there and grinds interminably, accomplishing nothing. It's running on a WIN2K, SQL2K box with dual processors and 4GB memory, with only a single instance of SQL active. The Task Manager shows the processors working at about 50% and memory usage at about 1.9GB, the same as when the SP is actually doing something. For the heck of it, I left the thing running overnight and after 16 hours it had still done nothing so I cancelled it. Then from the Query Analyzer I isolated and ran the query that the SP should have been running next and it executed in about 2 seconds, inserting 5000 rows with no problem.

The input data batch has 200K rows. Smaller batches of 100K or fewer rows run through the SP with no problem.

Has anyone experienced anything similar? Is there a memory leak problem or a memory management problem that might be causing this, or am I missing something?