Tejas More

 Profile Answers by Tejas More 

• Jul 7th, 2011

 

prefetch improves performance by anticipating the pages required for certain well-defined classes of database activities whose access patterns are predictable. The I/O requests for these pages are issued before the query needs them so that most pages are in cache by the time query processing needs to access the page. Asynchronous prefetch can improve performance for:

Sequential scans, such as table scans, clustered index scans, and covered nonclustered index scans

Access via nonclustered indexes

Some dbcc checks and update statistics

Recovery