Re: Curious performance experiment

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: David Gugick (davidg-nospam_at_imceda.com)
Date: 11/04/04 

Date: Wed, 3 Nov 2004 19:47:13 -0500

JXStern wrote:
> I found an SP scanning a table of 100k rows which lacked the proper
> index, doing a join to several other tables. Statistics IO reported
> it took 26000 logical reads (to do it twice, btw).
>
> I then added the proper index. Reads dropped to 1500. Good, huh? I
> should have declared victory and gone home.
>
> But no, not me. I then dropped the index and reran. It went up
> again, of course, but only to 6700 reads.
>
> Um, ... anyone ever see anything like this? I'm at something of a
> loss. I thought I'd run it several times in each mode, and in fact
> was running it in a loop of 100 from query analyzer to get a decent
> clock-time idea of execution (but got the statistics IO from single
> executions).
>
> Josh

What are these"reads"? Are you using STATISTS IO for the information? If
so, how many logical vs physical? You might consider using Profiler to
see the reads, CPU, and duration. Don't be alarmed that the reads in
Profiler do not match that of STATISTICS IO. They are reading different
structures and the SQL Trace API will normally report reads that are not
tracked by STATISTICS IO. 

-- 
David Gugick
Imceda Software
www.imceda.com

Relevant Pages

  • Experimental Research In Education: The Most Exciting Talk at the 2005 Joint Statistical Meetings
    ... tled "Education Research Meets the Gold Standard: Statistics, ... Education, and Research Methods after 'No Child Left Behind'". ... proper experiments in education research have gener- ... for students who aren't majoring in statistics. ...
    (sci.stat.edu)
  • Re: Unless the only reason you eat is to have babies
    ... The key is proper ... The idea that diet is a causal link to sexual orientation is amusing, ... don't necessarily indicate a coverup of political agenda. ... there are no such statistics because they don't exist. ...
    (rec.radio.shortwave)
  • Re: Curious performance experiment
    ... > I then added the proper index. ... > clock-time idea of execution (but got the statistics IO from single ... reorganized by clustering and unclustering it, ...
    (microsoft.public.sqlserver.server)
  • Curious performance experiment
    ... I found an SP scanning a table of 100k rows which lacked the proper ... I then added the proper index. ... clock-time idea of execution (but got the statistics IO from single ...
    (microsoft.public.sqlserver.server)
  • Re: Advanced Probability for Dummies?
    ... >>>studies statistics as a branch of mathematics. ... >>>applied statistician, ... as far as the proper application of ... > a generic student of statistics. ...
    (sci.stat.edu)