SQL Server Performance

Some SPs Time Out

Discussion in 'SQL Server 2005 General DBA Questions' started by pecobeconen, Jan 4, 2008.

  1. pecobeconen New Member

    First of all sorry with my english.
    I have a VB6 application running with a SQL SERVER 2005 SP2 (on WINDOWS 2003). The application connect to SQL with sqlncli library, like:
    "Provider=SQLNCLI;Data Source=xxxx;Initial Catalog=xxxx;User Id=xxxx;Password=xxxx;"
    .CursorLocation = adUseClient
    .ConnectionTimeout = 20
    All work fine, but in some moments, some SPs (3 or 4) starts to receive Time Out (only under the application). If i execute them with QA and same parameters they run excellent, but under application i received Time Out.
    The SPs are huge SELECTs with some SubConsultants. The selects are over (aprox) 20 tables, some with 1.000 files others with 10.000 and one with 1.000.000 files.
    Using SQL Profiler, the Selects (SPs) show (average, when run OK): CPU: 1400, Reads: 191.000, writes: 0, Duration: 7.000
    and show (average, when NOT run OK): CPU: 1.190.000, Reads: 21.165.000, writes: 0, Duration: 4.700.000
    When TimeOut appears, it only disappears doing a Full Maintenance of the database (Shrink, ReIndex, StatsUpdate, etc). But somethimes SPs starts to work again without do anything! [:S].
    I dont´t undestandt why if under application i received TimeOut, at same time under QA i can run SP without any problem!
    The database have: about 800MB, 150MB FreeSpace, AutoClose FALSE, Auto Create Statistics: TRUE, Auto Shrink: FALSE, Auto Update Statistics: TRUE, Auto Update Statistics Async.: FALSE
    Thanks, i hope someone help me.
    Predo!
  2. Adriaan New Member

    The connection has a timeout setting ("ConnectionTimeout = 20") and QA usually has no timeout. Use "ConnectionTimeout = 0" for your connection to prevent the timeout.
    For a complex query, 20 seconds is not excessively long. Also, the connection has to wait for the data to travel across the network, so if there is a lot of data being returned this could easily add up to over 20 seconds.
  3. pecobeconen New Member

    Thanks, response!!
    Days ago we tried that and dont work (and i thought was worse, becouse it freeze the connection "PC to Server". The PC with problem when calling the SP freezed, we must shut down the connection to the server onto that PCs), to the server are connected about 20 local PCs and 6 via VPN.
    Thanks, Perdo!
  4. Adriaan New Member

    "CursorLocation = adUseClient" means all the data has to travel to the client, after which the query will be executed on the client. QA normally lets SQL process the query.
  5. pecobeconen New Member

    I´ll tried it with conflictive SPs and comment.
    I can´t change the connection setting for all the application, becouse server Cursor Location dont let me use a lot of recordset properties.
    Thanks.
    By the way........ the same SPs and Database was migrated from 2000 to 2005, when it runs over 2000 never had it problem, any idea???
    Pedro!!
  6. MohammedU New Member

  7. pecobeconen New Member

    Thanks.
    I´ll check those articules too and comment my results.
  8. Adriaan New Member

    If the old server had just one processor, and the new one has multiple processors, then your query might be suffering from unwanted parallellism.
  9. pecobeconen New Member

    Adriaan, yes... old one had 1 Processor, actual have 2.
    What could i test, check or change????
    Changing CursorLocation don´t works, minutes ago i had again a SP Time Out.
    Thanks.
    Pedro!
  10. Adriaan New Member

    The timeouts will occur as long as you have a timeout setting on the connection (other than 0) - the process just takes longer than the setting allows.
    For the parallellism issue, find out what query within your sp is taking the largest amount of time - if you have just one query, you're in luck. As the final clause of this query statement (after your WHERE, GROUP BY, HAVING and/or ORDER BY clause) add OPTION MAXDOP = 1.
  11. satya Moderator

    Sagesh, that is applicable for the oDBC or OLEDB type of process running - http://vyaskn.tripod.com/sql_odbc_timeout_expired.htm fyi.
    Peco,
    Is this SP upgraded from SQL 2000 recently, if so perform a recompile and see the execution times. Also if the underlying tables are changed frequently then think about UPDATE STATISTICS to keepup the performance.
  12. pecobeconen New Member

    Thanks all comments.
    Since I put the (MAXDOP 1) Option at the end of huge Selects they run "fine". I dont´r sure if that is the solution, but they are running without problem.
    Now, the problem is when server Restart (here we have electricity problems, and last weeks we need to ShutDown the server many times (10 at leats) ). Always when restart I need at least to do a SP_UPDATESTATS or (if SPs still not running) run the complete Maintence Plan (it´s into a JOB executed every night) manually. After one of this steps the SPs starts running fine.
    Is that normal???
    Looking http://vyaskn.tripod.com/sql_odbc_timeout_expired.htm i have everything fine!
    Thanks, Pedro!
  13. satya Moderator

    If the weekly reindexing job is running ok without any issues then you could simply run UPDATE STATISTICS job on required tables to get it running for the application.
  14. sagesh New Member

    Hi,
    Please try CommandTimeout property.
    Set .CommandTimeout = 600
    I think this will help you.
    Thanks & Regards
    Sagesh.K [:)]

Share This Page