Post reply

Logical Reads Using Sql Profiler

  • April 16, 2012 at 11:07 am

    #259324

    I have a Stored Procedure and I am trying to find out the total reads for the procedure before and after I add an index.

    If I run the statement Exec Stored Procedure I get reads of 35227

    If I run the query inside the stored procedure and add up all the reads I get quite a bit more reads. 93939

    When I add the index the first one stays the same but the second drops dramatically to 37123

    Can someone tell me why there is a difference? And what is the actual right way to look at this?

    thanks

  • April 16, 2012 at 12:36 pm

    #1474288

    Which readings in your OP go with which trace event?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • April 16, 2012 at 12:42 pm

    #1474292

    I don't understand your question? I am looking at the sql:stmtcompletedevent and adding up all the reads when run the query in the stored proc versus the sql:stmtcompletedevent when I exec the stored procedure directly. Is that what you mean?

  • April 16, 2012 at 1:51 pm

    #1474358

    Yeah, I was just making sure you were comparing apples to apples and not looking at something like SQL:StmtCompleted vs SQL:BatchCompleted or RPC:Completed. Now that we have that cleared up, I got nothing but theories 😀

    Have you tried running both queries in SSMS to see if you're getting a different plan in the proc than as ad hoc sql? Also, from SSMS what kind of reads are you seeing with STATISTICS IO turned on?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • April 16, 2012 at 2:32 pm

    #1474403

    Your theory is correct. The execution plans are different as well as the logical reads in the Statistics IO On. Any thoughts on how to debug this?

  • April 16, 2012 at 2:53 pm

    #1474413

    So when I run the two now. The Execution of the stored proc is taking 150,000 reads versus the adhoc query which is only taking about 36000 reads. I am not sure how to even attack this. Why would there be such big differences in the execution plans?

  • April 16, 2012 at 2:55 pm

    #1474416

    opc.three (4/16/2012)

    Yeah, I was just making sure you were comparing apples to apples and not looking at something like SQL:StmtCompleted vs SQL:BatchCompleted or RPC:Completed. Now that we have that cleared up, I got nothing but theories 😀

    Have you tried running both queries in SSMS to see if you're getting a different plan in the proc than as ad hoc sql? Also, from SSMS what kind of reads are you seeing with STATISTICS IO turned on?

    If you just run the query inside the SP it will take a different execution plan Vs when you run as a stored proc by passing the values. Are you trying to tune the query or just see the different for your reference?

  • April 16, 2012 at 2:57 pm

    #1474418

    Tune the query. But I found the difference. It was a stupid error on my end. I hadn't cleared the procedure cache. Once I did that they were the same or at least much closer.

  • April 16, 2012 at 3:05 pm

    #1474425

    EDIT: blah...site once again cross-posted a response to another thread for me

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • April 16, 2012 at 3:22 pm

    #1474435

    sqldba_newbie (4/16/2012)

    opc.three (4/16/2012)

    Yeah, I was just making sure you were comparing apples to apples and not looking at something like SQL:StmtCompleted vs SQL:BatchCompleted or RPC:Completed. Now that we have that cleared up, I got nothing but theories 😀

    Have you tried running both queries in SSMS to see if you're getting a different plan in the proc than as ad hoc sql? Also, from SSMS what kind of reads are you seeing with STATISTICS IO turned on?

    If you just run the query inside the SP it will take a different execution plan Vs when you run as a stored proc by passing the values. Are you trying to tune the query or just see the different for your reference?

    Feel free to educate me further, but I do not think this is always the case. It will depend on how the ad hoc SQL is written as to whether it will receive the same plan as the stored query run via the stored procedure.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 10 posts - 1 through 9 (of 9 total)

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