tag:blogger.com,1999:blog-3702702923592093288.post3365055909648847040..comments2023-11-25T05:29:22.718+13:00Comments on Page Free Space: Temporary Table Caching ExplainedPaul Whitehttp://www.blogger.com/profile/04690243284528295117noreply@blogger.comBlogger14125tag:blogger.com,1999:blog-3702702923592093288.post-15896203697063689582023-03-24T04:01:18.018+13:002023-03-24T04:01:18.018+13:00Hello Anonymous,
SQL Server caches one data page ...Hello Anonymous,<br /><br />SQL Server caches one data page and one IAM page for each temporary object.Paul Whitehttps://www.blogger.com/profile/04690243284528295117noreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-40134902749416025712023-03-24T03:50:53.211+13:002023-03-24T03:50:53.211+13:00As the cached temp tables are metadata only, its s...As the cached temp tables are metadata only, its size should be very small. But I saw few cached tables size of 30-40 MB in my systems. How to check why its size is that much?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-60200720974022289152022-03-26T22:49:58.449+13:002022-03-26T22:49:58.449+13:00Yes the temporary table is truncated before cachin...Yes the temporary table is truncated before caching.Paul Whitehttps://www.blogger.com/profile/04690243284528295117noreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-30745774436864118992022-03-26T11:55:43.822+13:002022-03-26T11:55:43.822+13:00I presume the data is deleted from the temp table ...I presume the data is deleted from the temp table before the object is cached, right?Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-61502318790058031322021-11-10T21:40:48.104+13:002021-11-10T21:40:48.104+13:00Rayapati, there's no difference for a cached t...Rayapati, there's no difference for a cached temporary table whether it is explicitly 'dropped' or not. Both are replaced by a renaming operation. There's no advantage to dropping explicitly, so I typically don't bother.Paul Whitehttps://www.blogger.com/profile/04690243284528295117noreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-36883542713921460172021-11-10T21:35:52.733+13:002021-11-10T21:35:52.733+13:00Hi Marc, No there isn't. Shame, because it wou...Hi Marc, No there isn't. Shame, because it would have made writing this post and https://sql.kiwi/2012/08/temporary-tables-in-stored-procedures.html an awful lot easier.Paul Whitehttps://www.blogger.com/profile/04690243284528295117noreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-47076589133626257832021-11-09T04:45:09.009+13:002021-11-09T04:45:09.009+13:00Is there a way to view the statistics on a cached ...Is there a way to view the statistics on a cached temp table after executing the stored proc that created it?Marc Scirrinoreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-91352895099807078432021-10-15T04:17:26.644+13:002021-10-15T04:17:26.644+13:00Thanks Paul, is it only the transaction log overhe...Thanks Paul, is it only the transaction log overhead if we drop the temp tables explicitly, or any other impact Rayapatihttps://www.blogger.com/profile/05537566466351829007noreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-4488543695024102512021-01-11T22:11:53.652+13:002021-01-11T22:11:53.652+13:00Awesome article, Thanks Paul.Awesome article, Thanks Paul.R.K.Nairhttps://www.blogger.com/profile/13916752870489217925noreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-70505737007238571192020-08-18T17:59:42.085+12:002020-08-18T17:59:42.085+12:00Hi Matt,
Yeah that mistake was originally in Pam&...Hi Matt,<br /><br />Yeah that mistake was originally in Pam's blog post as well (now hosted at https://docs.microsoft.com/en-us/archive/blogs/sql_server_team/tempdb-files-and-trace-flags-and-updates-oh-my).<br /><br />I contacted Pam privately about it at the time, and her article was corrected (look for the "EDIT:" text):<br /><br />> [EDIT: While an explicit drop of a temp table in a stored procedure is unnecessary and thus not recommended, it will not invalidate the cache entry.]<br /><br />Sadly, the YouTube video itself cannot be corrected so easily.<br /><br />The information in my post is correct, and includes demo code for anyone wishing to validate it for themselves.Paul Whitehttps://www.blogger.com/profile/04690243284528295117noreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-46869961512918852512020-08-18T04:09:58.123+12:002020-08-18T04:09:58.123+12:00I had been advising folks not to drop temp tables ...I had been advising folks not to drop temp tables in procedures as recommended in this MS presentation https://youtu.be/vKvnIa6S-nQ?t=3681 ("If you explicitly drop it, we can't effectively cache it, and it causes additional metadata contention because we can't leverage the cache. So please don't explicitly drop your temp tables."). Now I really don't know what to think.Matthttps://www.blogger.com/profile/01934133058639336372noreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-32264974185911139122020-03-01T06:23:07.077+13:002020-03-01T06:23:07.077+13:00This is one of the best content available for sql ...This is one of the best content available for sql insight.Anonymoushttps://www.blogger.com/profile/12656331614389881113noreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-3844052685442267602018-12-20T21:52:23.504+13:002018-12-20T21:52:23.504+13:00Excellent article, thanks.Excellent article, thanks.AllinADazeWorkhttps://www.blogger.com/profile/18370474456065481142noreply@blogger.comtag:blogger.com,1999:blog-3702702923592093288.post-39993862304041671232018-12-13T17:24:51.055+13:002018-12-13T17:24:51.055+13:00Great article about the internals of Temp tables v...Great article about the internals of Temp tables vs table variables and the clean-up operations.JDhttps://www.blogger.com/profile/03882674205243271387noreply@blogger.com