locked
Space allocated in Temp drive RRS feed

  • Question

  • How much space is required in TempDB drive? Does it depend on the size of the datafile. Is there a formula I can use to calculate how much space I should allocate in TempDB drive?
    Tuesday, October 8, 2019 10:20 PM

Answers

  • Hello Friend,

    This kind of question is very difficult to answer.
    The ideal size depends.

    It will depend on your workload. If your base is new you will need to slowly build up. Start with a size that is relatively large. Usually around 10GB for data and 20GB for logging and changing as needed and common sense.

    Over time check the containment of TempdB files and change as needed.
    Your workload in the end along with the machine's capabilities is what they will define. Your RAM mostly.

    In addition to setting the size of TEMPDB, also split temp files by the number of logical processors on your server.

    To check the use of TEMPDB you can use the following query and validate the use.

    SELECT files.physical_name, files.name,
    stats.num_of_writes, (1.0 * stats.io_stall_write_ms / stats.num_of_writes) AS avg_write_stall_ms,
    stats.num_of_reads, (1.0 * stats.io_stall_read_ms / stats.num_of_reads) AS avg_read_stall_ms
    FROM sys.dm_io_virtual_file_stats(2, NULL) as stats
    INNER JOIN master.sys.master_files AS files
    ON stats.database_id = files.database_id
    AND stats.file_id = files.file_id
    WHERE files.type_desc = ‘ROWS’

    There are other tips like using RAID and fast disks for performance enhancement and 64kb block size to help.
    You can study a little about this through the official link:
    tempdb Database

    I hope this can help you.


    If this answer has helped you, mark it helpful so that someone else with a question or similar problem can find an answer or help more easily. Jefferson Clyton Pereira da Silva - [MCSA | MCP | MCTS | MTA | Database Analyst - Sql Server and Oracle]
    • Edited by Jefferson Silva DBA Wednesday, October 9, 2019 12:28 AM linguagem
    • Marked as answer by Avyayah Wednesday, October 9, 2019 8:46 PM
    Wednesday, October 9, 2019 12:27 AM
  • 1) Execute below on each user database to understand how much tempdb space will be required for integrity checks.

    dbcc checkdb WITH ESTIMATEONLY

    2) Size your Tempdb to be slightly more than the value you see in step 1.

    3) Monitor your Tempdb size with the production workload for some weeks/months and then you will have some idea.

    You may not know in some instances where an one-off query can bloat your tempdb. But above approach will be a starting point.


    Regards;
    Vivek Janakiraman
    ----------------------------
    My Blog
    My Linkedin Page
    ----------------------------

    • Marked as answer by Avyayah Wednesday, October 9, 2019 8:46 PM
    Wednesday, October 9, 2019 12:25 AM

All replies

  • 1) Execute below on each user database to understand how much tempdb space will be required for integrity checks.

    dbcc checkdb WITH ESTIMATEONLY

    2) Size your Tempdb to be slightly more than the value you see in step 1.

    3) Monitor your Tempdb size with the production workload for some weeks/months and then you will have some idea.

    You may not know in some instances where an one-off query can bloat your tempdb. But above approach will be a starting point.


    Regards;
    Vivek Janakiraman
    ----------------------------
    My Blog
    My Linkedin Page
    ----------------------------

    • Marked as answer by Avyayah Wednesday, October 9, 2019 8:46 PM
    Wednesday, October 9, 2019 12:25 AM
  • Hello Friend,

    This kind of question is very difficult to answer.
    The ideal size depends.

    It will depend on your workload. If your base is new you will need to slowly build up. Start with a size that is relatively large. Usually around 10GB for data and 20GB for logging and changing as needed and common sense.

    Over time check the containment of TempdB files and change as needed.
    Your workload in the end along with the machine's capabilities is what they will define. Your RAM mostly.

    In addition to setting the size of TEMPDB, also split temp files by the number of logical processors on your server.

    To check the use of TEMPDB you can use the following query and validate the use.

    SELECT files.physical_name, files.name,
    stats.num_of_writes, (1.0 * stats.io_stall_write_ms / stats.num_of_writes) AS avg_write_stall_ms,
    stats.num_of_reads, (1.0 * stats.io_stall_read_ms / stats.num_of_reads) AS avg_read_stall_ms
    FROM sys.dm_io_virtual_file_stats(2, NULL) as stats
    INNER JOIN master.sys.master_files AS files
    ON stats.database_id = files.database_id
    AND stats.file_id = files.file_id
    WHERE files.type_desc = ‘ROWS’

    There are other tips like using RAID and fast disks for performance enhancement and 64kb block size to help.
    You can study a little about this through the official link:
    tempdb Database

    I hope this can help you.


    If this answer has helped you, mark it helpful so that someone else with a question or similar problem can find an answer or help more easily. Jefferson Clyton Pereira da Silva - [MCSA | MCP | MCTS | MTA | Database Analyst - Sql Server and Oracle]
    • Edited by Jefferson Silva DBA Wednesday, October 9, 2019 12:28 AM linguagem
    • Marked as answer by Avyayah Wednesday, October 9, 2019 8:46 PM
    Wednesday, October 9, 2019 12:27 AM