The information in this article applies to:
SYMPTOMS
Output from SET SHOWPLAN ON reveals that an index (clustered or
non-clustered) on a temporary table created inside a stored procedure
is not used if the temporary table is queried from a nested procedure
(even using a search clause with a very high selectivity). For
example:
Note that this only happens if the outer stored procedure P1 is executed right after the two procedures are created. If the server is shut down and restarted and P1 is executed again, the index will be used from that point onward until the procedures are dropped and recreated. WORKAROUNDShut down and restart SQL Server if you run into the above situation. This means that those sites that shut down the server frequently will probably not see this problem. Another workaround is to replace the temporary table with a permanent table. STATUSMicrosoft has confirmed this to be a problem in SQL Server version 4.2 for OS/2 and Microsoft SQL Server version 4.2. We are researching this problem and will post more information here in the Microsoft Knowledge Base as it becomes available. Additional query words: Windows NT
Keywords : kbprg kbbug4.20 SSrvServer SSrvWinNT |
Last Reviewed: March 16, 1999 © 2000 Microsoft Corporation. All rights reserved. Terms of Use. |