Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Jul 2004
    Posts
    60

    Unanswered: BCP and table space allocation quandary

    Hey all, first time poster here.
    Cheers!

    Enterprise Edition SQL2k sp3a on Win2k3 Enterprise Edition server.

    Using BCP to load ~4 million records.

    Largest record width is ~350 bytes.

    When finished i have over 6 million pages reserved and only about 800 thousand actually used.

    Which puts me 'round about 3 rows per page and WAY too much empty space.

    YIKES!

    Any thoughts as to why this is happening?

    Or insight into how BCP handles page allocations that may be different than the typical extent based page allocations for table data?

    Thanks!

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It's not bcp...it's how the database is set up with free space....

    Sounds like the percent is high, which is allowing for a large number of inserts...

    If it's low, then the inserts would occur on new pages....and more maint is required...

    But it does sounds way too high....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    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.

  4. #4
    Join Date
    Jul 2004
    Posts
    60
    Hmmm...well there is a clustered index on the table with the default fill factor (0 = 100%, right?)

    I'm truncating the table between runs, so i'm not sure i follow why it would be splitting pages - as there arent any in existence to split prior to running the bulk load.

    Do you have a moment to elaborate a bit for my edification and education?

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'd be curious...

    Go into EM and select all tables, then go to the option tab and select indexes...

    Just thos, and script it out.

    The indexes will all fall to the bottom...take a look at what FILL_FACTOR and PAD_INDEX are set to....

    I usually never touch them....and relying on weekly maintenance...

    BUT if your transaction volume is high, I'd imagine you'd want to...

    How big is the database overall?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jul 2004
    Posts
    60
    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.

    im still very confused.


  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well yes...confused...

    INSERT INTO...SELECT is not bcp

    It IS also a HIGHLY logged operation

    Is the other linked server SQL Server?

    I would bcp out the table, truncate the existing (if need be) then bcp the data in..

    Oh, and you're problem is your log if I'm not mistaken, not the datafile

    I would dump the trans log before the operation, perform the bcps, then take a full backup, with regular tranny dumpsa afterwards
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jul 2004
    Posts
    60
    ahhh...OK...i'll clarify a bit.

    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.

    Any thoughts???


  9. #9
    Join Date
    Sep 2003
    Posts
    364
    Have you tried dropping all indexes and PK's before the BCP then re-creating them after the BCP? Or ordering your data file to match the columns and sorting of the pk and indexes?

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You know what...istead of us shooting blanks...

    How about you post the ddl of the table, include the bcp code, and a small sample of the data...

    bcp will not preallocate all those pages...at least I haven't seen it do that or read where it does.

    Not saying it can't mind you, it's just doesn't make sense from a logical perspective...

    And did you know that bcp actually is logged....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Jul 2004
    Posts
    60
    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.

    therein lies the crux of the issue.
    Last edited by oddity; 07-01-04 at 16:31.

  12. #12
    Join Date
    Jul 2004
    Posts
    60
    yeah...BCP is minimally logged...it logs disk allocations only, though, i believe.

    i'll put that stuff together - thanks for asking!

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What, may I ask, happens to dups with a PK on the table?

    The bcp should fail. No?

    I know contraints are ignored unless a hint is specified.

    And I know that a PK is a constraint.

    But I've seen dup keys kick out and fail the bcp, where FKs are ignored...

    Damn flashbacks...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    This is from BOL (CREATE INDEX):

    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?

  15. #15
    Join Date
    Jul 2004
    Posts
    60
    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.

    Boy, this is a whopper.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •