the reads of SQL profiler is the number of SQL Server logical read?

  • Jeffrey Williams wrote:

    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.

    1. 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 .

    Attachments:
    You must be logged in to view attached files.
  • 892717952 wrote:

    1. 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 .

    1. 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.
    2. 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.
    3. Yep.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore Roosevelt
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Grant Fritchey wrote:

    892717952 wrote:

    1. 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 .

    1. 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.
    2. 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.
    3. Yep.

    Thank you for your kind help, Grant Fritchey!

    1. not clear what is remote procedure call mechanism, can we say it the sql statment  is called in the front end (not executed in the SSMS)?
    2. is there any post to explain the details of the difference of Event class? big thanks!

     

  • 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
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Grant Fritchey wrote:

    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

  • 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
    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Grant Fritchey wrote:

    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 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply