Timeouts occurring, but not reflected in the SQL Server Error Log.

  • Timeouts occurring, but not reflected in the SQL Server 2005 Error Log. The timeouts are only reflected in the application log. There are a few COM objects on the same server as the SQL Server experiencing the issue as well as a web server on a separate server. The COM objects and the web server communicate with each other.

    My plan to investigate this issue is to use SQL Profiler and performance monitor to look for the completion of stored procs, look for locking as well as blocking, deadlocks, check memory usage, CPU usage, and I/O utilization.

    Can anyone recommend any other investigation options?

    Thanks

  • tell me more about these com objects you are using.

    The probability of survival is inversely proportional to the angle of arrival.

  • They are C++ objects and web services. A request is initiated on the web server to the COM objects and the COM objects pull data from SQL Server into memory and massages it before writing the data to a file on a separate file server. The amount of data is as much as 4GB. Once the process is compete a message is sent back to the web server and the connection to the database is closed. I realize SSIS could be used for this task, but the option to rewrite the process is a few months out.

    This process has run fine up until February. Since February timeouts have been occurring with an increasing regularity. One day of the week we do index rebuilds which take 5 hours to run and I expect the timeouts, the timeouts are now happening a couple of times every other day in the early morning hours (after 2am.).

    SQL Server's timeout period is set to 21600 or 6 hours.

    Thanks

  • Timeouts will never appear in the SQL error log, as they are not SQL errors. They are application errors.

    SQL has no timeout. It's happy to run queries for as long as they take, even if that is forever. What happens with a timeout is that the application sets a maximum time that it will allow for a query to run, if the results have not returned by that point, the application sends SQL another message telling it to cancel the execution of the query. The application then throws a timeout error.

    The timeout will be set in the application code, where it makes the connection to SQL. There's no query timeout setting in SQL Server.

    The solutions are to either increase the timeout in the application code, or to optimise the process. The latter is the better solution, otherwise you'll be constantly increasing the timeout. If you don't know where to start with performance tuning, it may be worth getting a professional in to look at it.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • The description of your architecture does not sound optimal for what you are doing. At the very least you should ditch the COM crap, and retrofit using using .NET assemblies. You did not mention whether ot not you are on a 64-bit platform or not, but .NET is able to fully leverage multi-threading and shared resources in that big virtual address space, where COM does not.

    Single tier design has scalability issues and they tend to bottleneck at some transaction level. It may be more appropriate for you to have a middle tier server to handle these client requests, and perhaps take advantage of some caching techniques. I would need a more intimate understanding of your application before I could comment any further.

    As Gail stated, these timeouts are not SQL errors they are client query timeouts that are a harbinger of performance issues due usually to architectural problems. Of course there may be some things you can do in the database (or server itself) like add more memory, add or optimize indexes to cover common queries or partition the large tables.

    The probability of survival is inversely proportional to the angle of arrival.

  • Yes I agree with your assessment. I will say that he SOL Server server level time out option is set to 21600 seconds which is 6 hours. Six hours is more than enough time. I will still do my due diligence and use sql profiler in conjunction with perf. mon just in case.

    Thanks

  • The windows 2003 and SQL 2005 are both enterprise level 64bit. The COM object is 32bit. Based on your assessment and talks around the office they are set on converting it to 64bit to resolve the issue because it would be easier to convert it then start from scratch with an SSIS package or .NET assembly. Any thoughts?

    Thanks

  • A 64bit platform can make a marginal design run well because it can put everything back in main memory again which makes everything run faster again (for a while anyway).

    The 32 bit COM stuff is another issue, of course. COM is deceptive... so easy and convenient to build a component to perform some cool function that seems to work fine in your development environment (as you impress the brass with your handiwork demo!). Then you put it into production with real transactions thrown at it and it is dog slow and consumes a lot of CPU and memory for the throughput it provides.

    The probability of survival is inversely proportional to the angle of arrival.

  • richard.moses 47670 (4/22/2011)


    I will say that he SOL Server server level time out option is set to 21600 seconds which is 6 hours. Six hours is more than enough time.

    There's no such thing as a SQL Server level query timeout. 1

    Timeout is purely a client application concept. It's purely and only the client that decides how long it is willing to wait. SQL Server is quite happy to run queries forever without stopping them.

    (1) There's remote query timeout which affects the time SQL (acting as a client) is willing to wait for a query that it has submitted to a remote server. There is lock timeout which is the amount of time SQL will let a query wait for a lock.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks for you help. Hopefully I can narrow down the issue or at least get SQL out of the equation after doing some additional monitoring. Process of elimination.

  • Thanks for you help.

  • I suspect the problem is that the queries in SQL are inefficient and are causing the application to time out. Maybe time for some optimisation.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • you can define REMOTE QUERY TIME OUT and REMOTE LOGIN TIME OUT in SQL Server and also in SQL Server SSMS query analyzer

    Regards,

    Syed Jahanzaib Bin Hassan

    MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog

    http://www.aureus-salah.com

    Regards,
    Syed Jahanzaib Bin Hassan
    BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    www.aureus-salah.com

  • Syed Jahanzaib Bin hassan (4/23/2011)


    you can define REMOTE QUERY TIME OUT and REMOTE LOGIN TIME OUT in SQL Server

    Which are used when SQL runs a remote query, eg. OPENQUERY, OPENROWSET, they are not used for queries that SQL runs locally.

    and also in SQL Server SSMS query analyzer

    Which is the timeout for queries run via Management Studio only, not other applications.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 14 posts - 1 through 14 (of 14 total)

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