August 2, 2021 at 2:57 pm
892717952 wrote:Jeffrey Williams wrote:What event are you capturing in your profiler trace?
the events I captured as below
Stored Procedures RPC:Completed SP:StmtCompleted TSQL SQL:BatchCompleted
So which row in the trace are you looking at for the read values? The RPC:Completed event captures everything that happens in the stored procedure. SP:StmtCompleted captures each statement within the stored procedure - and SQL:BatchCompleted is everything that happens within the batch.
2. SQL:BatchCompleted is everything that happens within the batch,what does the batch mean? is it the sql statement in the begin and end?
3. the attached file is the profiler data I captured, but the same numbers of reads for those two sql statements .
August 2, 2021 at 3:04 pm
- if one stored procedures includes many pieces of SQL Statements and when the stored procedure is called , then The Reads of RPC:Completed includes all the reads of the SQL statetments in the stored procedure?
2. SQL:BatchCompleted is everything that happens within the batch,what does the batch mean? is it the sql statement in the begin and end?
3. the attached file is the profiler data I captured, but the same numbers of reads for those two sql statements .
"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
August 3, 2021 at 3:28 pm
892717952 wrote:
- if one stored procedures includes many pieces of SQL Statements and when the stored procedure is called , then The Reads of RPC:Completed includes all the reads of the SQL statetments in the stored procedure?
2. SQL:BatchCompleted is everything that happens within the batch,what does the batch mean? is it the sql statement in the begin and end?
3. the attached file is the profiler data I captured, but the same numbers of reads for those two sql statements .
- If a procedure has a single statement, then the procedure and the statement will have the same number of reads when captured using the events you used. You're seeing normal behavior.
- A batch is any command not run through the remote procedure call mechanism. What's in the batch are each of the statements within the call. Note, you can call a procedure through a batch command. When you run a query through SSMS, you're issuing batch statements.
- Yep.
Thank you for your kind help, Grant Fritchey!
August 3, 2021 at 3:41 pm
Here's a code sample showing how C# calls a stored procedure. Compare that to 'EXEC dbo.MyProc' in SSMS. One is a remote procedure call. The other is a batch command. Both are ways to call stored procedures, but one is programmatic, the other isn't.
Does that help?
"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
August 4, 2021 at 12:54 am
Here's a code sample showing how C# calls a stored procedure. Compare that to 'EXEC dbo.MyProc' in SSMS. One is a remote procedure call. The other is a batch command. Both are ways to call stored procedures, but one is programmatic, the other isn't.
Does that help?
thank you !
I know there are 2 ways to execute SQL Scripts, one is to be called in the front end ( called by the front application , such as, c#/vb.net/Java/python...) , the other is to be called in SSMS( maybe on the sql server local server, maybe not), I know the first approach which it is RPC called, but I don't know if there is any way of RPC to called sql script. thanks
August 4, 2021 at 12:37 pm
I don't understand what you're asking. You can make batch calls from code, almost exactly the same as calling procedures. You just change the command type. SSMS is also a front end, same as any other. It just exclusively uses batch calls from the query window. However, it makes all sorts of RPC calls from the interface itself.
"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
August 5, 2021 at 12:32 am
I don't understand what you're asking. You can make batch calls from code, almost exactly the same as calling procedures. You just change the command type. SSMS is also a front end, same as any other. It just exclusively uses batch calls from the query window. However, it makes all sorts of RPC calls from the interface itself.
Thank you for your warm help!
I will try to do some test to understand it ,big thanks!
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply