Post reply

Timeouts across multiple applications

  • August 31, 2010 at 10:23 am

    #226432

    Hi Fellows,

    Lately I've been struggling with having several applications giving SQL timeouts, the deal is that it all started out of the blue without apparent reason, during the times that the timeouts rise, I try to check for blocking sessions and blocked sessions and nothing arises, we use Idera SQL Diagnostic and all the counters looks green on these, also, if I try to move to the periods where the timeout reports were, there is nothing unusual.

    My question is, what else besides locks, CPU Utilization, Memory Utilization, Network (this one *looks* good) can also cause a timeout in multiple applications using different tables? there is not an specific time for this to happen, and it tends to be more in the facility where the server is than the overseas facilities, which makes no sense for me.

    The server is an 8-core CPU server with 32 GB of memory, we have a SAN that is used to save the data in an RAID 5+0 array, the network interface is a gigabit fiber interface, it does have SQL Server 2008 and Windows 2008 server; and as I've stated above, this all started like two weeks ago without any apparent reason, in Idera it all looks good, if I try SSMS during these periods, it comes back pretty quick.

    Thanks in advance for your help.

    Frank.

  • August 31, 2010 at 2:26 pm

    #1214592

    Maybe the default trace can provide additional info.

    http://www.mssqltips.com/tip.asp?tip=1739

  • August 31, 2010 at 2:49 pm

    #1214606

    Tried that... but then everything gets super slow. I run the trace on a server that we use for development, is in the same data center as the production server; nevertheless I will try again.

    Thanks.

    Frank.

  • August 31, 2010 at 2:50 pm

    #1214608

    Nevermind, I was thinking on SQL Profiler.

    Frank.

  • August 31, 2010 at 3:45 pm

    #1214623

    Won't be much, if anything, of use in the default trace. It's got a limited set of events.

    Run a server-side trace (Not using the profiler GUI) and have a go at optimising the worst performing queries. Or get someone in if it's urgent.

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

    http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

    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

  • August 31, 2010 at 10:33 pm

    #1214721

    Thanks, I will try that out.

    Frank.

  • September 1, 2010 at 1:31 am

    #1214770

    Some questions

    -Do you use link servers and join with many tables between database?

    -Have you got sql R2 on the server ?

    -What time are the index rebuilds jobs happening ? have you noticed the rebuilds taking longer with sql 2008 compared to sql 2005 ? Are the rebuilds done “on line” ? Are the timeouts encountered during the rebuilds ?

    -What is the recovery model : is it Full ? Are you encountered any replication latency?

  • September 1, 2010 at 8:00 am

    #1214980

    -Do you use link servers and join with many tables between database? no

    -Have you got sql R2 on the server ? no

    -What time are the index rebuilds jobs happening ? Every Sunday

    have you noticed the rebuilds taking longer with sql 2008 compared to sql 2005 ? no

    Are the rebuilds done “on line” ? no

    Are the timeouts encountered during the rebuilds ? no

    -What is the recovery model : is it Full ? Full

    Are you encountered any replication latency? no

    Frank.

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

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