Learning How to Read SQL Plans

Indexes

First off… Scan means hitting the entire index. Seek means going straight to the index record.

• Scan - hits entire index
• Seek - immediately lookup So think of “table space scan” but instead “index scan” - it is better than scanning the entire table, but it means you’re probably missing another index.

Different Types of Index Hits

• Loop
• Hash
• Merge

These tell us how the engine will run the operation (hash is probably okay, but it might take a lot of work to build the hash… loop is fine for smaller records… and so on)

Index Compression

Index compression is almost always a good practice to use when creating TSQL indexes.

CREATE NONCLUSTERED INDEX [idx_Example_ColumnName]
ON [schema].[TableName] (ColumnName)
WITH (ONLINE = ON, DATA_COMPRESSION = PAGE)