August 13, 2013 at 1:49 am
Hello - having a performance issue narrowed to within a few rpc calls via sp_executesql. Please - no recommendations to change the code - I don't own it and it won't happen soon. In the meantime, I have:
SET NOCOUNT ON
BEGIN TRY
BEGIN TRAN;
EXEC [dbo].[PROC1] 155544325,83876715...
EXEC [dbo].[PROC2] 1055,''24000/HC213''
EXEC [dbo].[PROC3] 155544325,0,5729925....
COMMIT TRAN
EXEC [dbo].[PROC4] @ThingyID_3 out,@ExtPersonId_3 out...
EXEC [dbo].[PROC5] @ThingyID_3,@FixClThingyID_4 out...
EXEC [dbo].[PROC6] @AccountID_3,@ClientID_5 out...
EXEC [dbo].[PROC7] @ClientID_6 out,155544325...
EXEC [dbo].[PROC8] @ThingyID_3,@ToOpenClose_7 ...
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
rollback;
DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT;
SELECT @ErrorMessage = dbo.GetErrorInfo(''''),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
if @ErrorState = 0
set @ErrorState = 1
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;
This is just one example - but one of these procs loses it now and then - and I'd like to get a handle on what the durations, reads, etc are that will spike execution times for a spell. The problem I see is profiler could never provide such granularity. Its 2012 instance, so I have events with more options. Wondering if someone has seen this so I can fast track a solution.
August 13, 2013 at 5:53 am
in profiler what is the exact thing you miss?
what are the events you tried to find the performance timings?
without code of SP and tables structues involved, no one can suggest more.
Regards
Durai Nagarajan
August 14, 2013 at 12:25 am
Yes, that was poorly posed on my part. The RPC call using sp_executesql takes place - so RPC Started and RPC Completed will return metrics (Reads, Duration eg) for ALL the procs bundled in the RPC call. But anyone of the procs is having a problem. So would need to create another trace to capture SP:Completed, but that EventClass doesnt have Reads, which I am most interested in. I then have to include SP:StmtCompleted to that but all the sudden my trace def is more verbose than I would like.
So, I was hoping to be honest that someone knew that this could be done via Extended Events in a more graceful way. Basically there is one parent event which fans out into all these child events. They are all related but cross the fabric of the trace engine in different ways making them unrelated all the sudden. There has to be a way. In the meantime, I am doing what I mention above, but perhaps others have had a similar problem and resolution
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply