February 21, 2013 at 9:19 am
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
February 21, 2013 at 9:22 am
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.
February 21, 2013 at 9:59 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 21, 2013 at 10:02 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 21, 2013 at 10:43 am
bangsql (2/21/2013)
2. no of physical cpu = no of tempdb files.
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
February 21, 2013 at 11:57 am
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
February 21, 2013 at 2:42 pm
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.
February 21, 2013 at 3:12 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 21, 2013 at 4:10 pm
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.
February 22, 2013 at 3:12 am
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
February 22, 2013 at 10:12 am
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.
February 22, 2013 at 10:24 am
Thanks, Gail. It makes much more sense now.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply