April 27, 2009 at 10:45 pm
One of my clients yesterday approached me
and complained about SQL Server "long running queries".
They deployed a new version of ASP.NET application
yesterday and now users randomly having errors on their website.
When I actually asked them to show me the errors
(it is captured by their application)
I only saw general
"user freeuser
timeout ...."
error without any OleDb or anything related to SQL.
Question
--------
If there is SQL Server timeout error will it be captured in SQL Server Log
or you need to configure SQL to start capturing this type of things?
How do I prove to the user it is not SQL related timeout?
April 27, 2009 at 11:12 pm
Check whether they have specified any particular amount of time for query timeout.
April 27, 2009 at 11:20 pm
You mean in their .NET code?
Like CommandProperty.Timeout ?
April 28, 2009 at 1:32 am
I mean SQL Server property.
I dont have any idea about .net but if something like timeout is there you can also look into that...
April 28, 2009 at 2:46 am
riga1966 (4/27/2009)
If there is SQL Server timeout error will it be captured in SQL Server Logor you need to configure SQL to start capturing this type of things?
How do I prove to the user it is not SQL related timeout?
SQL Server has no concept of a timeout. A timeout is a client application setting. A client app will have a specific timeout set for queries, default for .net is 30 seconds. If a SQL query hasn't finished in 30 sec, the client app will send SQL a message asking for the query to be aborted and then it will give a timeout error back to the client.
Two ways to fix timeouts.
1) Change the timeout setting in the client app. That usually requires code changes.
2) Optimise the queries so that they run in less time.
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
April 28, 2009 at 3:03 am
Hi Gila,
Please correct me if I am wrong
What is the remote query timeout in SQL Server properties?
April 28, 2009 at 3:10 am
From Books Online:
Use the remote query timeout option to specify how long, in seconds, a remote operation can take before Microsoft SQL Server times out. The default is 600, which allows a 10-minute wait. This value applies to an outgoing connection initiated by the Database Engine as a remote query. This value has no effect on queries received by the Database Engine.
So, in other words, this value is used when SQL Server is acting as a client for a query running somewhere else, usually another database server. It acts the same way as the timeout in a client app, the SQL Server that's initiating the query waits a certain time for the remote machine to finish and, if it hasn't, sends a cancellation and returns a timeout.
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
April 28, 2009 at 3:13 am
Thanks Gila...You are really g8 🙂
April 28, 2009 at 8:33 am
You are also great ratheesh.:-D...............pulikuttaaaaaaaaaa:-P
[font="Comic Sans MS"]+++BLADE+++[/font]:cool:
April 28, 2009 at 9:02 am
I think I need to see the screenshots with the error.
Because their Application log
is very primitive.
I personally understand that it is most likely the client that
timeouts. Now the problem is to figure out what SQL causes these timeouts.
Or maybe it is caused by something else - .NET connection, ADO issues...
April 28, 2009 at 9:18 am
riga1966 (4/28/2009)
Now the problem is to figure out what SQL causes these timeouts.
Try running a trace.
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
April 28, 2009 at 9:30 am
if you set up a profiler trace then you should be able to capture this
just remember to set "RPC/statement/batch starting" and RPC complete events in the trace - if you only capture complete then you won't get the database sql that is timing out
MVDBA
April 28, 2009 at 9:34 am
I did.
I started two traces yesterday.
But apparently one of them caused disk space issues on C: drive on production server. (Oops!)
Actually it is interesting.
When you run a trace for 15 hours and you expect about
2 million rows there what location is being used for temporary storage
of that Profiler results.
I knew about SQL Profiler.
I am just preparing myself for the situation when
I do not find any errors in the SQL Profiler results.
Then I need to prove it is .NET code problem.
They actually use Microsoft LINQ as a data layer.
I will read about it now.
Maybe this causes problems.
Never heard about it.....
April 28, 2009 at 9:50 am
See the SQL Profiler EVENTS I chose.
Is this going to be sufficient to capture TIMEOUTS...
See attachment...
April 28, 2009 at 9:55 am
as i said you need RPC starting as well -
if you know this is going to be a stored proc then you can remove batch starting and t-sql starting and just keep RPC starting and RPC complete
MVDBA
Viewing 15 posts - 1 through 15 (of 40 total)
You must be logged in to reply to this topic. Login to reply