Logical Reads Using Sql Profiler

Question

Post reply

Logical Reads Using Sql Profiler

pamozer

SSCarpal Tunnel

Points: 4943
More actions
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

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

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 1

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

pamozer SSCarpal Tunnel Points: 4943 More actions · Answer 2

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?

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

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

pamozer SSCarpal Tunnel Points: 4943 More actions · Answer 4

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?

pamozer SSCarpal Tunnel Points: 4943 More actions · Answer 5

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?

curious_sqldba SSC-Dedicated Points: 36494 More actions · Answer 6

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?

pamozer SSCarpal Tunnel Points: 4943 More actions · Answer 7

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.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 8

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

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 9

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