Stored procedure timed out after importing dta

Posted on 2011-10-03
Last Modified: 2012-05-12
Hi Experts,
I really hope someone can help me.
I have a stored procedure in SQL server 2005 that imports data from a database on a different server. It can import about 10000 or more records at a time.
My desk top app calls this stored procedure giving it the date range to select the data. It works fine.
After it imports data, user can open a form to display this data on a grid. I call another stored procedure to get this data. but it times out. If I quit the app, goto sql server,
run     DBCC DBREINDEX(Edits_data, '', 80)
          UPDATE STATISTICS Edits_data
 and then go back to the application, it works fine.
Does that mean, my index and Statistics are not being updated? I have set "Auto Update Statistics and Auto update index True.

User can run a few batches at a time and import may be 30,000 records or more! I have one clustered index on primary key(this is an nvarchar field) and 3 other non-clustered index.

How do I solve this problem? It is stopping me from releasing the application. Any help will be appreciated.
Question by:RekhaShah
    LVL 51

    Expert Comment

    can you post the related codes for these sps..
    LVL 19

    Accepted Solution


    Hi RekhaShah,

    On a large table (>500 rows), statistics are automatically updated  when 500 +20%  of rows are modified. So, you your table contains 100,000 records, a 10,000 record insert will not update statistics. If possible, add code to update statistics to you procedure.
    LVL 3

    Expert Comment

    Hi RekhaShah,

    You could try to set the timeout period. It is set to 30 seconds as a default.
    It can be done like this

    I would try with Connection Timeout=0, which means "never"!
    10000 rows is nothing, we import 2 million rows a day. But it took a bit of work to design.
    Please do not leave your timeout at zero, as it will come back to hunt you some day.

    If you are rebuilding indexes and stats at a regular basis, you may be hit by a database growth?
    If this takes just over 30 seconds, your normal sproc will not be able to finish in time. But your dbreindex will need space for an additional copy of your biggest table, and this does not time out.


    Author Comment

    The table is not that big yet since it is still under development - most of the time I delete all the records. But, if i run the import and then without exiting the app, if I try to open a new form which will display, it times out. BUT, If i REINDEX and Update Stastics, IT RUNS WITHOUT ANY PROBLEM. So the actual problem may not be the real "timeout".
    Connection time out is not an issue since I am the only one who is connected to this db.

    It used to run fine in the beginning of the app development.
    I wonder if following would affect the performance
    I added

    1. some more data verifications
    2. added one more storedproc to check data
    3. Keep deleting the data from this table
    Like Rimvis suggested, I may add update state to my stored procedure. I will try it this evening.
    Any suggestions
    Thank you guys
    LVL 3

    Expert Comment

    If you delete all rows, and then import 10000 rows, the server needs to update all its statistics, because it has changed more than 20%. This takes time, maybe more than 30 seconds.

    May I recommend sp_whoisactive, so that you can see what the SQL Server is doing whilst your application is waiting for data?

    You can get it here:

    Your application may be blocking itself, if you have a BEGIN TRANSACTION too much.
    LVL 75

    Expert Comment

    by:Anthony Perkins
    First of all doing UPDATE STATISTICS after DBCC DBREINDEX is pointless.  Reindexing updates stats.
    Second DBCC DBREINDEX is deprecated use ALTER INDEX instead.
    Lastly and to answer your question, the problem lies in your Stored Procedure.  Unfortunately, until you post the contents, all we can do is make wild guesses as to the solution.

    Author Closing Comment

    I call a stored procedure that updates the the stats (no reindexing)  when the user leaves the form that allows him/her to import the data. This way, when user is done importing data- may be a few batches at a time, I only update stats once.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now