Craig Freedman's SQL Server Blog

A discussion of query processing, query execution, and query plans in SQL Server.

Browse by Tags

Tagged Content List
  • Blog Post: OPTIMIZED Nested Loops Joins

    In my past two posts, I explained how SQL Server may add a sort to the outer side of a nested loops join and showed how this sort can significantly improve performance . In an earlier post , I discussed how SQL Server can use random prefetching to improve the performance of a nested loops join. In this...
  • Blog Post: Optimizing I/O Performance by Sorting – Part 2

    In my last post, I discussed how SQL Server can use sorts to transform random I/Os into sequential I/Os. In this post, I'll demonstrate directly how such a sort can impact performance. For the following experiments, I'll use the same 3 GByte database that I created last week . The system I'm using...
  • Blog Post: Optimizing I/O Performance by Sorting – Part 1

    In this post from last year, I discussed how random I/Os are slower than sequential I/Os (particularly for conventional rotating hard drives). For this reason, SQL Server often favors query plans that perform sequential scans of an entire table over plans that perform random lookups of only a portion...
  • Blog Post: Random Prefetching

    In my last post , I explained the importance of asynchronous I/O and described how SQL Server uses sequential read ahead to boost the performance of scans. In this post, I'll discuss how SQL Server uses random prefetching. Let's begin with a simple example of a query plan that performs many random I...
  • Blog Post: Sequential Read Ahead

    Balancing CPU and I/O throughput is essential to achieve good overall performance and to maximize hardware utilization. SQL Server includes two asynchronous I/O mechanisms - sequential read ahead and random prefetching - that are designed to address this challenge. To understand why asynchronous I...
Page 1 of 1 (5 items)