INF: DBCC SQLPERF(LRUSTATS) Reports Some Irrelevant Info.

  - Microsoft SQL Server versions 4.21 and 4.21a

SUMMARY

With the introduction of the new Lazywriter process in SQL Server version 4.21, the scan limit information returned by the DBCC SQLPERF(LRUSTATS) command is no longer relevant.

MORE INFORMATION

In previous versions of SQL Server, individual processes would search the data cache for an available buffer. If a clean buffer was not found after scanning a certain number of pages (this number was referred to as the scan limit), the user process would select a dirty buffer and be required to flush the contents of the buffer to disk before claiming it.

With the introduction of the Lazywriter system process, the scan limit, as it was previously used, is no longer needed. In version 4.21 and later, the Lazywriter process awakens when the number of free buffers falls below a certain threshold and begins to flush out dirty buffers (see version 4.21 Release Notes for more information). The server now maintains a separate list of all free buffers. Therefore, SQL Server no longer has to search through all of the buffers to find a free one--it simply gets a new buffer from the list of free buffers.

When the DBCC SQLPERF(LRUSTATS) command is run, it returns information regarding scan limit, scan limit reached, and cache flushes. This information is irrelevant and should be ignored in SQL Server versions 4.21 and later due to the changes described above. Other counters reported by DBCC SQLPERF(LRUSTATS) including Average and Maximum Free Page Scan counters, now have different meanings than those described in the SQL "Administrator's Guide."

The new meanings are described below:

   Average Free Page Scan: The average number of data cache buffers scanned
   by the Lazywriter each time it searches for unused buffers to replenish
   the free pool.

   Maximum Free Page Scan: The maximum number of data cache buffers scanned
   by the Lazywriter while searching for unused buffers to replenish the
   free pool.