Question

I have a ongoing issue with my website, where it appears the W3wp.exe crashes and in turn anyone attempting to connect to the site will receive a SQL server timeout error.

The error is this...

System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

What actually happens is when this occurs, the CPU usage on the server maxes out to 100% and stays there. During this time if I try and connect to the homepage the site will appear to be loading and then timeout. This is also the case when i try and connect to the database through management studio. I get the above SQL timeout error from ELMAH when it emails me exceptions. When I do a iisreset I can once again connect to SQL server and the CPU usage drops immediately.

Now I have already posted a few questions oh here about the issue in regards to the SQL time out exception. So ill list what I have done and what im using...

• Im using Linq to SQL. • SQLCacheDependancy. • IIS 7.5 & SQL Server 2008 R2. • Disposing of all objects. • The call to the Linq to SQL dataContext is always wrapped in a USING Keyword.

These timeout errors occur infrequently, maybe once every 2 days, but today when I got up, my inbox was full of time out exceptions indicating one occurred overnight.

The other odd thing is that when for example it happened last night, it was around 4am, when I tried it this morning at 8am the site worked fine, so I appears that overtime the W3wp.exe, SQL server or both manage to either recycle or reset themselves.

Can anyone see what maybe causing this behaviour ? or point me in the right direction of what maybe the cause ? or perhaps even a possible solution ?

Any help is most appreciated

Truegilly

Answer 1

I would guess the timeout is because of the high CPU spike, not the other way round. If your IIS and SQL are on the same machine, try to find out the process (from task manager) which is hogging the processor.

If it is the web server, setup perform counters, especially the ones related to .NET Garbage collection, and see how frequently it is getting executed. Also check the Gen2 object size. You would have to collect this data starting from when it is running fine till CPU maxes out, and compare what is "growing" in the counters. Check if you have object tracking enabled in the data context, and also how long your using(datacontext){ ... } are.

If it is the sqlserver that is holding the CPU, then try to use the following Microsoft article to analyze the issue.

友情链接