June 30, 2020 at 3:37 pm
Hi All,
I was asked to map all ojects that a specific group of users access in our DBs. I've created an extended Events session and captured all of their batch completed and rpc_starting. When I started to analyze the data, I was surprised to see that rpc_started event gave me sql text and statements which do not match. In some cases sql_text displayed a query on one table, and statement showed a query on a different table. In other cases the statement field showed a stored procedure that was executed by the user, while the sql_text showed a select query from a table (I checked the SP, that select statement was not a part of it). I've tried to look in the internet for explanations about the meaning of those fields but couldn't find any. Can someone help me understanding the meaning of those two fields?
July 1, 2020 at 12:12 pm
You're looking at two very different things. sql_batch_completed is for calls that come in as a batch command and have completed. rpc_starting is for calls that come in from a remote procedure call and are starting. These are not the same. Also, you're looking at one type of event completed and one type of event starting. You need to match up the pairs here, or, switch to a different event. Either, you're looking for sql_batch_starting and sql_batch_completed, or, rpc_starting and rpc_completed, or alternatively, as is the case most of the time for most people in most circumstances, you want sql_batch_completed and rpc_completed. That last thing gives you all batch commands and all remote procedure calls. Although, understand that, a batch and a procedure call are different. You don't really compare or conflate the two. It's part of why they have two different values.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 2, 2020 at 6:04 am
Thanks for your reply, I think I need to explain my question. I need to see to which objects in DB a specific group of users access, this is why I collected both batch and RPC. I'm interested only in the objects they access not failures or success. I'm not trying to compare the rpc_starting to the batch_completed, I use them both for composing the list of objects that they access.
The question I asked was about rpc_starting. this event has 2 fields: statement and sql_text. there is a big difference in the content of those fields. In some records, the statement field shows an SP running while on the very same event. the sql_text field shows a select (which is not a part of the SP that statement shows). So my question is how can I understand the conflict between statement and sql_text on the same event?
July 2, 2020 at 10:41 am
The rpc_starting event has a "statement" event field. That is the remote procedure call itself. It will include the procedure name, the parameters passed in, and the values passed to those parameters. That's the only field it has. It sounds like you've added the Action, "sql_text". That's not necessary in order to capture the information within rpc_starting. It's possible that the Action is capturing some additional statements different from the statement. Don't capture it.
If you want to understand the objects accessed from a remote procedure call, you simply go to the stored proc.
Although, if it's a prepared statement, say from an ORM tool, I'm actually not sure where to go to look at that... Hmmm... Research or testing may be needed here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 2, 2020 at 1:14 pm
A little testing later, the answer to my own question is answer is sp_statement_starting/completed and filter it by the Type to 'Prepared'. You can see the full query of prepared statements that way.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 3, 2020 at 9:28 am
Thank you very much. I highly appreciate your help and advice.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply