SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TempDB questions


TempDB questions

Author
Message
drew.georgopulos
drew.georgopulos
Mr or Mrs. 500
Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)

Group: General Forum Members
Points: 531 Visits: 705
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
bangsql
bangsql
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 663
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.
ScottPletcher
ScottPletcher
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8134 Visits: 7163
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)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
ScottPletcher
ScottPletcher
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8134 Visits: 7163
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)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
GilaMonster
GilaMonster
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89831 Visits: 45284
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


drew.georgopulos
drew.georgopulos
Mr or Mrs. 500
Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)Mr or Mrs. 500 (531 reputation)

Group: General Forum Members
Points: 531 Visits: 705
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
Alexander Suprun
Alexander Suprun
Mr or Mrs. 500
Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)

Group: General Forum Members
Points: 523 Visits: 1516
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
ScottPletcher
ScottPletcher
SSCrazy Eights
SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)SSCrazy Eights (8.1K reputation)

Group: General Forum Members
Points: 8134 Visits: 7163
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)[size=2]Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them.[/size]
Alexander Suprun
Alexander Suprun
Mr or Mrs. 500
Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)Mr or Mrs. 500 (523 reputation)

Group: General Forum Members
Points: 523 Visits: 1516
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)SSC Guru (89K reputation)

Group: General Forum Members
Points: 89831 Visits: 45284
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search