temp DB performance advice RRS feed

  • Question

  • HI,

    I am working a report, where in the result is combination of multiple #temp tables. Structure is as below

    1. Stored procedure 1 which has a temp table which gives a 0.5 million rows

    2. Stored procedure 2 which has a temp table which give 0.1 million rows

    Finally i need to combine the result set of above 2 SP , again use a temp table and make one final result set for report. Now i am worried about the performance, later if data increases, will it effect temp db. We usually stage the data monthly , in a month it Database may contain about 1 million rows. How much is the maximum capacity temp db accommodates. Will it effect with above approach.

    Thursday, June 11, 2015 4:22 AM


All replies

  • Thomas

    In order to speed up have a properly created indexes on the #temp table. Also please read this article


    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

    • Marked as answer by Eric__Zhang Monday, June 22, 2015 3:29 AM
    Thursday, June 11, 2015 5:23 AM
  •  The size and physical placement of the tempdb database can affect the performance of a system.  For optimal tempdb performance, set the recovery model of tempdb to SIMPLE. This model automatically reclaims log space to keep space requirements small. 

    To check the size and autogrowth setting, run the following script:

        name AS FileName, 
        size*1.0/128 AS FileSizeinMB,
        CASE max_size 
            WHEN 0 THEN 'Autogrowth is off.'
            WHEN -1 THEN 'Autogrowth is on.'
            ELSE 'Log file will grow to a maximum size of 2 TB.'
        growth AS 'GrowthValue',
        'GrowthIncrement' = 
                WHEN growth = 0 THEN 'Size is fixed and will not grow.'
                WHEN growth > 0 AND is_percent_growth = 0 
                    THEN 'Growth value is in 8-KB pages.'
                ELSE 'Growth value is a percentage.'
    FROM tempdb.sys.database_files;

    -- Please mark my post as an answer if I helped you to resolve the issue or vote up if it helped, thank you--

    Thursday, June 11, 2015 5:36 AM
  • In addition to Uri's response, I want to add one point that you need to take into consideration in the architecture of the solution. We dont have any information to recommend about architecture of the solution but: There is no GUI application that can show 500k rows in one screen (this is a limitation of the screen and our ability to read as if you want to show 500K rows in one screen the text will be too small to read even if the screen is 100*10 meter). Therefore, I assume that your application GUI do not show 500k in one screen and it uses some type of filter, OR DURING THE JOIN AND AFTER THE JOIN YOU FILTER THE DATA. In these cases I highly recommend to think about your solution architecture and try to filter the data BEFORE the join operation if you can. You might find that in some cases people can help you to reduce the need of the temp table and improve the query as well.

    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]

    • Edited by pituachMVP Thursday, June 11, 2015 6:15 AM
    Thursday, June 11, 2015 6:14 AM
  • I have few advice for you

    • I would not recommend you to have half million records in Temp table. Which is directly related to Temp db storage. You can truncate and reload tables every time you run your SP
    • If you would like to go with Temp table then do not create INDEXES just after you declare the table. It will slow down the performance. Do it in your CREATE TABLE syntax. This is because, SQL Server has a feature "Temp Table reuse"
    • If you are going with SQL table instead of Temporary table then create indexes on table and don't forget to update stats for those indexes.
    • For reporting, you can simply join two table (instead of temporary tables). 

    Note: Having large number of transaction in TempDb may consume all space and memory.. which may lead you to go with service restart.

    If you want to go with Temp table, then I presume that you have followed Temp db best practices which includes "enough size" and "constant growth"

    Hope this will help

    Glad to help! Please remember to accept the answer if you found it helpful. It will be useful for future readers having same issue.

    My Profile on Microsoft ASP.NET

    Thursday, June 11, 2015 6:33 AM
  •   For optimal tempdb performance, set the recovery model of tempdb to SIMPLE. This model automatically reclaims log space to keep space requirements small. -

    Tempdb Already has recovery model simple and YOU CANNOT change the recovery model of tempdb database.


    Temptables are almost like any other user table you can create index as per your need on Temp table. Its good that you are using temp tables for bigger calculation. Index would definately speed up the processing. Having said that you need to make sure you have enough space on tempdb and tempdb is on high speed disk

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Wiki Article


    Thursday, June 11, 2015 6:41 AM
  • Thanks Uri...It was a good and informative article

    Thanks All for your advice!!!

    Thursday, June 11, 2015 5:12 PM
  • Thanks everyone, each one`s advice helps a lot
    Thursday, June 11, 2015 5:12 PM
  • You should also look into the storage configuration of your server.

    If you're going to have significant operations done in tempDB, it'll become very important.

    Optimally, you want your logs on their own spindle, data files on their own spindle, tempDB on it's own spindle and tempDBLog on its own as well. These should be separate physical spindles, and not just different drives.

    For example:

    D:\ Data Files
    L:\ Logs
    T:\ TempDB
    S:\ TempDB Log

    Then, the sixe of your TempDB drive will dictate the amount of data you can pump into it before it will have to reclaim space.

    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

    Thursday, June 11, 2015 5:22 PM