SQL Server Temp Tables data query

1. Phenomena

Using cacti monitoring, there is a graph about the temporary table

You can see that the temporary table being used is very large in active temp tables, and is maintained at about 400 during non-working hours. It feels very strange, so trace it down!

2. Explore

First, verify that the cacti data is accurate and that the cacti data is known to be taken from the sys.dm_os_performance_counters counter DMV in SQL Server. So query the following data:

SELECT * from sys.dm_os_performance_counters where counter_name = ' Active Temp Tables '

There is no difference between the query results and the displayed data in the graph

Then, query what the current temporary tables are. Use the following sql:

 Use tempdb Go Select *  from  wherelike'#%'orderbyASC 

The query results are as follows:

The following conclusions can be drawn:

1) The number of temporary tables is basically the same as the number in the cacti chart

2) from the name of the temporary table, the basic table variable corresponding to the temporary table. Because if a temporary table is created, it is named #temp_xxxx_ random ID

3) Many temporary tables, which are basically table variables corresponding to the creation date of more than 10 days ago, and have not changed recently, but SQL Server has been to destroy

3. Unsolved Puzzles

According to the existing knowledge, the table variable will be released after the end of the batch statement, why is there such a temp tables not destroyed?

Please advise! Thank you ~