By setting STATISTICS IO ON, I get valuable information about the amount of I/O performed on each table in my query. Is there a way to store this information for each statement run, even if the statement isn't issued from my Query Analyzer window? Aside from splitting each of my tables into a separate filegroup and running the fn_virtualfilestats() system table-valued function to get I/O statistics, is there a way to calculate simple I/O values for my tables? Essentially, I want something like a trace flag that I can set so that SQL Server will store this information, letting me analyze it and better understand my object I/O utilization.
Your best bet for gathering I/O information and saving it for analysis is to create a SQL Server Profiler trace to capture this information. You can then load the trace file into a table for easy querying and analysis. (To learn how to create a Profiler trace, see Steven Berringer's article "9 Steps to an Automated Trace," InstantDoc ID 43014, or search on the keyword Profiler from http://www.sqlmag.com.)
