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.
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1803 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 (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)

Group: General Forum Members
Points: 206377 Visits: 41956
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
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28028 Visits: 11484
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
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4196 Visits: 1695
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 (141K reputation)SSC Guru (141K reputation)SSC Guru (141K reputation)SSC Guru (141K reputation)SSC Guru (141K reputation)SSC Guru (141K reputation)SSC Guru (141K reputation)SSC Guru (141K reputation)

Group: Administrators
Points: 141129 Visits: 19415
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 (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)

Group: General Forum Members
Points: 206377 Visits: 41956
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
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25306 Visits: 12488
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 (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)

Group: General Forum Members
Points: 206377 Visits: 41956
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
Tom Gillies
Tom Gillies
Say Hey Kid
Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)Say Hey Kid (696 reputation)

Group: General Forum Members
Points: 696 Visits: 307
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 GilliesLinkedIn ProfileAbout Me
Chris Harshman
Chris Harshman
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

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