Post reply

Insert taking too long

  • September 20, 2006 at 7:05 am

    #116996

    So, my second emergency as a fledging DBA.
     
    There is an account that tries to insert a new record into a table, and it's taking over a minute, they say. I'm getting a copy of their database and when I do, I'll be able to say more. They only have 20,000 records, so I can't imagine why it's taking so long. However, the first things I was going to look at are:
    1) Verify they're telling the truth.
    2) Examine the Execution Plan and maybe use the Tuning Advisor.
    3) Look at the Indexes
    4) Look at the Triggers, perhaps it's a combination of multiple inserts?
     
    With my #4, will the triggers show up in the Execution Plan and Tuning Advisor, or only the Insert statement itself?
     
    What else should I be looking for?
     
    em

  • September 20, 2006 at 7:22 am

    #661481

    I don't know about the tuning advisor, but in query analyser, if you show the execution plan, then the trigger's plan will be included as well.

     

    However I'd also look at how many indexes are on the table and how wide is the clustered index.  Those 2 things can have a huge impact on insert/update performance.  Also look if something is locking the table, maybe the insert is fast but has to wait before executing.

  • September 21, 2006 at 5:45 am

    #661699

    Be careful when using a copy of a database beacuse you've lost the original hardware and environment which might be the problem. My guess is that when you try the insert on your own machine it will work perfectly!!!

    As you've mentioned triggers then that might be your problem. Ask them to profile the update on their system using the showplan events and get them to send you the trace then you can see what's really happening. Make sure they include the batch and statement start and finish events with times to trap for network/middle tier/client issues.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • September 21, 2006 at 12:22 pm

    #661834

    Also try to monitor the locks on the table. Any transaction taking longer than required?

  • September 22, 2006 at 9:04 am

    #662005

    Ok, I'm confused by the Profiler. I can get it to show me that things are being locked, but I can't figure out how to get it to tell me which table, exactly, it is locking.
     
    I'm sorry for such a beginner's question.
     
    em

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply