Dynamic Management Objects for Waiting Tasks
What are some common Dynamic Management Views to manage waiting tasks in SQL Server?
Performance Tuning
SQL Server 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.
The SQL Server Database Engine
The three parts of the SQL Server database engine with a focus on how the SQLOS component of the database engine manages resources.
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.
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).
How Data is Stored in SQL Server
Data Pages and Extents in SQL Server and how data is stored in SQL Server. Data records are physically stored in 8kb data pages on the hard drive.
SQL Query Processing
How SQL Queries are processed by the Relational Engine. Parsing, Resolving, Optimizing, Compiling, and Executing to return SQL Sets.
Concurrency: Phantom Reads
A closer look at the Serializable Transaction Isolation Level and how it could prevent Phantom Reads concurrency issues in SQL Server.
Concurrency: Inconsistent Analysis
A closer look at the Repeatable Read Transaction Isolation Level and how it could help with Inconsistent Analysis concurrency issues in SQL Server.
Concurrency: Lost Updates
A closer look at the Read Committed Transaction Isolation Level and how it could allow Lost Update concurrency issues in SQL Server.