Fix stuck SQL Server stored procedure

Thursday, March 25, 2004

Here's the problem:

A fairly complicated (10 tables across three SQL Servers, several subqueries, and joins up the wazoo) stored procedure with a single parameter provides summary data for a critical company report. The sp will not complete for some parameters, but will for others. No errors, not crashing, just a huge CPU spike, and an interminable process. When working, the sp returns hundreds of records in 5-15 seconds, depending on the parameter (user). When not working, and allowed to run for hours, the sp returns only tens of records, essentially never completing.

Debugging, tracing, fiddling, and losing lots of sleep for three months yielded nothing, until one day the Microsoft tech working on the case comes up with a simple fix:


This trace switch forces SQL Server's query processor to run at SP2 level, ignoring any later hotfixes or service packs. Supposedly the SQL Server development team will fix this bug, but I don't count on it.

This command corrects only the current connection, so in order to make this fix effective for all executions, it needs to be added as a startup parameter:


And all will be well, at least if you're running into the same bug I was. If not, good luck, and see how far your $250 support charge at Microsoft will get you!

0 Comments

Comments are closed for this article.