Profiling a specific stored procedure
-
I have a stored procedure that doesn't always do the update I expect it to, so I want to set up a Profiler trace. I don't care about the events from any other stored procedure, function, or SQL statement. The SP I want to trace has some input parameters, so I'd like to see them. It has an output parameter, so I'd like to see that. It uses dynamic SQL, so if there's something specific I should be looking at related to that, I'd like to see that as well. I'd like to see that it starts, and that it finishes.
I've tried restricting by TextData and ObjectName, but I'm still getting way too many events, and absolutely killing the application's response time. So my question is, what events, data columns, and filters do I need to accomplish this?
Thanks in advance for any help on this. It's frustrating enough having a stored procedure not work.
Mattie
-
have you tried running the debugger on the proc (query analyser, right click in the proc/debug)?
-
Remi,
Thanks for responding. As for using QA, I have, and it runs fine. In fact, it runs fine most times it executes in my application. The problem seems, at least to me, very similar to the one described in http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=186841, so I was really hoping for a resolution to that one, but no such luck.
Mattie
-
Ya it's a shame that we didn't hear from him again. But I'm not sure you understood what I meant bu running the debugger on the proc. Are you aware that there is a debugger that can run the proc line by line just like in vb?
If you know with which parameters it fails, then you can copy that proc under a new name and debug it on the server (unless you already have a test server for this stuff).
Then you can add print statements too see which parts on the proc is being run and what are the variables value.
Or you could paste it here so we can have a look.
-
No, I understood about running it thru the QA debugger. It doesn't fail there. In fact, after it has failed in the application, and the user logs out of the app, then logs back in, and presses the same button, it works fine. And I've never been told that it fails when the user executes it the first time.
Now this is probably going to give Frank Kallis a heart attack, but here's what the stored procedure does. It passes in a record number and a string that contains the SET statement for the update. Before it does the update, however, it inserts the existing data into a history table, using more dynamic SQL. If that's successful (which doesn't seem to be the case when this process doesn't work), it then does the update. If the update is successful, it commits the transaction, otherwise it rolls it back.
I'm going to follow this post with another one that contains the code, slightly obscured.
Mattie
-
Let me send an ambulance to Frank's house before you post this...
I'll be waiting for mine here on the floor.
-
Here's the procedure, and don't claim you weren't warned. Let me also make the following points. First, I now know that this couldn't violate more rules of stored procedures than if that had been what I set out to do. Two, there's nothing I can do about it right now. Three, god-awful as it is, it does exactly what I need it to do, when it decides to execute, which is almost all of the time. And finally, yes, I'm being very defensive, but please don't take it as a reflection on you. I'm very grateful for whatever help anyone can offer.
CREATE PROCEDURE stp_TableUpdate
@MyUID nvarchar(10),
@MySet nvarchar(1600),
@ArchiveInd int,
@ArchivedBy nvarchar(20),
@ArchivedOn nvarchar(1000),
@HistoryIdentity int output as
BEGIN
--all processes
DECLARE @ReturnError int
DECLARE @sql nvarchar(2100)
DECLARE @SQLSetError nvarchar(100)
DECLARE @SQLSetCount nvarchar(100)
--update process
DECLARE @SQLUpdate nvarchar(1000)
DECLARE @SQLWhere nvarchar(100)
DECLARE @UpdateParmDefinitions nvarchar(200)
DECLARE @UpdateReturnError int
DECLARE @UpdateRecordCount int
--insert process
DECLARE @InsertParmDefinitions nvarchar(100)
DECLARE @HistoryRecordCount int
DECLARE @SQLInsert nvarchar(1000)
DECLARE @SQLSelect nvarchar(1000)
DECLARE @SQLIdentitySet nvarchar(100)
DECLARE @ParmValues nvarchar(200)
SET @ReturnError = 0
BEGIN TRAN
IF @ArchiveInd = 1
BEGIN
SET @SQLInsert = 'INSERT INTO MyTableHistory
(ServiceUID, ServiceDate, Counter, ServicePeriod, ServiceType, ServiceReason, ClaimedMiles, ServiceHours, ServiceDesc, Remarks, ServiceAmt, MileageAmt, MileageTaxableInd,TollsAmt, FY, PayPeriod, SchedulingUID, Status, StatusBy, StatusOn, CreatedBy, CreatedOn, ModifiedBy, ModifiedOn, CancelledBy, CancelledOn, CancelledDesc, AuthorizedBy, AuthorizedOn, PaidOn, TravelFrom, ArchivedOn, ArchivedBy) '
SET @SQLSelect = 'SELECT UID, ServiceDate, Counter, ServicePeriod, ServiceType, ServiceReason, ClaimedMiles, ServiceHours, ServiceDesc, Remarks, ServiceAmt, MileageAmt, MileageTaxableInd, TollsAmt, FY, PayPeriod, SchedulingUID, Status, StatusBy, StatusOn, CreatedBy, CreatedOn, ModifiedBy, ModifiedOn, CancelledBy, CancelledOn, CancelledDesc, AuthorizedBy, AuthorizedOn, PaidOn, TravelFrom, ArchivedOn = @ParmArchivedOn, ArchivedBy = @ParmArchivedBy ' + ' FROM MyTable WHERE UID = ' + @MyUID
SET @SQLIdentitySet = ' SET @ParmScopeIdentity = SCOPE_IDENTITY() '
SET @sql = @SQLInsert + @SQLSelect + @SQLIdentitySet
SET @InsertParmDefinitions = '@ParmScopeIdentity int output, @ParmArchivedBy varchar(20), @ParmArchivedOn datetime'
EXECUTE sp_executesql @SQL,
@InsertParmDefinitions,
@ParmScopeIdentity=@HistoryIdentity output, @ParmArchivedBy = @ArchivedBy, @ParmArchivedOn = @ArchivedOn
SELECT @HistoryRecordCount = @@RowCount, @ReturnError = @@Error
END
IF @ReturnError <> 0
OR @HistoryRecordCount = 0
OR @HistoryIdentity = 0
RETURN @ReturnError
SET @SQLUpdate = 'UPDATE MyTable '
SET @SQLWhere = ' WHERE UID = ' + @JSUID
SET @SQLSetError = ' SET @ParmUpdateReturnError = @@Error'
SET @SQLSetCount = ' SET @ParmUpdateRecordCount = @@Rowcount'
SET @sql = @SQLUpdate + @MySet + @SQLWhere + @SQLSetError + @SQLSetCount
SET @UpdateParmDefinitions = '@ParmUpdateReturnError int output, @ParmUpdateRecordCount int output'
EXECUTE sp_executesql @sql,
@UpdateParmDefinitions,
@ParmUpdateReturnError=@UpdateReturnError output, @ParmUpdateRecordCount = @UpdateRecordCount output
IF @UpdateReturnError > 0
OR @UpdateRecordCount = 0
BEGIN
SET @ReturnError = @UpdateReturnError
ROLLBACK TRANSACTION
END
ELSE
COMMIT TRANSACTION
RETURN @ReturnError
END
GO
-
Sorry for the delay, I just got released from the hospital.
Well first of all, you don't rollback the transaction after the first sp_executesql when there's an error. Is this what you want?
Maybe this will help you figure out the problem : right before the first return, I would insert the values of all the parameters used to set the @sql variable and the @sql variable into a log table. This would give you an idea of why this is failing.
Also is it possible that you blow past 2100 characters when you build the string (seems to me that if all the variables are maxed out, they could add up to more than 2100 characthers)?
-
Released from the hospital would be this icon I suppose.
I don't explicitly rollback the transaction after the first sp_executesql because I thought that exiting the SP without committing achieved the same thing. Is this an incorrect assumption?
As for the length, I don't think that's a problem, because what can be updated when the update doesn't occur is very limited. Under other circumstances, where it doesn't fail, the update can be much longer. And I would hope that returning the error code would give me a clue that something had failed, but it never does.
That said, I'm going to try your suggestion about the log table. Although I wonder about the logic of using an insert statement to determine why an insert/update statement doesn't work. (Like fighting fire with fire?)
Are there any good references on Profiler?
Thanks,
Mattie
-
Sounds like an assumption of code execution to me (like the order by should be by the primary key if none is specified)... Nothing good can come out of this with sql server (because there are way too many features than can change the "default behavior").
I can't really answer your auto rollback question on that end, but one of these 3 articles will surely be able too :
All about transaction Part III
I've never used the profiler to log the failure of code execution.. maybe someone else can shed some light on this process (if it's even possible).
-
BTW, the point of logging the statement is to be able to rerun it and see if it's a compile or execution error and then fix the problem... fix is hard to do when you have no idea of why it's failing, but I guess you had already figured that part out.
-
Remi,
Well, as always happens when I'm trying to isolate why something fails intermittently, everything they tried today worked. So I'll keep my log table going until the process fails, and then I'll see if I can spot a difference (parameters, execution time).
Thanks for your help. If I figure anything out, I'll post it.
Mattie
-
HTH... this might sound strange... but I hope it fails again
. -
Did you add the missing rollback too?
If yes, then try removing it too see if it fails again.
-
No, I haven't added in the rollback yet. Incredibly enough, it's more important to me to track what's happening when it fails than to have it not fail. So far, I'm much more frustrated with this problem than my users are.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply