July 17, 2006 at 3:05 pm
I am in the process of trying to improve the performance of a stored procedure. To do so I enabled execution plan and Server Trace.
After running the sp I see in the server trace a column called "Reads" and "Writes". What are these columns referring to: Disk read and writes or Memory read and writes?
If they are disk reads then a high number is not a good thing and if they are memory then a high number is a good thing.
Thanks for any help.
July 18, 2006 at 9:50 am
page reads and writes
http://www.compman.co.uk/scripts/browse.asp?ref=450145
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
July 18, 2006 at 10:47 am
They're LOGICAL reads and writes - you can't infer whether or not they're coming from disk or memory.
If the duration is also high, this is a good indicator that they're likely to be physical reads (i.e. from disk), but to really confirm it you need to run a parallel System Monitor trace, measuring PhysicalDisk counter values for DiskReads/sec and DiskWrites/sec.
If you create a System Monitor counter log file, and use the SQL2005 client version of Profiler (if you have that available to you - it will happily monitor SQL2000 servers), then you can import the System Monitor trace and display the two outputs side-by-side in a single window. Clicking on a particular Profiler statement highlights the equivalent point on the Perfmon graph (and vice-versa) - very useful.
July 18, 2006 at 2:54 pm
yeah sorry .. logical reads .. however sp tuning wouldn't really be concerned with physical or logical because it would depend upon frequency of use , to populate the cache initially you probably will get physical reads .. however from then on the proc should read from cache .. I'm normally first concerned with overall i/o , saving 25 i/o on a proc which runs many times a second may give greater savings than knocking a few thousand i/o from a proc which runs once a day.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
July 20, 2006 at 7:38 am
Well, yes, but consistenly high IO is going to lead to higher overall cache turnover (i.e. pages needed by other processes aren't going to stay in memory as long as they might if some processes are demanding very high levels of logical reads / writes). This can easily affect the performance of SPs that would actually perform very well left to themselves.
You're right, it comes down to percentages, and it's almost always more beneficial to tune the performance of frequently-executed code before looking at that which only runs occasionally. However, if there are, say, twenty SPs which each run just once a day, but each take 15 minutes and require 10 million Reads or more, that means that at any one time during the day you might conceivably have at least one of these SPs running. Worse, several of them might run at the same time, in which case they'd be much higher on my priority list than other SPs.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply