none
Physical Temp Table

    Question

  • Hi All,

    We have SQL Server 2008 SP2 Database. In our database, we have a table which is a Physical Temporary Table. A process inserts huge records into this table, and deletes them after the process completes.

    We are experiencing huge performance bottleneck during Inserts and deletes on this table.

    Have you all, experienced this kind of table and improved the performance ? If so, please, share your knowledge.

    Thursday, April 03, 2014 12:42 AM

Answers

  • The way your question is phrased, it's pointless to discuss indexes etc. So I guess your question if you can put the table elsewhere for better performance, and yes, there are two options.

    The first option is available to you today. You make the table into a global temp table. You create the table from a stored procedure which mark as a startup procedure. This way the temp table will be around until the server is shut down. This will improve performance, since one of your problems today is that all operations in the table are fully logged like everything else in the user database. There are several optimizations to logging in tempdb, since there is no need to recover tempdb on startup.

    Note that this solution does not require you to change any code, as you can define a synonym that points to the global temp table.

    The other option requires an upgrade to SQL 2014 Enterprise Edition so that you can use the new feature of memory-optimized tables. Such tables can be created with durability = SCHEMA_ONLY, and operations to such tables incurs basically no disk I/O at all.

    All this said, evaluating execution plans and making sure that you have the correct indexes on the table is essential.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, April 03, 2014 7:22 AM

All replies

  • I can only guess what you mean by a "physical temp table".  Is that just a regular table in your user database, used to store temporary data?  Perhaps we can offer suggestions on how to improve performance if you provide some details about the process that inserts and deletes from this table.  I would expect some selects to be involved too.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Thursday, April 03, 2014 12:49 AM
  • Yes, it is a regular table, used to store data temporarily.

    Thursday, April 03, 2014 1:11 AM
  • You may look at the query to insert data to your table. You can show us your code and execution plan for the same. If you are deleting all your data, then you can even consider TRUNCATE table option. TRUNCATE would be faster than DELETE, having said, depends on your business.
    Thursday, April 03, 2014 1:15 AM
  • We can't use the Truncate, since various process with the process_id inserts into the table and deletes them.
    Thursday, April 03, 2014 1:17 AM
  • Hi, One of the popular practices is small batch processing of inserts and deletes. If transactions involved then pls Keep transaction as short as possible.

    Please provide few specifics to think further.

    Thursday, April 03, 2014 1:41 AM
  • We can't use the Truncate, since various process with the process_id inserts into the table and deletes them.

    Is the clustered index on process_id, ad perhaps some other column?  If not, that might help.  The actual CREATE TABLE statement and sample queries will help us help you.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Thursday, April 03, 2014 2:11 AM
  • The way your question is phrased, it's pointless to discuss indexes etc. So I guess your question if you can put the table elsewhere for better performance, and yes, there are two options.

    The first option is available to you today. You make the table into a global temp table. You create the table from a stored procedure which mark as a startup procedure. This way the temp table will be around until the server is shut down. This will improve performance, since one of your problems today is that all operations in the table are fully logged like everything else in the user database. There are several optimizations to logging in tempdb, since there is no need to recover tempdb on startup.

    Note that this solution does not require you to change any code, as you can define a synonym that points to the global temp table.

    The other option requires an upgrade to SQL 2014 Enterprise Edition so that you can use the new feature of memory-optimized tables. Such tables can be created with durability = SCHEMA_ONLY, and operations to such tables incurs basically no disk I/O at all.

    All this said, evaluating execution plans and making sure that you have the correct indexes on the table is essential.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, April 03, 2014 7:22 AM