Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

TempDB questions Expand / Collapse
Author
Message
Posted Thursday, February 21, 2013 9:19 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 11:44 AM
Points: 349, Visits: 615
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
Post #1422668
Posted Thursday, February 21, 2013 9:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 49, Visits: 572
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.
Post #1422672
Posted Thursday, February 21, 2013 9:59 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:12 AM
Points: 1,970, Visits: 2,909
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1422683
Posted Thursday, February 21, 2013 10:02 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:12 AM
Points: 1,970, Visits: 2,909
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1422684
Posted Thursday, February 21, 2013 10:43 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:54 AM
Points: 42,466, Visits: 35,530
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 2008, MVP
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

Post #1422717
Posted Thursday, February 21, 2013 11:57 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 11:44 AM
Points: 349, Visits: 615
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
Post #1422736
Posted Thursday, February 21, 2013 2:42 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 12:28 PM
Points: 185, Visits: 918
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
Post #1422798
Posted Thursday, February 21, 2013 3:12 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 10:12 AM
Points: 1,970, Visits: 2,909
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)
"In America, every man is innocent until proven broke!" Brant Parker
Post #1422809
Posted Thursday, February 21, 2013 4:10 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 12:28 PM
Points: 185, Visits: 918
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
Post #1422824
Posted Friday, February 22, 2013 3:12 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:54 AM
Points: 42,466, Visits: 35,530
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 2008, MVP
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

Post #1422947
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse