TempDB questions

  • 1) I know temporary tables and table variables occupy space in TempDB, but what happens when I invoke a view; do views also make demands on TempDB?

    2) What is the natiure of the relationship between the number of processors (physical or virtual) and TempDB?

    Thanks very much

    drew

  • 1. any query uses group by , order by etc.. uses tempdb.

    so if view contains that tempdb will use

    2. no of physical cpu = no of tempdb files.

  • 1) I know temporary tables and table variables occupy space in TempDB, but what happens when I invoke a view; do views also make demands on TempDB?

    2) What is the natiure of the relationship between the number of processors (physical or virtual) and TempDB?

    1) That's not quite true. RAM is always used first to hold temp tables and table variables. But, if (and only if) RAM available for holding such rows is exceeded, then rows are physically stored in tempdb as necessary.

    Views are no different than tables in how they are ultimately processed. The query plan converts the view definition to point to its source table(s). So, yes, views may also require use of tempdb.

    2) There is no direct relationship per se. But for performance reasons, it's often best to increase the number of tempdb data files to match some proportion of the number of physical processors.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • bangsql (2/21/2013)


    1. any query uses group by , order by etc.. uses tempdb.

    so if view contains that tempdb will use

    2. no of physical cpu = no of tempdb files.

    RAM is also used first for grouping or sorting when possible. Only if RAM available to that query is insufficient is tempdb used to do grouping and sorting.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • bangsql (2/21/2013)


    2. no of physical cpu = no of tempdb files.

    http://www.sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%281230%29-tempdb-should-always-have-one-data-file-per-processor-core.aspx

    Temp tables and table variables are always in TempDB, they're both tables, they're both entered in the TempDB system tables, they're both allocated space in the TempDB database. They, however, may not be on disk. Allocated space in TempDB != written to the disk. They won't necessarily get written to disk unless there's memory pressure.

    Sorts and hashs will spill to TempDB when they can't get a large enough memory grant.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • God i love this site!

    Thank you bangsql, Scott and Gail, for your explanation, elucidation and especially for the link.

    i already feel 2% smarter <g>

    best

    drew

  • ScottPletcher (2/21/2013)


    RAM is always used first to hold temp tables and table variables. But, if (and only if) RAM available for holding such rows is exceeded, then rows are physically stored in tempdb as necessary.

    GilaMonster (2/21/2013)


    They, however, may not be on disk. Allocated space in TempDB != written to the disk. They won't necessarily get written to disk unless there's memory pressure.

    So if I have tempdb data/log files 1MB each, available RAM 1GB and I create 10MB temp table then my tempdb file is still gonna be 1MB? Something tells me it's totally not true.


    Alex Suprun

  • Alexander Suprun (2/21/2013)


    ScottPletcher (2/21/2013)


    RAM is always used first to hold temp tables and table variables. But, if (and only if) RAM available for holding such rows is exceeded, then rows are physically stored in tempdb as necessary.

    GilaMonster (2/21/2013)


    They, however, may not be on disk. Allocated space in TempDB != written to the disk. They won't necessarily get written to disk unless there's memory pressure.

    So if I have tempdb data/log files 1MB each, available RAM 1GB and I create 10MB temp table then my tempdb file is still gonna be 1MB? Something tells me it's totally not true.

    It depends. SQL won't necessary use all available memory just for your request. But if RAM is available for your task, up to its valid limit, SQL won't write it to disk. It makes sense really. Processing solely in memory is always vastly faster than going to disk.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • ScottPletcher (2/21/2013)


    Alexander Suprun (2/21/2013)


    ScottPletcher (2/21/2013)


    RAM is always used first to hold temp tables and table variables. But, if (and only if) RAM available for holding such rows is exceeded, then rows are physically stored in tempdb as necessary.

    GilaMonster (2/21/2013)


    They, however, may not be on disk. Allocated space in TempDB != written to the disk. They won't necessarily get written to disk unless there's memory pressure.

    So if I have tempdb data/log files 1MB each, available RAM 1GB and I create 10MB temp table then my tempdb file is still gonna be 1MB? Something tells me it's totally not true.

    It depends. SQL won't necessary use all available memory just for your request. But if RAM is available for your task, up to its valid limit, SQL won't write it to disk. It makes sense really. Processing solely in memory is always vastly faster than going to disk.

    I've asked a specific question: 10MB of memory is required for tempdb object and 1Gb is available, is it going to write to disk or not? What is the "valid limit"? If this "valid limit" is 1MB then there is not even a point of mentioning this feature because it would be completely useless in most cases.

    Even if something makes sense it doesn't necessarily mean that it's implemented this way.


    Alex Suprun

  • Alexander Suprun (2/21/2013)


    I've asked a specific question: 10MB of memory is required for tempdb object and 1Gb is available, is it going to write to disk or not?

    Maybe, maybe not. Depends what else is going on. I've done that (small table, lots of free memory) and had the temp table written to disk. Not written to disk and removed from memory, so access to that temp table was still fast and from memory, but the table was written to disk. Probably the lazy writer being pro-active.

    So if I have tempdb data/log files 1MB each, available RAM 1GB and I create 10MB temp table then my tempdb file is still gonna be 1MB? Something tells me it's totally not true.

    Indeed that's totally not true. The table would be allocated space in TempDB as I already explained, if there's not enough space in TempDB, the database would grow so that there would be space when/if the table needs to be written back, there's somewhere to write it.

    Bear in mind that it's not memory or disk, it's entirely possible for a table to be in both, written back to disk, but still in the buffer pool. In that case access is still fast, as it's in memory.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • I looked at this right at the end of this answer and in the specific test I did 250 pages seemed to be a magic number.

    I'm not claiming this will always be the case though.

  • Thanks, Gail. It makes much more sense now.


    Alex Suprun

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply