Optimization

Question 1

What are heap based tables?

Answer 1

tables without a clustered index

Question 2

What is a covering index?

Answer 2

an index that contains all the output columns for the operation performed on the index (So there is no strict definition of a covering index. Whether an index is “covering” or not is based on the query that used the index. More specifically, it is based on the output columns of the query that used the index)

Question 3

Is a clustered index a covering index?

Answer 3

It can be considered a covering index since it contains all the columns in the table

Question 4

What’s the difference between a table scan of an index based table (Clustered Index scan) and a table scan of a heap table?

Answer 4

Clustered index scan is faster than a table scan of a heap table because leaf nodes of the clustered index are stored together.

Question 5

What steps do you take when optimizing a slow running query?

Answer 5

1. First, run update statistics on all tables involved in your query.
2. Get query plan so you can get the most optimal plan according to the query engine.
3. Get the most expensive step in the plan and stop from there.

Question 6

What do you look for in a query plan?

Answer 6

1. Table scan - slowest process - means there is no index used and there is no clustered index on the table
2. Clustered Index Scan - better than a table scan but slower than an index seek. Try creating an index for it (find the fields where conditions are applied and create an index over them. Find the most selective criteria and put that column first in the index).

Question 7

Why wouldn’t the query engine use an index seek over a clustered index scan?

Answer 7

Index seeks are generally faster than scans, but there might be a situation where an index is not “covering” which means it will need a bookmark lookup in addition to the index seek. Clustered indexes don’t require bookmark lookups since all the data are already stored on the leaf of the clustered index.

Question 8

What’s a bookmark lookup?

Answer 8

Process of finding the actual data in the sql table.

This is done when the index used in the query is not “covering” - not all columns in the select are in the index

Question 9

What can u do if a query’ use of an index always results in a bookmark lookup after?

Answer 9

UNCLUDE all columns in the query to the index to make it “covering”. Not necessarily part of the index. They can be just INCLUDED