SQL Server Performance

Investigating sproc freeze

Discussion in 'T-SQL Performance Tuning for Developers' started by ralmoritz, Feb 27, 2008.

  1. ralmoritz New Member

    Hi,
    I'm trying to fix a problem in a legacy app, who's maintenance I'm unfortunate enough to have inherited. The app reads data from an external source and inserts it into working tables, marked "tempXXX". Once all the data has been read, the data from the working tables is inserted into the final tables and the working tables are truncated.
    There's a sproc, "spCopyData", which copies data from "tempTable1" to "Table1". Now "Table1" has a trigger that fires on insert. This trigger calls another 6 sprocs (!) to copy data from various working tables (tempTable2 .. tempTable7). A problem has come up at one customer's site, where there are 27 million records in one of the working tables (!), and 1.4 million records in another. The problem is that after executing for some time, "spCopyData" seems to just "hang" and make no further progress.
    I'd appreciate any advice on how to track down and correct this problem.
    TIA,
    Ralph

  2. ranjitjain New Member

    Hi,
    I feel longest running query in this case will be to read 27 million records from working table and inserting into final table which could be using more resources.
    It would be better if such working tables are skipped and data is inserted directly into final table if possible in small batches, so that you can avoid long transactions. IF working tables can not be skipped then the job being done through trigger should be made explicitly with small insert batches

Share This Page