InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 12:38:38 GMT20http://www.sqlservercentral.com/Forums/Topic1422668-391-1.aspxThanks, Gail. It makes much more sense now.Fri, 22 Feb 2013 10:24:35 GMTAlexander Suprunhttp://www.sqlservercentral.com/Forums/Topic1422668-391-1.aspxI looked at this right at the end of [url=http://dba.stackexchange.com/q/16385/3690]this answer[/url] 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.Fri, 22 Feb 2013 10:12:07 GMTMartin Smith-178018http://www.sqlservercentral.com/Forums/Topic1422668-391-1.aspx[quote][b]Alexander Suprun (2/21/2013)[/b][hr]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? [/quote]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.[quote]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.[/quote]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.Fri, 22 Feb 2013 03:12:31 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1422668-391-1.aspx[quote][b]ScottPletcher (2/21/2013)[/b][hr][quote][b]Alexander Suprun (2/21/2013)[/b][hr][quote][b]ScottPletcher (2/21/2013)[/b][hr]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.[/quote][quote][b]GilaMonster (2/21/2013)[/b][hr]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.[/quote] 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.[/quote]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.[/quote]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.Thu, 21 Feb 2013 16:10:45 GMTAlexander Suprunhttp://www.sqlservercentral.com/Forums/Topic1422668-391-1.aspx[quote][b]Alexander Suprun (2/21/2013)[/b][hr][quote][b]ScottPletcher (2/21/2013)[/b][hr]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.[/quote][quote][b]GilaMonster (2/21/2013)[/b][hr]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.[/quote] 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.[/quote]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.Thu, 21 Feb 2013 15:12:52 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1422668-391-1.aspx[quote][b]ScottPletcher (2/21/2013)[/b][hr]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.[/quote][quote][b]GilaMonster (2/21/2013)[/b][hr]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.[/quote] 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.Thu, 21 Feb 2013 14:42:28 GMTAlexander Suprunhttp://www.sqlservercentral.com/Forums/Topic1422668-391-1.aspxGod 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>bestdrewThu, 21 Feb 2013 11:57:19 GMTdrew.georgopuloshttp://www.sqlservercentral.com/Forums/Topic1422668-391-1.aspx[quote][b]bangsql (2/21/2013)[/b][hr]2. no of physical cpu = no of tempdb files.[/quote][url]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[/url]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.Thu, 21 Feb 2013 10:43:08 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1422668-391-1.aspx[quote][b]bangsql (2/21/2013)[/b][hr]1. any query uses group by , order by etc.. uses tempdb. so if view contains that tempdb will use2. no of physical cpu = no of tempdb files.[/quote]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.Thu, 21 Feb 2013 10:02:17 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1422668-391-1.aspx1) 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 [i]per se[/i]. But for performance reasons, it's often best to increase the number of tempdb [i]data[/i] files to match some proportion of the number of physical processors.Thu, 21 Feb 2013 09:59:47 GMTScottPletcherhttp://www.sqlservercentral.com/Forums/Topic1422668-391-1.aspx1. any query uses group by , order by etc.. uses tempdb. so if view contains that tempdb will use2. no of physical cpu = no of tempdb files.Thu, 21 Feb 2013 09:22:16 GMTbangsqlhttp://www.sqlservercentral.com/Forums/Topic1422668-391-1.aspx1) 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 muchdrewThu, 21 Feb 2013 09:19:26 GMTdrew.georgopulos