SQL 2008 RTM Temp Tables Today I implemented a monitoring solution on our SQL server using Cacti and the first thing i noticed is the growth in the number of temporary tables, in just 3 hours on our test system we had an additional 300+ temp tables in the temp DB. I also noticed that SQL Server's memory growth over these 3 hours was huge - 15 GB utilized after 3 hrs.

There were only 3-4 users testing on the test system and about 5 connections open to the application when I ran a SP_WHO.

What can be causing this? I did a query on the objects in the temp DB and all the names are similar to this #XXXXXXXXX, I read somewhere that these are from the Table Variable (Declare @myTable...)

Is there any way to check why we have a growth in the number of temp tables &amp; what is causing it? Any help at this stage would be greatly appreciated.

I am going to be installing the service packs tomorrow for 2008, hopefully that solves the problem.
Temp table usage is going to be totally up to your applications which have their databases on the SQL Server, or that are using the SQL Server.  For example the monitoring application itself could be using dozens of temp tables while it gathers up data.

As for the memory usage, SQL Server will by default use up as much memory as it needs to.  Every time is needs to modify data it loads that data into memory and then leaves that data in memory until the SQL Server needs the memory for something else.  In other words high memory usage isn't something that needs to be worried about as it is totally normal.  That said you'll want to cap the amount of memory that the SQL Server has access to so that it doesn't use all the memory on the server.