2009. 12. 3. 16:20

read-ahead는 무었인가?

 SET STATISTICS IO ON 을 실행하고 프로시저나 쿼리를 실행할때

 logical reads 820340, physical reads 0, read-ahead reads 820333 현상이 보인다. 이때 read-ahead는 무엇인가?

 

  • read-head는 페이지를 읽을때 메모리에 이미 있으면 그 값을 반환하는것 같음
  • 병렬 처리 실행에서는 사용할 수 없음
  •  결국, 높다고 안 좋은건 아닌듯 함

 

SQL Server 2008

http://msdn.microsoft.com/en-us/library/ms191475.aspx

 

  • SQL Server 2000

  • SQL Server 2000 uses ReadFileScatter to perform read-ahead operations. SQL Server uses sophisticated algorithms to retrieve data pages that are going to be used in the immediate future.

    For example, if you run a query that can use an index to determine applicable rows, a read-ahead may occur on the actual data pages that are needed to complete the select list. As index entries are identified, SQL Server can post OVERLAPPED (async) I/O operations for the data pages that will be used in upcoming steps of the query plan. This is how a query using a bookmark lookup operator uses read-ahead.

    This example is just one of many read-ahead situations that SQL Server can use. Allowing the index searching to continue while the data page I/O is in progress maximizes the CPU and I/O of the system. The I/O is often completed by the time it is needed so other steps in the plan have direct memory access to the needed data and do not have to stall while waiting on I/O.

    When a read-ahead is posted, it can be from 1 to 1,024 pages. SQL Server limits a single read-ahead request depth to 128 pages on most editions. However, Microsoft SQL Server Enterprise Edition raises the limit to 1,024 pages.

    SQL Server uses the following steps to set up read-ahead.

    1. Obtain the requested amount of buffers from the free list.

    2. For each page:

      1. Determine the in-memory status of the page by doing a hash search.

      2. If found to be already in memory, set up the read-ahead request to immediately return the buffer to the free list upon I/O completion.

      3. Establish the proper I/O request information for ReadFileScatter invocation.

      4. Acquire I/O latch to protect buffer from further access.

      5. If the page is not found in hash search then insert it into the hash table.

    3. Issue the ReadFileScatter operation to read the data.

    When the I/O operation is complete, each page is sanity checked for a valid page number and torn page errors. In addition, various other data integrity and safety checks are performed. The I/O latch is then released so the page is available for use if it is located on the hash chain. If the page was determined to be already in memory, the page is immediately discarded to the free list.

    This process shows the key factors of SQL Server I/O patterns. Read-ahead goes after pages that can already be in memory or not allocated. Because SQL Server maintains the in-memory buffers and hash chains, SQL Server tracks the page’s state. Importantly, read-ahead processing opens the door for overlapping read and write requests at the hardware level.

    If a page is already in memory when the read-ahead request is posted, the contiguous read is still needed and is faster than breaking up read requests into multiple physical requests. SQL Server considers the read to be unusable for the page in question, but many of the pages around it may be usable. However, if a write operation is in progress when the read is posted, the subsystem has to determine which image of the read to return. Some implementations return the current version of the page before the write is complete; others make the read wait until the write completes; and yet others return a combination, showing partially new data and partially old data. The key is that SQL Server will discard the read as unusable but the subsystem needs to maintain the proper image for subsequent read operations. The in-progress write, when completed, must be the next read image returned to the server running SQL Server.

    Do not confuse read-ahead with parallel query plans. Read-ahead occurs independently of the parallel query plan selection. The parallel plan may drive I/O harder because multiple workers are driving the load, but read-ahead occurs for serial and parallel plans. To ensure that parallel workers do not work on the same data sets, SQL Server implements the parallel page supplier to help segment the data requests.

    SQL Server has added increased diagnostics to report previously unreported read failures. The Microsoft Web site contains the following Knowledge Base article that provides diagnostic installation and usage instructions.