none
Temp tables RRS feed

Answers

  • Yes, I meant such tables. I just want to know if too many such tables keep tempdb busy and degrade the performance of the db server.

    It's not that these temp tables are like small brownies and run around in SQL Server and do all sorts of naughtty things. No, they sit there perfectly still. And they are not really tables as I said. Only definitions of them. Nothing to be worried about.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, January 1, 2020 8:55 PM
  • The below must be a good read for you. You do not have to worry too much on the same.

    https://sqlperformance.com/2017/05/sql-performance/sql-server-temporary-object-caching


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
     [Blog]  [LinkedIn]

    • Marked as answer by Curendra Thursday, January 2, 2020 9:47 AM
    Thursday, January 2, 2020 4:07 AM

All replies

  • Why does tempdb has too many old temp tables? Also they cannot be dropped.

    You mean tables like #AB123456? They are cached definitions of temp tables in stored procedures. They are only definitions and do not take up much space.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, January 1, 2020 9:46 AM

  • You mean tables like #AB123456? They are cached definitions of temp tables in stored procedures. They are only definitions and do not take up much space.

    Yes, I meant such tables. I just want to know if too many such tables keep tempdb busy and degrade the performance of the db server.
    Wednesday, January 1, 2020 10:01 AM
  • It is very possible that those tables are cached,if you know some procedures that create tables within run the below test
    DECLARE @table_counter_before_test bigint;
    SELECT @table_counter_before_test = cntr_value
    FROM sys.dm_os_performance_counters 
    WHERE counter_name = 'Temp Tables Creation Rate';

    DECLARE @i int;
    SELECT @i = 0;
    WHILE (@i < 10)
    BEGIN
      -- <execute your stored procedure>
       SELECT @i = @i+1;
    END;

    DECLARE @table_counter_after_test bigint;
    SELECT @table_counter_after_test = cntr_value
    FROM sys.dm_os_performance_counters 
    WHERE counter_name = 'Temp Tables Creation Rate';

    PRINT 'Temp tables created during the test: ' +
           CONVERT(varchar(100), @table_counter_after_test 
                                 - @table_counter_before_test);

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, January 1, 2020 10:56 AM
    Answerer
  • Yes, I meant such tables. I just want to know if too many such tables keep tempdb busy and degrade the performance of the db server.

    It's not that these temp tables are like small brownies and run around in SQL Server and do all sorts of naughtty things. No, they sit there perfectly still. And they are not really tables as I said. Only definitions of them. Nothing to be worried about.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, January 1, 2020 8:55 PM
  • Hi Curendra,

    If the temp table is a local temporary table(#temp), it will be dropped automatically when the user ends their session.

    If the temp table is a local temporary table created in a stored procedure, it will be dropped when the stored procedure ends.

    And if the temp table is a global temporary table(##temp), it will be dropped when the session that created it ends and no other sessions reference it.

    In addition, here is a similar thread might help.

    Hope this could help you.

    Best Regards,

    Amelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 2, 2020 3:08 AM
  • Sometimes production server responds really slow and it becomes fast after restart. I thought it was due to accumulations in tempd and on restart tempdb got cleared.
    Thursday, January 2, 2020 4:06 AM
  • The below must be a good read for you. You do not have to worry too much on the same.

    https://sqlperformance.com/2017/05/sql-performance/sql-server-temporary-object-caching


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped.
     [Blog]  [LinkedIn]

    • Marked as answer by Curendra Thursday, January 2, 2020 9:47 AM
    Thursday, January 2, 2020 4:07 AM
  • Sometimes production server responds really slow and it becomes fast after restart. I thought it was due to accumulations in tempd and on restart tempdb got cleared.

    Or it becomes fast because after a restart you get better plans in the cache, since the entire cache is flushed when you restart SQL Server.

    There could be other reasons as well. To determine the reason for this behaviour closer investigation is needed. For instance sampling wait stats every ten minutes and saving the delta and compare waits before and after restart.

    I would not put my bets on the cache temp-table definitions.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Thursday, January 2, 2020 10:16 PM