When it comes to performance tuning, locating the problem index (or lack thereof) is one of the first steps and being able to very quickly narrow the focus of your efforts makes for a much more efficent DBA. Using this technique, you can very easily get quantifiable I/O data for your queries and quickly get an idea of which queries and index need your attention.

This is a combination of 2 techniques posted on SQL Server Central, so I want to give these guys their due.

Last year Michael Morin posted a great technique on determining which indexes are
responsible for most of your I/O. The premise is to store the output from DBCC Showcontig with TableResults into a table and correlate that information with a Profiler Trace monitoring the Scan:Started event. (Number of times accessed * avg. row size) equals the 'Data Flow' for that index.

So, lets say you use Michael's method and you determine that the index PK_Trains is generating your most I/O, but you have several queries
that access that table. How do you know which query is responsible for all this I/O?

Last month Solomon Rutzky post an ingenious idea of creating an After Insert trigger on your Trace Table that
runs DBCC InputBuffer to populate the TextData field for events that don't normally populate TextData (ie. Scan: Started).

So by combining these two techniques you can take Michael's idea a step further and calculate a 'Data Flow' value
for each of your queries.

An example would be to combine the Trace Table created with Michael's technique with your Table of Indexes to determine which
queries are scanning PK_Trains.

SELECT CAST(TextData AS VARCHAR(4000)) AS SQLString, COUNT(*)
FROM TraceTable TT
JOIN MyDatabase..CompileIndexesShowContig CI ON TT.objectid = CI.objectid AND TT.indexid = CI.indexid
WHERE TextData IS NOT NULL
AND DatabaseID = 10
AND EventClass = 51
AND CI.indexname = 'PK_Trains'
GROUP BY CAST(TextData AS VARCHAR(4000))
ORDER BY COUNT(*) DESC

Perhaps PK_Trains is being scanned so heavily because a certain query is missing a more selective index. I have found this extremely helpful in troubleshooting high I/O queries. Enjoy.

Mike's Column: http://www.sqlservercentral.com/columnists/mmorin/2573.asp

Solomon's Column: http://www.sqlservercentral.com/columnists/srutzky/3201.asp