SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Balloons and Data


Balloons and Data

Author
Message
Jim P.
Jim P.
SSC Eights!
SSC Eights! (899 reputation)SSC Eights! (899 reputation)SSC Eights! (899 reputation)SSC Eights! (899 reputation)SSC Eights! (899 reputation)SSC Eights! (899 reputation)SSC Eights! (899 reputation)SSC Eights! (899 reputation)

Group: General Forum Members
Points: 899 Visits: 2215
I don't have a good way to test this anymore. But at my last job we would do a data load once a month that consisted of text files that had millions of rows and 30-60 columns to about 60 matching tables.

I would purposely grow the tempdb files ahead of time. Then do a shrink afterward. It seemed to work well.

Was that a bad idea?



----------------
Jim P.

A little bit of this and a little byte of that can cause bloatware.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85124 Visits: 41077
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.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eric M Russell
Eric M Russell
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12094 Visits: 10634
I have a job kicks off every 5 minutes and queries active requests and joins session info, t-sql statement, cpu/io/duration, etc. into a table on my local instance. Also, a while back I added tempdb and transaction log usage, so I can monitor and archive that as well.
Adding an index here and there can switch hash joins or sort operations to merge joins, and not only speed up processing but also use less tempdb allocation. To reduce transaction logging, look for procedures that perform multiple updates and consolidate them into one single update.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Miles Neale
Miles Neale
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3026 Visits: 1694
Jim P. (8/8/2013)
I don't have a good way to test this anymore. But at my last job we would do a data load once a month that consisted of text files that had millions of rows and 30-60 columns to about 60 matching tables.

I would purposely grow the tempdb files ahead of time. Then do a shrink afterward. It seemed to work well.

Was that a bad idea?


Over time there has been one test I have done to answer this question and many like it: Did it work? If the answer is yes then it was a good solution. Was it the best solution? Does not matter, it worked and you cleaned up the effects after. Done!

M...

Not all gray hairs are Dinosaurs!
Steve Jones
Steve Jones
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61887 Visits: 19099
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) Hehew00t;-):-P

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85124 Visits: 41077
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) Hehew00t;-):-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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14222 Visits: 12197
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) Hehew00t;-):-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
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85124 Visits: 41077
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) Hehew00t;-):-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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search