Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bukester
Starting Member
3 Posts |
 Posted - 2013-08-13 : 05:55:30
|
| 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 ONBEGIN TRYBEGIN TRAN;EXEC [dbo].[PROC1] 155544325,83876715...EXEC [dbo].[PROC2] 1055,''24000/HC213''EXEC [dbo].[PROC3] 155544325,0,5729925....COMMIT TRANEXEC [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 TRYBEGIN CATCHIF XACT_STATE() <> 0rollback;DECLARE @ErrorMessage NVARCHAR(4000), @ErrorSeverity INT, @ErrorState INT;SELECT @ErrorMessage = dbo.GetErrorInfo(''''),@ErrorSeverity = ERROR_SEVERITY(),@ErrorState = ERROR_STATE();if @ErrorState = 0set @ErrorState = 1RAISERROR(@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
38200 Posts |
|
|
Bukester
Starting Member
3 Posts |
Posted - 2013-08-14 : 02:35:29
|
| 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. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2013-08-14 : 12:05:52
|
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 KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Bukester
Starting Member
3 Posts |
Posted - 2013-08-15 : 04:52:39
|
| 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. |
|
|
|
|
|
|
|
|
Defining a trace for sp_executesql