BUG: There is a decrease in performance when you frequently create and drop temporary tables in SQL Server 2000 (891553)

SYMPTOMS

Assume the following scenario: You try to perform a repeated operation in a single connection in Microsoft SQL Server 2000 by using the following procedure:

You create a temporary table.
You run an Insert statement to insert data into the temporary table by using the sp_executesql stored procedure.
You drop the temporary table.

In this scenario, performance times may steadily increase. Additionally, performance times continue to increase as long as the connection is not dropped. This problem does not occur if you use a permanent table.

Note This problem slows down only the current connection, and it occurs only in the global temporary tables.

WORKAROUND

To work around this problem, use one of the following methods:

Use a TRUNCATE statement instead of a CREATE statement and a DROP statement.
Use permanent tables instead of temporary tables.
Use an INSERT statement directly instead of calling the sp_executesql stored procedure.

Note The call to the sp_executesql stored procedure may not be coded in your application. However, the stored procedure may be added by the underlying database API. For example, the underlying database API may be ADO, OLE DB, or ODBC. You can use SQL Profiler to determine whether the sp_executesql stored procedure was sent to the computer that is running SQL Server.
Run the SQL script from an application. When you run the SQL script from an application, you can disconnect and then reconnect more frequently. Or, when you do this, you can wrap your code inside a stored procedure in SQL Server.

MORE INFORMATION

Steps to reproduce the problem

Click Start, point to All Programs, point to Microsoft SQL Server, and then click Query Analyzer.
Log on to the computer that is running SQL Server 2000 in SQL Query Analyzer.

Paste the following code into SQL Query Analyzer:

use pubs
go

set nocount on
declare @i int
set @i = 0
declare @tt as datetime
set @tt = getdate()

while @i < 100000
begin
	if @i % 10 = 0 
	begin
		if exists (select * from tempdb.dbo.sysobjects where id =object_id('tempdb.dbo.#T1'))
			drop table #T1
		CREATE TABLE #T1(Id int NULL)	
	end
	exec sp_executesql N'Insert into #T1 values(@P1)', N'@P1 int', @i
	select @i = @i + 1
	if @i % 10000 = 0 and @i > 0
	begin
		print datediff(ms,@tt,getdate())
		select @tt = getdate()
	end
end