SQL Server Profiler - Why are some calls RPC:Completed and Some Calls SQL:BatchCompleted

Question

I will be the first to admit that I often get confused when I use SQL Server profiler.

Having said that, I decided to fire it up to see the SQL that was being generated by experimenting with the Include method of a Db Set. I was going through the music store example where there are Albums, Artists, and Genres.

One thing I noticed was that some calls had an event class of SQL:BatchCompleted while others had an event class of RPC:Completed. It seemed like the lazy load calls were being traced under the RPC event class.

What is the difference was between these two event classes and why does lazy loading result in an event class of RPC:Completed?

Sankey

Answer 1

BatchCompleted means TSQL code (e.g. selects) have completed. RPC:Completed means stored proc has completed. It could be that EF executes SQL code dynamically using sp_executesql so you get RPC:Completed.

Bradfield

Answer 2

It's all about parameterization. It runs as a SQL Batch when there are no dynamic parameters and runs as an RPC when there are. This setup allows for optimal reuse of the query plan.

See: https://blogs.msdn.microsoft.com/bindeshv/2010/07/12/ef-query-execution-pattern-usage-of-sp_executesql-vs-direct-execution-of-sql-statement/

Aryl