SQL Server Query Execution
Flavien Charlon, a software developer on our adCenter team team recently wrote a series of articles on his blog after completing SQL Server training by Kimberly Tripp.
Each blog post outlines the different query execution plans.
Part 1: SQL Server Data Structures
In SQL Server, data is organized in pages. A page has a fixed size (8 KB). Each page contains records. The number of records that can be stored in a page depends on the size of the records. The operation of reading data from a page is called a logical IO. The smaller the size of a record is, the more records can be read with the same number of logical IOs. (See full post here)
Part 2: SQL Server: Scans and seeks
The most primitive operation in SQL Server is retrieving from a table a set of rows that satisfies a given search predicate. This can be achieved using two basic strategies: scans and seeks. (See full post here)
SQL Server can use different data access strategies when retrieving rows from the table. The strategy that will be used depends on the columns of the table, the available indexes, the query, the data in the table, and the statistics. There are 7 basic data access strategies. (See full post here)