Balloons and Data

Question

Balloons and Data

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TomThomson SSC Guru Points: 104773 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tom Gillies SSCrazy Points: 2761 More actions · Answer 4

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 Profile www.DuhallowGreyGeek.com[/url]

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 5

Steve Jones - SSC Editor - Thursday, August 8, 2013 8:49 AM
First, 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.

timwell SSCertifiable Points: 5315 More actions · Answer 6

Jeff Moden - Friday, August 9, 2013 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.

So did this article ever get written?

Eric M Russell SSC Guru Points: 125561 More actions · Answer 7

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://www.sqlservercentral.com/scripts/tempdb/151252/

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho