Are there any indexes on the table you imported into? If there is a clustered index, you may have just seen a large amount of page-splitting. Also, you may want to run dbcc updateusage (0) on the database, to make sure you are getting the most up to date information from these numbers. They are not updated automatically very well.
the database is ~7gb in total
this one table, when loaded, (with this allocation issue) causes the database to jump to over 50gb.
this table should only be a couple gigs by itself.
it is completely empty between each of my test runs.
i take my ealier 'clustered' index comment back - its actually a non-clustered unique with the default fill and pad values.
interestingly enough - i just used a logged operation to load the table (insert into...select) using a linked server to get the data off the source server and the allocation was normal. ~800k reserved and ~800k used.
this must have something to do with the way BCP is working.
Using BCP to load this table SQL Server is allocating (sysindexes..reserved where indid = 0) 6 million pages to my table but only using (sysindex..dpages where indid = 0) 800 thousand. This is actual table data, not a log file growing. This is unrecoverable page allocation.
NOT supposed to happen.
The table is empty prior to using BCP to load and having the above allocations occur. The table has a unique non-clustered index with a 100% fillfactor - but there are no rows in the table.
When using a linked server and an INSERT INTO...SELECT statement instead of BCP the allocated and used pages are virutally identical. Both in the 800k range.
It's only when using BCP that the table allocates an additional ~50gb of EMPTY pages and ends up with a rows per page density of ~3.
I have no idea what is going on. I cant use the INSERT INTO...SELECT in production because the BCP file is coming from a far remote site that i have no access to.
good idea, thought about it, but have not tried it.
the nonclustered PK is the only index on the table and it is what keeps the data...well...unique. if i drop that index i have 2 issues:
1. loading dupes - not cool.
2. would need to drop all the FKs related to it in the process, and re-build all of them too. the load window will not allow a full rebuild of all the PKs and related FK constraints - these tables hold tens upon tens of millions of rows each and building the constraints in testing has shown that to take many hours (6+) - with an hourly load window everything needs to take less than an hour before the new files show up. UGH.
the INSERT..INTO test i ran loaded ~4.5 million in just a few minutes - so the assumption is that BCP would be much faster...but it takes ~30 minutes due to all this empty page allocation/splitting and the ensuing database file growth.
i cant understand why it would over allocate using BCP and not with a fully logged operation.
User-specified FILLFACTOR values can be from 1 through 100. If no value is specified, the default is 0. When FILLFACTOR is set to 0, only the leaf pages are filled. You can change the default FILLFACTOR setting by executing sp_configure.
From this, the default fill factor may not be 100%. What is the server default fillfactor from sp_configure?
default fillfactor is still stock meaning '0' or '100%'.
but i dont think that is a part of the equation, as using a fully logged insert avoids the over allocation issue entirely. (wish that were a possible solution)
fillfactors are only used when you create/rebuild an index.
from that point on SQL server endeavors to fill every page entirely.
in this instance, with a fully truncated table, even with a 100% fillfactor each index page is still completely empty and should (in theory) accept data until it's full.
regardless...the over allocation is to the data pages in this particular fiasco of an issue the non-clustered index page allocations are stored in seperate rows in sysindexes and they are all normal and not splitting.
i'm tracing the process now, and watching perfmon's extents allocated per second counter and it is spinning through the freakin' roof!!!!
I'll yank out the BULK INSERT and the table definition statement once this trace finishes.