Post reply

Performance issues (timeout expired)

  • August 31, 2011 at 10:55 am

    #242631

    Hello everybody

    Someone can help me to find out the problem about the following situation:

    I have a server with SQL Server 2008 Std. Edition one of the database in the server constantly throw the error message "Timeout Expired" when a user runs a query against the database. Specifically there is a process that calculates the payrroll in the company, the process uses a stored procedure with many cursors and I have identified in sql server profiler when the stored procedure is running a fetch statement (fetch next from cursor_xxx) the error message rises because it takes too much time.

    I rebuild the indexes every two days, the tempdb database is located in a RAID-10 drive different from the installation directory of SQL Server, the database is in the same RAID-10 drive.

    Hope you can tell me what can I check to fix that problem. Thanks.

  • August 31, 2011 at 11:24 am

    #1375595

    With the details you have provided, the best way to do it would be to get rid of the cursor. Like you stated, it is taking way too much time and resource. Once you rewritten the SP and get rid of the cursor, check the execution plan and see if you have enough index support.

    -Roy

  • August 31, 2011 at 11:32 am

    #1375600

    I agree with Roy. Based on the info, I'd start by removing the cursor.

    It also appears that you are using temp tables for staging the data. Are these temp tables or table variables?

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • August 31, 2011 at 12:36 pm

    #1375630

    Ok, I will consider you suggested, thanks I will remove all the cursors used in the stored procedures.

  • August 31, 2011 at 12:46 pm

    #1375634

    Once you've removed all the cursors, the next step would be to break the stored procedure into 'chunks' and run each bit individually. This will allow you to identify which bits of the procedure are the real problem areas and therefore the areas that you should be performance tuning using execution plans.

  • August 31, 2011 at 1:17 pm

    #1375663

    Hello, thanks for your advice I will remove the cursor. In the stored procedure no temporary tables are used and no table variables are used too, only cursors.

  • August 31, 2011 at 2:07 pm

    #1375697

    Let us know if you were able to solve the problem. I would also try to collect some performance data to identify possible bottlenecks, for that you can use the information in this link[/url].



    My blog

Viewing 7 posts - 1 through 6 (of 6 total)

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