“Large I/O Effectiveness” helps you to determine the performance benefits of large I/O. It compares the number of pages that were brought into cache by a large I/O to the number of pages actually referenced while in the cache. If the percentage for “Pages by Lrg I/O Used” is low, it means that few of the pages brought into cache are being accessed by queries. Investigate the individual caches to determine the source of the problem. Use optdiag to check the value for “Large I/O Efficiency” for each table and index.
“Pages by Lrg I/O Cached” reports the number of pages brought into all caches by all large I/O operations that took place during the sample interval. Low percentages could indicate one of the following:
Allocation fragmentation in the table’s storage
Inappropriate caching strategy
“Pages by Lrg I/O Used” reports the total number of pages that were used after being brought into cache by large I/O. sp_sysmon does not print output for this category if there were no “Pages by Lrg I/O Cached.”
