Post reply

very slow insert statement initially then fast execution subsequentially

  • September 13, 2007 at 9:12 am

    #174797

    i've got a site that's timing out when inserting a record (approximately 5 columns of data). at first, i suspected it might be something to do with the asp code, but have since tried running the statement directly against sql server. the initial insert statement took 1:07 minutes to run. subsequent executions of the same statement run in under 1 second.

    it seems that sql server is asleep and needs to wake up and process the initial job. once it's awake, everything runs fine. assuming i'm correct, is there a way to keep sql active and ready to quickly respond. if there really is no such thing as a sleeping sql server, what can i do next to troubleshoot this? i'm looking at moving the client's db to a new sql instance, which i hope will improve performance, but i still have to figure out what's going on with the original sql instance.

    tks

  • September 13, 2007 at 9:42 am

    #733511

    Could be an execution plan compilation issue, but it still shouldn't take that long for a simple insert. In the database properties dialog box is the Auto Close option set to true or false?

  • September 13, 2007 at 9:45 am

    #733515

    autoClose is set to false.

  • September 13, 2007 at 10:47 am

    #733553

    check for "blocking" or "autogrows"


    * Noel

  • September 13, 2007 at 10:47 am

    #733551

    Oh and "autoshrink" too


    * Noel

  • September 13, 2007 at 11:34 am

    #733566

    thanks noel.

    i think you're right about the autogrows. i looked through the logs and can see a 'autogrow of file "X" in database "X" was cancelled by user or timedout after 30422 milliseconds. use alter database to set a smaller filegrowth...' i've got an error 500 asp page that gives me a failure report that seems to coordinate with the times in the sql errors. now i just need to figure out how to change the filegrowth property.

    tks again.

  • September 13, 2007 at 11:50 am

    #733571

    Try to schedule the grows for an off pick times and reserve enough space so that autogrow does not have to happen at critical times.

    Cheers,


    * Noel

  • September 14, 2007 at 12:06 am

    #733669

    Really a cool problem, ok lets first analyse the problem first.

    SQL Server usually takes more time for executing a query for the first time and it will take very less time on subsequent query execution. Its because its creating statistics for that particular query.

    Solution for your problem, Please have a backup of your log file and truncate the log file and check it out.

    Regards,

    Venkatesan Prabu. J

    HCL Technologies,

    Chennai

     

     

    Thanks and Regards,
    Venkatesan Prabu, 😛
    My Blog:

    http://venkattechnicalblog.blogspot.com/

  • September 14, 2007 at 6:36 am

    #733746

    there must have been something wrong with the db as the log file was close to 50 gigs while the db is only 13 megs. i detached the db, deleted the log file, and reattached, which created a new log file which is a mere 504kb.

  • September 14, 2007 at 7:59 am

    #733776

    5 columns of what?  And why approximate?  I have inserts that has 5 columns that take 30 bytes, I have some that take 400K.

Viewing 10 posts - 1 through 9 (of 9 total)

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