Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2003

    Unanswered: physical io changing?

    If you run a query or stored procedure once and get the amount of physical_io used through the sysprocesses table when it completed, and then ran the exact same thing later, would you expect the two physical_io amounts to be the same? Would they be close? If they could be a lot different, what could cause that?

  2. #2
    Join Date
    Feb 2003
    They could be substantially different. When SQL Server performs an I/O it looks in its buffer cache - which is in memory - for the page it needs. If it does not find it, then the page is read from disk and placed into the buffer cache. Reading from disk results in your physical I/O. Once the page is in memory, though, it will stay there; it stays for a period of time that depends - for the most part - on how active the server is and how frequently that particular page is accessed. In any case, once the page is in memory, the next time SQL Server needs to access it, it does not need to execute a physical I/O. Hence, with your query's first execution, it may have quite a bit of I/O if some of the pages it is accessing are not in memory; once it has been executed, though, the pages should be in memory, so the physical I/O should be greatly reduced during the next execution, since it will access the pages in memory.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts