• At first read this seems to be an interesting read with some useful conclusions, however on more careful analysis I would suggest that the heading and conclusions are somewhat misleading.

    In a nutshell the trace records the scan stat as shown thus from a query

    select * from pubs.dbo.titleview

    (25 row(s) affected)

    Table 'titles'. Scan count 25, logical reads 50, physical reads 0, read-ahead reads 0.

    Table 'titleauthor'. Scan count 23, logical reads 46, physical reads 0, read-ahead reads 0.

    Table 'authors'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0.

    The point about this query is that the scan on authors is actually an index scan taking 70% of the time and the scans on the other two tables are clustered index seeks - ok it's a poor example I admit- the point is that if the titles table was doing table scans the scan count would still stay at 25. ( and 25 table scans could be bad news whereas 25 index seeks are fine )

    I dispute some of the maths and conclusions, in a reasonable configured SQL Server any table hotspots should be in cache, I've found more often that less frequently used heavy i/o queries can flush the data cache. Technically 200k i/o could flush 1.5Gb through the data cache, for a reasonale table this may appear only as 1 scan ( in fact a select * from a 300 million row table would show as 1 scan ) so in the overall scheme of things the 1 scan that moved several gigabytes of data would not be shown as significant. On a small table a single integer value returned from an indexed table shows a scan count of 1 whereas the entire column ( just over 2100 rows ) also shows 1 scan. In actual fact we have 4 bytes vs 8600 bytes or 2 page i/o vs 5 page i/o or 1 scan vs 1 scan. For small tables sql server is unlikely to do any row ops and read ahead is usually 64kb blocks, exactly how read ahead deals with a table of only 32kb I'm not 100% sure. Most typical integer secondary indexes would be contained within 3 pages of which two pages will always be read it's wrong to attempt to multiply the scan count by 4 bytes to see how much data is being read, I'd suggest ( in this case ) always 2 pages.

    The article certainly gives a slant on object usage, however I'd suggest a sql statement vs reads, writes, cpu, duration is of much greater value

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/