0

I have a very peculiar problem and I'm looking for suggestions that might help me get to the bottom of it.

I have an application in .NET 3.5 (MVC3) on a SQL Server 2008 R2 database.

Locally and on two other servers, it runs fine. But on the live server there is a stored procedure that always times out after 30 seconds.

If I run the stored procedure on the database, it takes a couple of seconds. But the if the stored procedure is received by the application, then profiler says it took over 30 seconds. The same query the profiler receives, runs immediately if we run it directly on the DB.

Furthermore, the same problem doesn't occur on any of the other 3 local servers.

As you can understand, it's driving me nuts and I don't even have a clue how to diagnose this. The even logs just show the timeout as a warning.

Has anyone had anything like this before and where could I start looking for a fix?

Many thanks

tereško
  • 58,060
  • 25
  • 98
  • 150
Nick
  • 2,877
  • 2
  • 33
  • 62

1 Answers1

1

You probably have some locking taking place in your application that doesn't occur when running the query on the server.

To test this run your query in your application using READ UNCOMMITTED or the NOLOCK hint. If it works you need to check your sequence of calls or check to see whether your isolation level isn't too aggressive.

These can be tricky to nail down.

Eben Roux
  • 12,983
  • 2
  • 27
  • 48
  • That particular stored procedure is a bit intensive. It created a temp table, adds data onto it, updates them and then runs a select on the temp table and deletes them. I wonder if this is the root of all problems. And I wonder why it works well on other environments – Nick Dec 03 '12 at 14:48
  • Yeap.. this did it!! still not sure why only one server needs it out of many! Very peculiar but at least it works now – Nick Dec 03 '12 at 15:13