We are trying to capture I/O for SQL Server databases as a way to charge internal customer groups for database usage. We are interested in bufferpool logical data I/O, data writes, logical index I/O and index writes.
These four items will be added together, and we don't need to identify any of the components individually. We don't have any need to measure physical I/O, but if it is part of an overall measurement we will take it. We want to avoid asynchronous I/O if at all possible. Ideally we would like to also include any reads or writes that bypass the bufferpool completely. I believe loads bypass the bufferpool.
We can't seem to find this information at the database level. I successfully set up these measurements on DB2, but I can't seem to do it in SQL Server. I can't even figure out where to start! Is this even possible?
You can gather I/O stats via Performance Monitor and also via Profiler.
For More Information
- Dozens more answers to tough SQL Server questions from Michael Hotek are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in December 2002