5

Background - I have a website & a windows scheduled job which are a part of an MSI and get installed on the same server. The website is used by the end-user to create some rules and the job is scheduled to run on a daily basis to create flat files for the rules created by end-user. The actual scenarios are way more complex than explained above.

Problem (with the website) - The website is working fine most of the times, but some times it just wont load the rule creation page - and the exception being logged it 'query timeout or SQL server not responding'

Problem (with the job) - The job is behaving just like the website and fails some times with the exception - 'query timeout or SQL server not responding'

What I've tried -

  • I've added 'Connection Timeout' to the SQL connection string - doesn't seem to help with the logging - which would tell me if it was a SQL connection timeout or a query timeout.
  • I've also run the stored procedures which are called by the website & job - and ALL the stored procedures complete well within the business defined timeout of 3600 seconds. The stored procedures actually complete in under a minute.
  • I've also run SQL profiler - but the TRACES also didn't help me - though I could see a lot of transactions but I couldn't justify something being wrong with the server.

What I seek - Are there any other reasons which could cause this? Is there something which I could look for?

Technology - SQL Server 2008 R2, ASP.Net, C#.Net

Restrictions - The code details can't be revealed due to client confidentiality, though I'm open to questions - which I'd try to answer keeping client confidentiality in mind.

Note - There is already a query timeout (3600s) & Connection Timeout (30s) defined in the applicaiton config file.

Suyash Khandwe
  • 386
  • 3
  • 11
  • Without knowing MS SQL: I would suggest to increase the logging verbosity and monitor this behaviour. – Samuel Jan 13 '14 at 07:56
  • Are there specific times when the queries/job are slow, or is it random? There's really not enough info above to go with, by the way. It'd help if you could post parts of the code with the business logic or confidential things removed. – SchmitzIT Jan 13 '14 at 07:58
  • 1
    @suyashKhandwe Check If you are disposing your connection object properly. – Suraj Singh Jan 13 '14 at 07:59
  • Can you clarify whether you're talking about a connection timeout or a command timeout? The question implies the latter, the title the former. If it's a connection timeout make sure all your connections are being disposed of correctly. If it's the former you'll need to fire up ActivityMonitor – Liath Jan 13 '14 at 07:59
  • To answer a few question in the above comments - 1. It seems to be command timeout because some parts of the website/job work fine. 2. I'm confident that connections are being disposed correctly because I'm using Enterprise Library and don't have to manage connections manually. – Suyash Khandwe Jan 13 '14 at 09:36

4 Answers4

1

So, I tried a few things here and there and was able to figure out root cause -

The SQL stored procedure was joining 2 tables from 2 different databases - one of which had varying number of records - these records were being updated/inserted by a different (3rd party) job. Since the time of the 3rd party job and my job was not same - no issue came up due to table locks, but the sheer volume of records caused my job to timeout when my timeout was not enough.

But, as I said I've given the business standard command timeout of 3600 seconds - somehow Enterprise Library was overriding my custom timeout with its own default command timeout of 30s - and hence the C# code part would come throw an exceptions even before the stored procedure had completed executing.

What I did - This may be of help for some of us -

  1. I removed the reference of Enterprise Library from the project
  2. Cleaned up my solution and checked into SVN.
  3. Then cleaned up SVN as well.
  4. I didn't build the application after removing Enterprise Library reference - obviously it wouldn't build due to reference errors.
  5. After that, I took a clean checkout and added Enterprise Library again.

Now it seems to work even with varying number of records.

Suyash Khandwe
  • 386
  • 3
  • 11
1

Just had the same problem also yesterday. Had a huge query taking 18 sec in SQL Server but was running out in C# even after 200 sec. I rebooted my computer disconnect the DB and even disconnect the server... nothing changed.

After reading some threads, I've notice a common feed about indexes. So I removed some indexes in my database, put some back and voilà!. Back to normal.

Here's maybe I thought could had happened. While I was running some test, I probably still had some zombie connections left and my colleague was creating some tables in the DB at the same time and linked them to tables used in my stored procedure. Even if the newly created tables had nothing to do with the stored procedure, having them linked with the other ones seems to have messed up with the indexes. Why only the C# couldn't work properly? My guess is there a memory cache in SQL Server not accessible when connecting some place else than SQL Server directly.

N.B. In my case, just altering the stored procedure didn't have any effect at all, even if it was a common "solution" among some threads.

Hope this helps if someone has the same problem. If anyone can find a better solution/explanation, please share!!!

Cheers,

mathieu.blais06
  • 55
  • 11
0

I had similar problem with mssql and did not find any particular reason for this unstable behavior. My solution was to have the db re-indexed with

sp_updatestats

every hour.

Thomas Baumann
  • 111
  • 1
  • 7
  • On a side note if your DB needs it's indices rebuilt every hour you should take a serious look at your fill factor! – Liath Jan 13 '14 at 08:58
  • Re-indexing DB every hour may be too costly for our server resources - we deal with huge amounts of data (I'm talking of as high as 10 million rows of data in some of the tables) and indexing this much data every hour may slow down the DB – Suyash Khandwe Jan 13 '14 at 09:43
0

You can use WITH RECOMPILE in your stored procedure definition to avoid the error of 'query timeout or SQL server not responding'

Here's the Microsoft article:
http://technet.microsoft.com/en-us/library/ms190439.aspx

Also see this for reference:
SQL Server: Effects of using 'WITH RECOMPILE' in proc definition?

Sample Code:

CREATE PROCEDURE [dbo].[sp_mystoredproc] (@param1 varchar(20) ,@param2  int)   
WITH RECOMPILE   
AS   
... proc code ...
Community
  • 1
  • 1
Ish Goel
  • 315
  • 1
  • 7
  • 16
  • 2
    This didn't work - the reason most probably behind this could be that the problem lies within my C# code and not in the SQL part. – Suyash Khandwe Jan 13 '14 at 09:38