April 30, 2009 at 7:24 pm
post# 705980 Mike mentioned:
"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".
How do you know that particular rpc call got timeout based on your saved trace file?
Is it true that if you did capture the rpc starting event, but never got RPC:Completed, then this particular rpc call got timeout?
Pei Zhu
May 1, 2009 at 11:05 am
Hi VM,
You wrote:
In the "Events Selection" tab of the "Trace Properties" window, right click on Stored Procedures and click on 'Select Event Category'. You can find the "RPC:Starting" you are looking for.
But there is no such thing as 'Select Event Category" in SQL Profiler.
Only if you select template "TSQL_SPs" then I see
RPC:Starting.
The question is now
how do I know how to script this event?
Because it all looks like:
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
....
MSDN says 10 is eventid and 16/1/17 is columnid
(http://msdn.microsoft.com/en-us/library/ms186265.aspx)
I think I'm getting there.....
May 1, 2009 at 11:11 am
Part of this article goes over scripting a profile trace. I don't know why you want RPC:Starting. It's the competed events that have duration and other performance-related columns
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
May 1, 2009 at 11:18 am
Could someone answer the question how did you know the rpc call timeout (without completing) based on the trace?
May 1, 2009 at 12:28 pm
Hi Gail,
Mike Vessey on page 2 of this thread wrote:
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
Gail,
You think I don't need it?
May 1, 2009 at 12:54 pm
Personally when I'm checking performance of queries, I just use the RPC:Completed and T-SQL Batch Completed events. The starting events give no useful information and I find they just clutter the trace results.
Even if the app times out, there will still be a competed event. There has to be, it's not as if the query carries on running forever. (I have tested this)
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
May 4, 2009 at 4:00 am
Hi, if I understand your question, the only modification over your SQL server is the ASP .Net application. Random time outs can be for differents scenarios:
A) IIS web site configuration time out modification. You can correct this in the management console of IIS.
B) Application timeout. You can correct this in the web.conf file that is in the root folder of the client web application.
C) Random time outs from SQL can say us that the ASP .Net application query has a very long time for the application configured time out. In my opinion is not a problem with your SQL server.
NOTE: For debug you could try to execute ASP .Net SQL code locally in the SQL server management console. If you don't get a time out error your server is ok and the problem come from the VB .Net application.
May 5, 2009 at 2:35 am
from experience RPC starting is required if the Command timeout occurs and the command is killed before any results (even the 1st row) is returned.
Most of my monitoring for timouts I can manage by RPC complete , however in some circumstances the RPC complete never occurs so i find it best to include it for complete results (you can also kill the connection as soon as you run the command and avoid locking up your live database)
you may also find that some procs will ignore the command timeout and run for several hours (if your developers are as bad as mine)
I can't find any direct evidence of what it is that makes these ignore the ado.net command timeout, but they all seem to be using dynamic SQL with EXEC (@strsql) inside the proc.. I'm guessing that this is somehow sending a false message back to ado.net - but i have no proof
MVDBA
May 5, 2009 at 2:47 am
michael vessey (5/5/2009)
from experience RPC starting is required if the Command timeout occurs and the command is killed before any results (even the 1st row) is returned.
Shouldn't be. I did a quick test.
CREATE PROCEDURE Waiting AS
WAITFOR DELAY '00:00:45'
SELECT 'done'
GO
I then called that from a .net app with the command timeout set at 30 sec. After 30 sec I got an error from the app and an RPC:Completed event in profiler.
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
May 5, 2009 at 2:51 am
it's possible that ourprocs are actually erroring at some point rather than timing out - and our web app isn't handling the error correctly and just lazily displying a timout error
MVDBA
May 6, 2009 at 8:07 am
Gail, Michael!
You guys seem to be very close to the root of the problem.
Michael,
Actually you had a good point. I noticed there's a lot of
sp_executesql 'XXXXXXXXX' stuff. I would say most of SQL is like this.
Maybe Microsoft LINQ works this way?
Gail,
I tried "RPC:Completed and T-SQL Batch Completed events"
and it created 300,000 MB file in 11 min.
We cannot afford it. We only have 50 GB of free space of D:\ drive.
So I just left RPC:Completed and it's running for 13 hours now and is 4GB.
It will stop at 5PM today and then I will have to load it into SQL table (I hope it's not gonna be 15 million records)
Viewing 11 posts - 31 through 40 (of 40 total)
You must be logged in to reply to this topic. Login to reply