RPC:Completed in SQL Profile, but the data is not updated

Question

RPC:Completed in SQL Profile, but the data is not updated

Gamleur84

Default port

Points: 1475
More actions
January 23, 2019 at 6:41 pm

#416650

Hi,
I am facing a strange issue. I have the simple query executed from the application:
UPDATE T1 SET ALERTSPROCESSED=1,RECVERSION=1430426648 FROM BATCHJOBHISTORY T1 WITH ( INDEX(I_2271ALERTSPROCESSEDIDX), FORCESEEK) WHERE (ALERTSPROCESSED=0)
I started investigating this issue because it was identified as an expensive query (~50ms, lot of reads, writes and cpu time). This query is triggered by the application every 60 seconds.
During my investigation, I realized that ALERTSPROCESSED field is set to 0 for all 6000 rows in the table. I asked myself how that was possible since the query above should have updated all the rows to ALERTSPROCESSED = 1. I captured the trace in SQL Profiler and I caught the RPC:Completed and SP:StmtCompleted events for that specific query, everything looks good. How is this possible ? It must be an issue coming from the application, I don't understand how the data is not updated while SQL Profiler shows RPC:Completed and SP:StmtCompleted.
I ran manually the query in SQL Management Studio and it was successful and all rows were updated within a second. Now the read/writes/cpu/duration in SQL Profile are very low for this query, but it still does not update the value for new rows.
Any clue ?
Thank you

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323482 More actions · Answer 1

I would assume that some other process is toggling all rows back to zero, and not that SQL is not updating after it explicitly said it updated/Completed.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Gamleur84 Default port Points: 1475 More actions · Answer 2

Hi,

Are you thinking that another query would revert the change ? I can confirm there is not based on the SQL Profiler Trace.

Also, there is no trace of a rollback when I monitor from SQL Profiler or SSMS.

I understand that it unlikely that SQL does not update the rows, but that is really what I am seeing and trying to find the rational.

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 3

Based on the information, nothing but guesses. Differences in parameter values between your test and the actual. Differences in data between your testing and the actual. Differences somewhere are what normally will cause differences in behavior between two systems. Identify the differences and you'll likely solve the problem.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Davis H Right there with Babe Points: 722 More actions · Answer 4

Check all statements from the app, not just RPC. There could be a rollback. Is this in an Availability Group? Does the app's connection string have a READ_INTENT=TRUE? Does the app have error handling?

Check the connection properties for every thread executing from the app if you are not able to look at the app's DB config file.

These things don't "just happen". There is a logical reason.

Sergiy SSC Guru Points: 110192 More actions · Answer 5

Create a trigger to record values from inserted and deleted tables into a "trace" table.

Something like this:

Create trigger ....

for update

As

Insert into BATCHJOBHISTORY_ALERTSPROCESSED_trace

(TraceTime, ALERTSPROCESSED_old, ALERTSPROCESSED_new, pk_col)

Select GETDATE(), d.ALERTSPROCESSED, I.ALERTSPROCESSED, I.pk_col

From inserted I

Inner join deleted d on d.pk_col = I.pk_col

You may wish to add columns for APP_NAME(), user names, etc., so it gives you full info about the source of that reverse update.

_____________
Code for TallyGenerator

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

A couple of things come to mind as "possibilities".

1. You've verified that the database that you're looking at is the database the code is executing in, correct?
2. You've verified that there isn't more than 1 copy of the table in a different schema, correct?
3. Have you checked the table for triggers?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Gamleur84 Default port Points: 1475 More actions · Answer 7

Hi

Thank you for all the input.

I know it seems that I am not looking at the right thing or I have the wrong parameters, but I doubled check and asked a co-worker.

I also agree that these things happen for a reason.

For now, I will try the trigger as suggested and also capture all events in SQL Profiler

Gamleur84 Default port Points: 1475 More actions · Answer 8

Hi

From SQL Profiler, I can see there is a Rollback Transaction Starting and Rollback Transaction Started. I suspect that is the cause of this issue.

It is difficult to match the roll back transaction event with a specific transaction, there is not much information provided by the SQL Profiler... i might need to use Extended Events

Gamleur84 Default port Points: 1475 More actions · Answer 9

Would it be correct to say that RPC:Completed and SP:StmtCompleted are tied to a specific query, but the whole transaction can roll back later ? Is there any way in SQL Profiler that I can tell the transaction id of a query ?

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 10

Gamleur84 - Friday, January 25, 2019 12:52 AM
Would it be correct to say that RPC:Completed and SP:StmtCompleted are tied to a specific query, but the whole transaction can roll back later ? Is there any way in SQL Profiler that I can tell the transaction id of a query ?

Yes. The statement can complete and also get rolled back.

Look at causality tracking in Extended Events. That's the easiest way to tie together a whole bunch of disparate events. There's nothing like it in Profiler/Trace.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

Gamleur84 - Friday, January 25, 2019 12:10 AM
Hi
Thank you for all the input.
I know it seems that I am not looking at the right thing or I have the wrong parameters, but I doubled check and asked a co-worker.
I also agree that these things happen for a reason.
For now, I will try the trigger as suggested and also capture all events in SQL Profiler

No problem. Had to ask the questions to eliminate the possibilities. I think you're on the right track with the "Rollback" discovery that you've made.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions