XE sp_statement_starting event

Question

Hello!

The XE wizard "says" sp_statement_starting event should be logging the sp starting:

And it does log starting of SPs (object_type = PROC), but it also logs PREPARED events:

Q1) Why?
Q2) Can I get rid of them and see in the log ONLY starting of the stored procedures?

Regards,
Michael

Answer 1

So how exactly did you run the procedure? Typed an EXEC statement in a query window, selected it, and pressed EXECUTE?

It does not seem like that. Rather it seems that you used the Execute option from the context menu in Object Explorer. Whatever, these PREPARED queries are not from the execution of your stored procedure, but queries that SSMS ran behind your back. If you find the statement for your procedure, you will see that object_name has a value, and object_type is something else.

As for PREPARED, there are some variations on that theme, and I think that X-Events is somewhat inaccurate here. A prepared in the true sense, is when a client API calls sp_prepare with query and gets back a handle, so it can execute the same query many times without sending the query text. But SSMS is not using this, but it does send the query text every time. It appears that X-Events just lumps everything which is not a stored procedure into PREPARED.

Answer 2

sp_statement_starting is fired every time the execution of a stored procedure moves to the next statement.

If all you want to see is when the procedure starts, you should collect the event module_start.

Answer 3

"sp_statement_starting is fired every time the execution of a stored procedure moves to the next statement." - but anyway, as far as I understand this "next statement" must be the next statement inside the running stored procedure - not any other statement being run... why does this PREPARED events are getting logged if I hadn't used them in any SP?

Regards,
Michael

Answer 4

Hi @Mikhail Firsov ，

sp_statement_starting belongs to SP:StmtStarting Event Class, it indication a T-SQL statement within a SP has started

Object_type PREPARED is prepared statement, the prepared query caches an execution plan at first run and all subsequent executions of the query re-use this plan.

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

Hi Yufeishao-msft,

Firstly thank you for the explanation what the PREPARED object_type is, but does this -

"it indication a T-SQL statement within a SP has started"

...means that all those PREPARED events were initiated by the code inside the running SP? If yes it would sound strange to me as for this test I ran the simplest SP which does nothing more that Select * fom sometable...