SQL Server Performance

SQL Server data copy problem

Discussion in 'General DBA Questions' started by linus516, Apr 13, 2007.

  1. linus516 New Member

    Hi,



    I am facing a great problem. I have a DB about 80GB. There are 217489811 number of records which include 2005, 2006 and 2007 data in 1 table. Now I want to split the table into by quarter tables 2005Q1, 2005Q2.....etc. It is because it is easier for me to housekeep the old data. (It takes about few hours to housekeep 3 month data currently, so that it affect my server performance a lot)



    so I start my work, however it is failed every times. Actually, if I copy 3 month data to a new tables, it is nearly about 30 million records which needs about 1 GB space. However, it takes about 8 hours to complete the job and failed because of not enough space allocated. Actually, my Server has about 50GB space, but when the job complete and fail, all the space is occupied. But i wonder why this happen because the data only need about 1 GB, how come it use 50GB?



    it seems that nothing i can do to housekeep the old data. because housekeep the current table takes too long time while splitting tables use up all the space. So how can i do?



    For your information, the table has non-clustered index on the data date field



    need your help

    thanks,

    Linus
  2. ndinakar Member

    Looks like your log is getting filled up. Look into using the BCP utility. Its much faster. Also, have a job running to TRUNCATE the LOG and let it run every minute or so, so the log doesnt fill up.

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  3. linus516 New Member

    my DB recovery mode is set to simple, so how comes the log will grow up?

    BTW,

    1. BCP utility means everything is copied to text file and then re-insert into SQL Server ?
    2. how to write a job to truncate the log, can you show me some example?

    Million thanks,
    Linus
  4. MohammedU New Member

    Even your database recovery is in simple, to transfer the data sql has to use log and if you are running in single transaction then your will not be truncated until it committs...

    You can use BCP utility as mentioned and you are correct you have to copy the data to file and upload again or

    You can use DTS package to copy the data and make sure your destination table do not have any indexes and you check the check box fast load option in dts properties to use log minimally...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  5. ndinakar Member

    yes BCP OUT is copying to text file and using BCP IN to the new db/table. It is much much faster than DTS and you can see the difference if the data is over a million rows.
    For truncating the log i think the command is something like:

    BACKUP LOG <Db> WITH TRUNCATE ONLY

    I could be off a little you can check BOL for exact syntax. Now you can create a job with this command and schedule it to run every minute during your data transfer. Make sure the table has no indexes/FK's. Just have the clustered index and you should be fine.

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  6. MohammedU New Member

  7. linus516 New Member

    SQLState = S1000, NativeError = 0
    Error = [Microsoft][ODBC SQL Server Driver]I/O error while writing BCP data-file


    do you know what is the problem?
  8. linus516 New Member

    it seems that the performance does not boost up a lot.............it takes around 3 hours to import 15M data into the table...............however, there are 5M data remaining......i dont know when the job finish.....
  9. ndinakar Member

    are you creatig one gigantic file with all 15mil rows? Its better to create multiple smaller files than one big file.

    ***********************
    Dinakar Nethi
    SQL Server MVP
    ***********************
    http://weblogs.sqlteam.com/dinakar/
  10. MohammedU New Member

    Do you have indexes on destination table? If yes, try without indexes...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  11. satya Moderator

    You mean dropping and recreating them, it will take again many number of hours to recreate them.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  12. MohammedU New Member

    It is not always the case...

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  13. satya Moderator

    But it is with 15 million rows where the bulk insert itself hitting the roof [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.</i></font id="size1"></font id="teal"></center>
  14. MohammedU New Member

    I have copied many times 15 million rows table under 60 minutes...
    It all depends on the table structure, if the table has the text columns definately it is going to take long time even with bulk insert...

    If the destination table has the index, then it will use transaction which cost the peformance...for fast BCP table should not have the index...



    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  15. satya Moderator

    Well, lets not deviate or hijack the Originator's post and wait for the feedback.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  16. linus516 New Member

    thanks for you advice.............finally it takes 3 hours to complete the job...........maybe the destination table has index.......i forget to drop it first..............but just want to know why the index cost so much time?
  17. linus516 New Member

    another question..........how to delete data much more efficiency from this 217489811 rows table
  18. MohammedU New Member

    It depends on how much data you want to delete out of 217 mil. rows...

    How many rows you want to delete?


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  19. linus516 New Member

    i think about 1.2M
  20. satya Moderator

    Then you have to follow usual procedure to delete rows in smaller chuncks say 10000 per transaction and ensure to look at the transaction log growth by performing regular log backups, otherwise it will have knock on affect on the log sizes.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  21. MohammedU New Member

    WHILE 1 = 1
    BEGIN
    SET ROWCOUNT 100 -- you can try different values...
    DELETE FROM TABLE WHERE ....
    IF @@ROWCOUNT = 0 BREAK
    -- If you want you can use WAITFOR DELAY cammand..to wait few seconds/ms between each iteration...
    END


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  22. satya Moderator

    ... suggest that run this as a scheduled job and take care of tempdb & transaction log sizes during the operation.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.
  23. linus516 New Member

    hi ,

    i also want to know why this bcp action takes so much time for inserting the last few thousand records?

    it stops at

    1000 rows sent to SQL Server. Total sent: 597000 for about 45 mins.....but the whole job last for only 1 hr 17mins..........i dont understand about that

    there are about 632464 rows copied.
  24. MohammedU New Member

    Because it takes time to commit the transaction....if don't specify -b switch all rows will be treated as single transaction....

    -b batch_size

    Specifies the number of rows per batch of data copied. Each batch is copied to the server as one transaction. SQL Server commits or rolls back, in the case of failure, the transaction for every batch. By default, all data in the specified data file is copied in one batch. Do not use in conjunction with the -h "ROWS_PER_BATCH = bb" option.

    Did you try with no indexes on destination table?

    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  25. linus516 New Member

    is it meant that i should use -b to boost up the performance?

    Now the destination table is with no index

  26. satya Moderator

    Not with the performance only for the sake of keeping up the transaction limit 1000 rows per batch. That partially helps the performance, have a look at BOL for BCP information in this case and you can scheudle a job if this is ongoing requirement.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Share This Page