1

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 & 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.

Improve this question
0

2 Answers 2

Reset to default
1

You can get at that information in the tempdb by following the following solution:

reading temporary tables that aren't yours

You can get some general information regarding tempdb by following the following post:

DMV query to get at data stored in tempdb tables,...

Improve this answer
6

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.

Improve this answer
4
  • I dont think it is caused by our monitoring solution because that simply selects from sys.dm_os_performance_counters. At about 1 AM this morning all the temp tables were cleared and at about 6AM when the users started testing again the count started increasing.
    – Robert Brown
    Commented Nov 30, 2012 at 5:48
  • The temporary tables that you are seeing can be caused by just about anything that the users are doing. It could be stored procedures executing, table variables, temp tables in stored procedures, etc. Without knowing what's happening on the system this probably isn't something to worry about.
    – mrdenny
    Commented Nov 30, 2012 at 20:50
  • +1 Well explained. TempDB contains lots of information from various sources and the SQL database engine is going to start caching (RAM usage / Buffer Cache) as soon as the users start the application(s).
    – John K. N.
    Commented Dec 5, 2012 at 10:06
  • Thanks I will continue to monitor the number of tables, the app seems to have about 300-400 at any given time in our Live Environment. Will also look at dumping the temp table info as indicated below.
    – Robert Brown
    Commented Dec 5, 2012 at 11:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.