RSS

SQL Server Execution Plan Definition 101

06 Aug

Heap Table:

A database table that doesn’t have clustered index. As opposed to clustered table.

Clustered Table:

A database table that has clustered index.

Non-clustered and Clustered:

Clustered index: index that stores the actual data.

Non-clustered index: a pointer to the data.

Refer to this post for more detail definition.

Table Scan:

Full scan, that is go and read through all of the data, in a table to find the rows that satisfy the query. Normally happen on table without any indices.

Clustered Index Scan:

Full scan in a clustered index table to find the rows that satisfy the query.

Non-clustered Index Scan:

Full scan in a non-clustered index table to find the rows that satisfy the query. Probably happens mostly in query that select all columns.

Clustered Index Seek:

Scan only on clustered index (as opposed to whole table).

Non-clustered Index Seek:

Scan only on non-clustered index (as opposed to whole table).

Key Lookup (Bookmark Lookup):

Happens when the query requests data from columns not present in the clustered index.

RID Lookup:

Happens when the query requests data from columns not present in the non-clustered index.

For understanding of some of these terms with example, this post by Greg Robidoux, “Understanding SQL Server Indexing” is very helpful.

Advertisements
 
Leave a comment

Posted by on August 6, 2011 in General

 

Tags: , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: