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 2012 Forums
 Other SQL Server 2012 Topics
 Defining a trace for sp_executesql
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Bukester
Starting Member

3 Posts

Posted - 08/13/2013 :  05:55:30  Show Profile  Reply with Quote
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.

tkizer
Almighty SQL Goddess

USA
37132 Posts

Posted - 08/13/2013 :  11:39:27  Show Profile  Visit tkizer's Homepage  Reply with Quote
We can't help without seeing the code of the stored procedures.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Bukester
Starting Member

3 Posts

Posted - 08/14/2013 :  02:35:29  Show Profile  Reply with Quote
I think I posed this topic poorly. Let me try again. What I am wondering is if there is an expert on extended events who could quickly define a trace to capture an RPC call which you see in my first post AND all if its child events. It may not be possible to do. The RPC call above uses sp_executesql - 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, the actual code is not relevant for this discussion. I dont want to optimize code - I just want to identify the offending child procedure. In fact this nests down to a few levels but even getting one level down would be enough.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37132 Posts

Posted - 08/14/2013 :  12:05:52  Show Profile  Visit tkizer's Homepage  Reply with Quote
Just add all of those events to a trace and run it server-side and not through Profiler. There is very minimal impact to server-side traces. You can convert it to Extended Events using Jonathan Kehayias' converter. That seems to be the easiest way to create an XE session as the learning curve is so big as compared to server-side traces, in my opinion. I'm actually writing a chapter on this for an upcoming book.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

Bukester
Starting Member

3 Posts

Posted - 08/15/2013 :  04:52:39  Show Profile  Reply with Quote
Yes , all of this is assumed SS trace. If I had to rewrite this entire post - it would come down to - do EE's have ability to capture SP:Completed Reads? Thats the root question.

Hmmm. An EE converter? That sounds like something I should look into asap!

Thank you Tara. Good luck with the book.
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.06 seconds. Powered By: Snitz Forums 2000