August 9, 2013 at 11:22 am
Steve Jones - SSC Editor (8/9/2013)
Jeff Moden (8/8/2013)
Right sizing log files and datafiles for the FULL recovery model is a far bigger PITA than TempDB will ever be, IMHO. For example, even if you tell the system to "Sort In TempDB" when you go to do a clustered index rebuild on a very large table and you have Point-In-Time log backups running every 10 minutes, your log file can grow to several times the size of the original table during the process for a multitude of reasons. That's not to mention the bloat that it will cause in the MDF file while it makes a copy of the index.Of course, there's the fragmentation nightmare that occurs if you try to shrink a database.
All in all, there are some serious shortcomings that MS has continued to ignore on index maintenance over the years resulting in Balloons that appear to have check valves in the in the inflation tubes.
Sounds like a good article (hint, hint) :hehe::w00t:;-):-P
It's a complex subject so it might take a while to put together but an article on the subject is a good idea. I might just take that on and include snippets of the real life partitioning example that I've been writing code for to help alleviate such problems.
--Jeff Moden
August 9, 2013 at 5:05 pm
Jeff Moden (8/9/2013)
Steve Jones - SSC Editor (8/9/2013)
Jeff Moden (8/8/2013)
Right sizing log files and datafiles for the FULL recovery model is a far bigger PITA than TempDB will ever be, IMHO. For example, even if you tell the system to "Sort In TempDB" when you go to do a clustered index rebuild on a very large table and you have Point-In-Time log backups running every 10 minutes, your log file can grow to several times the size of the original table during the process for a multitude of reasons. That's not to mention the bloat that it will cause in the MDF file while it makes a copy of the index.Of course, there's the fragmentation nightmare that occurs if you try to shrink a database.
All in all, there are some serious shortcomings that MS has continued to ignore on index maintenance over the years resulting in Balloons that appear to have check valves in the in the inflation tubes.
Sounds like a good article (hint, hint) :hehe::w00t:;-):-P
It's a complex subject so it might take a while to put together but an article on the subject is a good idea. I might just take that on and include snippets of the real life partitioning example that I've been writing code for to help alleviate such problems.
Not just "might just", please. Write it, no "might" about it, please.
Tom
August 9, 2013 at 8:07 pm
L' Eomot Inversé (8/9/2013)
Jeff Moden (8/9/2013)
Steve Jones - SSC Editor (8/9/2013)
Jeff Moden (8/8/2013)
Right sizing log files and datafiles for the FULL recovery model is a far bigger PITA than TempDB will ever be, IMHO. For example, even if you tell the system to "Sort In TempDB" when you go to do a clustered index rebuild on a very large table and you have Point-In-Time log backups running every 10 minutes, your log file can grow to several times the size of the original table during the process for a multitude of reasons. That's not to mention the bloat that it will cause in the MDF file while it makes a copy of the index.Of course, there's the fragmentation nightmare that occurs if you try to shrink a database.
All in all, there are some serious shortcomings that MS has continued to ignore on index maintenance over the years resulting in Balloons that appear to have check valves in the in the inflation tubes.
Sounds like a good article (hint, hint) :hehe::w00t:;-):-P
It's a complex subject so it might take a while to put together but an article on the subject is a good idea. I might just take that on and include snippets of the real life partitioning example that I've been writing code for to help alleviate such problems.
Not just "might just", please. Write it, no "might" about it, please.
Gosh... With encouragement like that, how can I say "No"? I just hope I can do the subject justice. Someone like Gail (she knows the internals far better than I) would do a much better job on this subject but I'll give it a whirl.
Thanks for the encouragement, Tom.
--Jeff Moden
May 9, 2017 at 1:30 am
Thanks for that article Steve. I don't consider or describe myself as as DBA, but I do encourage people to go and talk to real DBAs ;-), Having read the article and the discussion here, I am just a little wiser about what is going on with TempDB and even more sure than I was about the value of knowledge and experience.
Tom Gillies LinkedIn Profilewww.DuhallowGreyGeek.com[/url]
May 9, 2017 at 7:17 am
Steve Jones - SSC Editor - Thursday, August 8, 2013 8:49 AMFirst, you need to change terminology. Worktables are intermediate tables used by SQL Server. If you refer to tables you create as worktables, it becomes hard to discuss performance related items.Second, if you need places for data to manipulate it, like staging tables for ETL, why not just create real tables? Don't drop them, and don't clear them out, except for the process that uses them. If you can't keep naming straight, then use a GUID in each process to create/drop the table. If you have process 1 using the same table as process 2 and you aren't sure when these processes will run, you are architecturally making a big mistake.
LOL, I've never known what to really call those, I've tended to call them "permanent temp tables" which makes the programmers laugh, but considering they have a tendancy to name them starting with tmp, it seemed fitting to me.
May 9, 2017 at 8:15 am
Jeff Moden - Friday, August 9, 2013 8:07 PML' Eomot Inversé (8/9/2013)
Jeff Moden (8/9/2013)
Steve Jones - SSC Editor (8/9/2013)
Jeff Moden (8/8/2013)
Right sizing log files and datafiles for the FULL recovery model is a far bigger PITA than TempDB will ever be, IMHO. For example, even if you tell the system to "Sort In TempDB" when you go to do a clustered index rebuild on a very large table and you have Point-In-Time log backups running every 10 minutes, your log file can grow to several times the size of the original table during the process for a multitude of reasons. That's not to mention the bloat that it will cause in the MDF file while it makes a copy of the index.Of course, there's the fragmentation nightmare that occurs if you try to shrink a database. All in all, there are some serious shortcomings that MS has continued to ignore on index maintenance over the years resulting in Balloons that appear to have check valves in the in the inflation tubes.Sounds like a good article (hint, hint) :hehe::w00t:;-):-P
It's a complex subject so it might take a while to put together but an article on the subject is a good idea. I might just take that on and include snippets of the real life partitioning example that I've been writing code for to help alleviate such problems.
Not just "might just", please. Write it, no "might" about it, please.
Gosh... With encouragement like that, how can I say "No"? I just hope I can do the subject justice. Someone like Gail (she knows the internals far better than I) would do a much better job on this subject but I'll give it a whirl.Thanks for the encouragement, Tom.
So did this article ever get written?
May 9, 2017 at 8:39 am
To understand the purpose of TEMPDB, why it can occasionally increase dramatically in size, it help to know what objects are allocated in TEMPDB during peak periods of growth. For querying this information, I wrote the following script a while back.
Query details about objects allocated in TEMPDB.
http://qa.sqlservercentral.com/scripts/tempdb/151252/
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply