BUG: Stored Proc Keeping Extra Execution Plans in Proc Cache

• Microsoft SQL Server version 4.2 for OS/2

SYMPTOMS

If you create a stored procedure in a particular case, that is, "Create proc UPPERCASE, MiXeD cAsE or lowercase," and do not call it in the exact same case, SQL Server will create a new execution plan in the procedure cache.

CAUSE

When you call a procedure, its name gets passed to a function, which builds a numeric representation of the procedure based on the ASCII value of its name. It then looks in the procedure cache to see if there is already another procedure in cache of the same numeric value. If there is no execution plan based on this comparison, the function then goes to sysprocedures, gets the query tree and builds a new plan.

These plans are retained in the procedure cache and are only cleared from the procedure cache by dropping and recreating the procedure or shutting down SQL Server. An indication that this problem has been encountered is that large numbers of execution plans for a given procedure will be shown when doing a DBCC memusage. The server may slow down after many procedures are called with the incorrect case. This is due to the procedure cache being used up by the extra execution plans in memory. Restarting the SQL Server will temporarily improve performance.

WORKAROUND

Call all stored procedures in the same case they are created.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.2 for OS/2. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.