Subject Re: Detecting disk operations
> Is there a way to detect throught mon$ tables or trace or other way disk operations caused by query?

  Engine collects some performance stats counters internally. Trace, monitoring, ISC API - all query engine's
performance data. Therefore there is no "special hidden ways" to ask engine for something that is not
collected by the engine itself.

  Trace is the most precious way to know given query execution statistics.

> I see only page_reads and page_writes, page_marks, page_fetches but how to distinguish which one:
> 1. Page is read because missed in cache

  Page read is always happens when page buffer is missed. Page fetch is always happens when page buffer
is accessed by the engine.

> 2. Page is read/write beacause of big sort operation

  Sort IO is directed into separate temporary files and it is nor cached by the engine, nor paged.
Unfortunately, engine doesn't collect Sort statistics.

  You could try to use OS utlities to look at performance stats at the file level, btw

> 3. Page is read/write from temp object bigger then memory buffer e.g big global temporary table, others?

  GTT's data go thru the common page cache as all other database operations.

Hope it helps,