3

When watching results in SQL Profiler, does the RPC Complete include time to send results to the client? Or is it just the time the server spent gathering the records?

The reason I'm asking is I'm working on a program to move a large amount of data. The databases are cloud hosted in Azure, and the code is (currently) running locally. I can see the RPC call times in SQL Profiler, and the query to select the rows isn't cheap (massive table filtered by an indexed column then sorted by the table's primary key for paging results), and it isn't an insignificant amount of data (100k records per page, some situations I'm moving up to 60 million records per run).

I'm curious if I should expect it to run faster once the code is running inside Azure. If the RPC complete timing includes the time spent for the data to be sent to the client, this should decrease some (I'm hoping dramatically). If the RPC complete timing is just the time for the server to select the data, then I won't see much of an increase when it's running inside Azure.

6

Network I/O is included in the rpc_completed duration*, so I would expect you'll see improvement in the workload you've described.

I enabled TCP/IP on my local SQL Server 2016 instance, and then ran a series of queries through a .NET application that uses an ORM. Here's a comparison of sp_statement_completed and rpc_completed Extended Events targets for the exact same queries in that test run:

screenshot of Extended Events results

Each set of two rows is the two events for that same query.

Notice that rpc_completed is significantly higher than sp_statement_completed each time, since the latter doesn't include the network time (it's just how long a statement in a batch took to complete on the server).

For laughs, I then turned off TCP/IP, so that the queries would go through the named pipes provider:

screenshot of the second XE test run

Notice that rpc_completed is still higher, but the difference is significantly less, since there's no TCP overhead going on - the "round trip" is all in-process.

*My testing was done with Extended Events, but I would suspect the same to hold true for Profiler


To be clear, network time can be included in sp_statement_completed as well in the case of "streaming plans" (thanks to David Browne for pointing this out).

Sometimes, especially simple queries with lots of results, the results aren't spooled for sending after the statement is complete. Instead the results are sent as the plan runs. This is sometimes called a "streaming plan", and it's what you'll get with a simple nested loop join or select * from a large table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.