October 5, 2007 at 11:36 am
i tend to over-explain and talk too much so, my question first!
is there a way to keep sql server from sorting the result set? even though a sort method isn't specified in the select query, it still performs a sort (and thusly uses tempdb and eats disk space) i just want the records in what ever order it grabbed them from the table.
kind of a strange question that came to mind while testing, maybe someone can suggest a better method than what i threw together, any insight would be helpful. I've been attempting to test two different table implementations and ran into a storage issue in the process, I have an existing system where people before me have set up a partitioned view (ala sql server 2000) in a 2005 environment, (many tables with a check constraint on the date column, all accessed via a view that performs a union all on every table), i have created a partitioned table (files, filegroups and create partiton function, scheme, and table) that does the same thing, only with 1 table instead of 72. anyhow when trying to get the records from the old partition view to the new partition table i just used a
insert into newTable select * from oldTable
much to my surprise the tempDB was used to hold the records from the select query before then inserting into my partition table, this ultimately led to my test machine running out of available disk space.
October 5, 2007 at 11:58 am
I don't have a sample set I can work with to reproduce what you are talking about, but I would guess it is the UNION ALL of each of these tables that is really causing your issue.
Normally, when you UNION ALL a couple of sets of data together, you get a concatenation in the execution plan that can be handled entirely in memory. It is possible that the query optimizer has seen that your data will clearly not fit in memory and is using TempDB to manage the UNION.
I know you have 72 tables, but you may want to select your data in individually instead of using the partitioned view.
October 5, 2007 at 12:15 pm
hm i'll try that, each table is about 4-6GB which would fit in memory. thanks!
October 5, 2007 at 12:23 pm
It's not even important that a table fit into memory, the UNION ALL still has to be pasted together. If I remember the behavior correctly, it needs to collect all of the data first, UNIONS it all together, and then returns it. Pulling from a single table with no UNION, it can read rows into the buffer and back out to your destination table all in one step.
Watch your transaction log file - 4gb of data from one table in a single transaction could be a big file. You may want to chunk this up and commit in pieces. The insert may go fast, but the commit could take forever.
Remember, even if you have truncate log on checkpoint turned on, one big transaction will be all in the same checkpoint.
October 5, 2007 at 1:01 pm
big transaction log? i found out about that the first go-round , wound up being on the order of ~30GB , that along with the tempdb table at ~30GB as well and its log file, well, it ate up my test bed in short order!
thanks again, it's much appreciated!
October 6, 2007 at 8:49 pm
30 Gig? Can you even buy a harddisk that small anymore? Go buy a 300 GB disk or two from ABC Warehouse or something... shoot, I think even Staples sells them... they don't cost much anymore.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply