SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 SQL Timeout
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AlphaMan
Starting Member

United Kingdom
3 Posts

Posted - 08/20/2013 :  07:30:01  Show Profile  Reply with Quote
Hi,

We have a very unusual problem. Hopefully someone may have some ideas.

We support a bespoke browser based sales system built using asp.net with a SQL Server 2008 back end.

There is a particular SQL statement that periodically times out when being run from the browser based sales system. I can replicate it timing out and when a SQL Server Profile is run it records the SQL statement with a duration of 29995 (the 30 second timeout period). So it's definately hitting the SQL Server.

If I then copy and paste the SQL statement into a query in SQL Server Management Studio it runs fine and well within the 30 second timeout period?!? Once this is done if the same SQL statement is subsequently run using the browser based system it does not time out?!? It's as if running it as a query has cleared something.

Obviously running the SQL statement using asp.net uses the .NET SQL Data Provider and running it in a Query I guess doesn't. That's the only difference i can see.

Does anyone have any ideas? We've restarted IIS to no avail. Next time it happens, we're going to restart the SQL Server to see if that sorts it.

Many thanks in advance.

Cheers,

Rhydian.

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 08/20/2013 :  08:18:26  Show Profile  Reply with Quote
quote:
Originally posted by AlphaMan

Hi,

We have a very unusual problem. Hopefully someone may have some ideas.

We support a bespoke browser based sales system built using asp.net with a SQL Server 2008 back end.

There is a particular SQL statement that periodically times out when being run from the browser based sales system. I can replicate it timing out and when a SQL Server Profile is run it records the SQL statement with a duration of 29995 (the 30 second timeout period). So it's definately hitting the SQL Server.

If I then copy and paste the SQL statement into a query in SQL Server Management Studio it runs fine and well within the 30 second timeout period?!? Once this is done if the same SQL statement is subsequently run using the browser based system it does not time out?!? It's as if running it as a query has cleared something.

Obviously running the SQL statement using asp.net uses the .NET SQL Data Provider and running it in a Query I guess doesn't. That's the only difference i can see.

Does anyone have any ideas? We've restarted IIS to no avail. Next time it happens, we're going to restart the SQL Server to see if that sorts it.

Many thanks in advance.

Cheers,

Rhydian.

Does the query send/receive lot of records? If so, the network bandwidth may be playing into the total time, and hence into the timeout. Even if that were not the case, the time taken each time you run the query can vary depending on what else is going on on the system at the time.


You can increase the command timeout in ASP.Net. http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout.aspx
Go to Top of Page

AlphaMan
Starting Member

United Kingdom
3 Posts

Posted - 08/22/2013 :  04:14:33  Show Profile  Reply with Quote
Hi James,

The SQL is fairly complex but doesn't return that many records most of the time (say around 100).

It's just weird that running the SQL is SSMS seems to then allow the SQL run through the browser based system to run.

We could as you suggest increase the timeout but would rather get to the bottom of why this is happening first.

Rhydian.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 08/22/2013 :  08:51:22  Show Profile  Reply with Quote
Is this using a LINQ to SQL query? LINQ to SQL can sometimes generate very poor execution plans depending on how you structure the LINQ.

One thing you might try, if you have not already, is to run the SQL Profiler to see the query text that is coming through to the server, copy that and run it from SSMS.

Running a query from .Net should not add any significant overhead to the query compared to running it from SSMS. So the root cause of the problem may lie somewhere else.
Go to Top of Page

lazerath
Constraint Violating Yak Guru

USA
337 Posts

Posted - 08/22/2013 :  10:12:46  Show Profile  Reply with Quote
Hi AlphaMan, I've seen queries generate wildly different plans if the app is connecting with non-default connection settings (ANSI_WARNINGS OFF, ARITH_ABORT OFF,CONCAT_NULLS_EQUAL_NULL OFF, etc...). Take a look at what is coming through from your app layer and try those same settings in SSMS.
Go to Top of Page

AlphaMan
Starting Member

United Kingdom
3 Posts

Posted - 08/23/2013 :  06:50:16  Show Profile  Reply with Quote
Hi all,

Just to update you...

If I run DBCC FREEPROCCACHE on the SQL Server after the SQL has timed out and then re-run the SQL after, it runs fine.

So clearing the plan caching definately sorts the problem. I'm going to read up on plan caching a little further as have no experience with them at all.

Will keep you updated. If this info enlightens someone to explain why this would solve the issue, please let me know.

James: We use the asp.net GridView to show the returned data with a DataSource defined in asp.net.

We get the DataSource from the GridView control

Then we set the DataSource SQL and connection string

We then bind the DataSource and GridView

No idea what connection /provide asp:GridView uses…don't link it's LINQ. Is it?

Cheers,

Rhydian.
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
411 Posts

Posted - 08/23/2013 :  13:29:09  Show Profile  Reply with Quote
Try specifying "OPTION ( RECOMPILE )" on the SQL query. It may be that an earlier plan is being reused, and that plan performs very poorly for the current query values.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000