What do DiskRead and DiskWrite properties include?

[Breck_Carter]

Do they include...

1. SYSTEM dbpsace table and index pages
2. Secondary dbspace table and index pages
3. Temporary dbspace table and index pages
4. Temporary dbspace other reads and writes
5. LOAD and UNLOAD reads and writes
6. xp_write_file and xp_read_file reads and writes
7. Checkpoint file operations
8. Recovery file operations
9. Other file operations not listed above

[Former Member · Accepted solution]

Any page level read or write should be included.

At a minimum that would include all of 1., 2., 3., 4., 7., 8. and for:

5.: at least the writes for the LOAD and reads for the unload, 6.: any paging to/from the temporary file (or if part of an insert/update table and index updates)

Not much else would fall into item #9 (other than possible pagingfile or swap I/Os maybe)

These are low level counters updated whenever page level reads and writes are used and the items identified always fall into that category.

I'd have to drill more into some of the other I/O parts more if they are important. They might be included but I'd have to do a bit more research.

[Breck_Carter · Accepted solution]

Thank you for being so thorough.

Points 5 and 6 were unclear...

5a. LOAD and UNLOAD reads and writes to SQL Anywhere dbspace files - I think you've answered "yes" to this one

5b LOAD AND UNLOAD reads and writes to external (text etc) non-dbspace files - for example, LOAD reads a 5G text file; does that get counted in DiskRead?

6 xp_write_file and xp_read_file reads and writes to external non-dbspace files - this is the "direct I/O" for the xp_thingies; I assumed the "indirect I/O" (e.g., an INSERT of xp_read_file return value into a blob column) is counted in point 1

[Former Member · Accepted solution]

From my read ... and unless you have noticed a case I need to look into more ....

For 5a you are correct. For 5b my read indicates that is a no.

For 6 ... again xp_write_file( ) writes and xp_read_file( ) reads are a no case as well. {except as you've noted}

Unless either of those happen to cause I/Os of cache pages to/from the temporary file (#3) or are part of an operaton that interacts with a table they do not seem be counted.