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.