3

I am working on the optimization of a SP which contains some business logic using looping. I have removed the looping and converted those piece of code into some simple insert/update statements.

Now I've to do benchmarking and compare old and new code in terms of execution time and logical/physical reads.

My problem is because of the loop in my old code, how can I determine what is the total no of logical/physical reads. Because in SSMS, I can see thousands of IO stats statements like:

"Table 'Employee'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 43, lob physical reads 0, lob read-ahead reads 0."

  • 1
    SQL Sentry Plan Explorer has a Table I/O tab that shows all of the reads broken down by table. It also has several other features that will easily allow you to compare two statements or batches. – Aaron Bertrand Aug 29 '16 at 19:09
  • @AaronBertrand The problem with Plan Explorer is that the Table I/O tab is grayed out in the free version. It does provide the data Ashwini is after but $$$. – Tara Kizer Aug 29 '16 at 19:34
  • 2
    @TaraKizer No, Table I/O is not a paid feature. It will only be greyed out if you (a) open or generate an estimated plan (which obviously has no actual I/O) or (b) open an actual plan after generating it in Management Studio (in which case SSMS has no I/O information to share with Plan Explorer). If you generate an actual plan from within Plan Explorer, you get Table I/O. – Aaron Bertrand Aug 29 '16 at 19:36
  • @AaronBertrand, oh gotcha! I'm usually looking at estimated plans and saw that tab grayed out. I stand corrected! – Tara Kizer Aug 29 '16 at 19:37
6

I can think of 3 options to get the total reads:

  1. Extended Event session
  2. SQL Profiler
  3. IO Statistics Parser

IO Statistics Parser is pretty simple, just copy/paste your STATISTICS IO output and voila!

  • Just curious, why Profiler ? Its expensive from the time it gets clicked :-) – Kin Shah Aug 29 '16 at 17:00
  • @Kin, because it answers the question and is easy to use. I agree that it's expensive, though there are ways to limit that. – Tara Kizer Aug 29 '16 at 17:02
2

The best tool that I have found for query tuning is Plan Explorer.

You can get full execution plan along with Table IO - LOB Logical Reads, Physical Reads, Scan Count, etc.

In Pro version, you can even track the optimizations (history) that you have made by comparing the previous and optimized versions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.