Does high vol. of #tmp tbls(hundred over) effect CPU Memory

Question

Hi All I found there are hundred over #temp tables created in tempdb... #temp tables are created physically on disk... But will these high volume of #temp table effect CPU Memory? Regards Shehzad

Jignesh Trivedi · Answer

yes Please refer http://www.sql-server-performance.com/2012/sql-server-t-sql-tuning-temp-table-union/ hope this will help you.

Khan Abrar Ahmed · Answer

Yes instead of creating the temp table create table variable if you are not using the table in cross script.

Khan Abrar Ahmed · Answer

Yes but once execution is done then it will free the memory resources and you dont need to drop the table.

Shehzad · Answer

Hi Abrar / Jignesh Thanks for your reply...But my concern is CPU Memory utilization.... As per Articles i have read about table Variable it is partially created on Memory ' Is my understanding correct? Regards Shehzad

Jignesh Trivedi · Answer

hi yes agree with Khan Abrar Ahmed. but it automatically dropped by SQL server once it finish execution scope. We can also an DROP a temp table explicitly (after finish task simply drop it). Instead of Temp table you can use temp variable or CTE. hope this will help you.