Performance Tuning

The SQL Server Database Engine

The three parts of the SQL Server database engine with a focus on how the SQL Server Operating System (SQLOS) component manages resources.

SQL Query Processing

How SQL Queries are processed by the Relational Engine. Parsing, Resolving, Optimizing, Compiling, and Executing to return SQL Sets.

Query Optimizer Phases

The SQL Server Query Optimizer uses cost-based optimization to create an execution plan with the least cost.

Task Execution Model

How the SQLOS manages worker threads in the SQL Server Execution Model and the different wait queues in SQL Server to manage threads used by the CPU.

Table Structures in SQL Server

Heaps vs Clustered Indexes and how tables are structured and data is stored in SQL Server. Data rows from a table are stored in the data pages in either an unsorted order (Heap) or a sort order (Clustered Index).

Execution Plans – Table Operators

How to read Heap and Clustered Index table operators in a SQL Server Execution Plan. One way of determining if a table is structured as a Heap or a Clustered Index is to look at the table operators from the Execution Plan.

What are Transactions?

The ACID properties of SQL Transactions, What are the different ACID properties of a transaction how they ensure that data is Atomic, Consistent, Isolated, and Durable.

Transactions and Errors

SQL Transaction and Error Handling? What is the difference between Auto-Commit and Explicit Transactions and how transaction affect Errors and Exception Handling?