Need Suggestion - Performance Issue


  • Hello guys, 

    Finally I'm here for your help after various trials with my data.


    I'm dealing 50M records and row length is 7000 wide. I have to load into destination table, according to my previous load of 1.8 M records with row length 7000 wide it took my procedure to run for 2 hours to load  and based on that I estimated like around 48 - 50 hrs for 50M records. Everything is fine but my tempdb is filling up very fast when I'm dealing with 50M records by which my data file is filling fast and due to this i have to stop my process. 

    Its not not straight load, involves 5 table joins and has indexes properly. Destination table log file and data file is not having any issue. 

    Any thoughts on how to avoid filling  tempdb very fast?

    Sunday, September 01, 2013 6:25 PM


  • what is the recovery model of the database? Changing the model to "bulk logged" will have minimal log.

    check these for further understanding

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Hello Sarat,

    IMHO how can tempdb be related to Recovery model .Recovery model has effect on database transaction log precisely not tempdb as such.

    Hello Murali,

    Also I suppose there must be some Group by ,order by, sorting (heavy) operation going on which is causing tempdb to fill.Lot of temp tables can also cause it to fill.

    Are you using SNAPSHOT isolation level or Isolation level that used versioning ,these heavily rely on temp db and use it.

    Only operations that support minimal logging will be minimally logged rest all operation will be fully logged ,and right now I have no idea about what commands you are using.see below

    Changing recovery model to Bulk Logged can have negative impact with slow hardware(In some cases).Only guarantee with Bulk rec model is logging will be less .See minimal logging forces data pages to be written to disk before transaction commits this forceful flushing can create bottleneck if hardware is slow.

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Sunday, September 01, 2013 7:13 PM

All replies