select objectid, indexid, sum(*) as scans
from IndexCapture
group by objectid, indexid

In the above query change sum(*) to count(*).

This is a very nice article. But I agree with the other post that table size should be included into the calculation instead of just averagerecordsize.

AL

I think the sum(*) statement in the SQL to create IndexCaptureSummary should probably be count(*). Also, in the report sql statement, the table name should be CompileIndexesShowContig according to the sql used to create the table earlier on...

I thought this was an interesting concept and so I did the study on my database and I have to say that it does appear that the results as stated in the article can be misleading. One of our most heavily used indexes is on our products table which has large records (varchar() text descriptions,etc), but only 250 rows and the index used is clustered.

I'm not sure that the products table is our IO hog, but it showed up as 57% of flow on 17% of scans using this technique. Most of our queries don't return * from products but only select columns and thus (scancount * AverageRecordSize) may not be a good indication of the IO demand created by those scans. It's also frequently used and small enough overall that I'm 99% sure it's held in memory and hurting disk IO.

Since the profiler event Scan:Start just triggers when an index is used (correct?), our products primary key is probably also scanned when using the products table in joins where records aren't even being retreived (necessarily). Not too sure on this point...

Thus, I think the data collection techniques are good, but I wonder if there is a better formulation of the summary report that would factor in the number of rows in the table and/or other data from profiler that would give a more accurate picture of IO demands. Otherwise, great work!

This article and all the replies were very interesting and serve a purpose. I too have a few questions about some of the concepts used. I actually think this solution is overthinking a common problem.

If I think I/O is a problem, I will run a SQL Profiler Trace and parse it with my favorite parsing tool and sort by Reads or Writes (typically in my systems if I/O is a problem it is reads... there is probably a table scan or index scan someplace most of the time that can be better implemented through better code, better indexing or a better table/normalization structure) and find the worst queries.

As colin has said earlier (and in numerous posts ) if you have a lot of reads you are bringing in a lot of data and possibly flushing out your cache and definitely using (over?) your disks and CPU..

Take your reads multiply by 8 and divide by 1024 twice. This is number of GB you are reading. I have seen a horribly written query perform 23,000,000 reads on a database whose total size is only 5GB!!

So I like looking at the "simple" things first. That normally solves the biggest headaches.

I just created this and the

select cisc.objectname, cisc.indexname, ic.scans, floor(scans*cisc.averagerecordsize) as flow
from IndexCaptureSummary ic
inner join CompileIndexesShowContig cisc on cisc.objectid = ic.objectid and cisc.indexid = ic.indexid
order by flow desc

works but if you have a table with no pk or no index and just say rowid it does not pick that up...thats so on the

two tables i added default of
'' for both...
one would come in as Null and the other as blank..
SET THE INDEXID default '' on CompileIndexesShowContig and INDEXCAPTURE

Also the sum(*) i changed to count(*)
insert into IndexCaptureSummary (ObjectId, IndexID, Scans)
select objectid, indexid, count(*) as scans
from IndexCapture
group by objectid, indexid

Lets say its taking the AverageScans...what if you only select 5 records from a table and another day you select all records...(then would this be a bit misleading) as the i/o is reduced with smaller selects...(Anyone know how to change it so that rows could be incorporated into this )

I like it alot............

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

How do you measure this part...aswell..that be useful to