January 10, 2007 at 9:59 am
Hello all.
I'm tuning a stored procedure that is doing some scans, by adding appropriate indexes. I am looking at the CPU and Reads column in Profile to identify if the amount of data being brought into the data cache has been reduced after my tuning. High reads and scans on the production system are suspected of causing a low page life expectency (around 100 secs).
I think that Reads interprets to number of 8K pages read, so 100 Reads is 800K into the data cache???
That's the background, now the question:
When I run the proc after sp_recompile, the CPU is 328 and Reads are 2900. When I run the same proc immediately after without a recompile, the CPU is 0 and Reads are 21. The subtree cost for the estimated execution plan is 20.
Why is the CPU and Read so much lower on the second run? Are the high CPU and Reads due to the query optimiser compiling the plan, or are the lower stats on the second run due to the fact that the data is now cached, and there are no IO's. Or is it a combination of the two?
Thanks,
Wayne
January 10, 2007 at 12:16 pm
Yes you are right...
If the plan and data is cached SQL serve your request from cache/memory, so it doesn't need to work much on the request...
MohammedU
Microsoft SQL Server MVP
January 11, 2007 at 7:46 am
reads are page reads, note that the values inside profiler are likely to be different to what you see in QA. cpu is I guess cpu cycles and sould be taken as a measure of work done.
When you first run a proc it will be comiled and the plan placed in cache, hopefully successive calls to the proc will use the cached plan avoiding an recompiles. Thus the inital stats for the compilation of the proc will be higher than the calls. This illustrates why proc recompiles are a bad thing.
The reads total will be the same regardless of physical or logical io, however physical io is slower than logical io, you should bear that in mind as if your proc later has to pull physical reads then performance will be lower.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply