Indexes

Question 1

What is a page?

Answer 1

• Page has a size of 8k
• 8 consecutive pages make one extent
• Page can only belong to a single object. Table, index…etc.

Question 2

What is an extent?

Answer 2

• An extent is made up of 8 pages
• Uniform extent - All pages belong to single object
• Mixed extent - Pages belong to multiple objects

Question 3

What happens when an object exceeds 8 pages?

Answer 3

SQL server allocates additional uniform extents for this object.

Question 4

What is a heap?

Answer 4

A heap is just a bunch of pages and extents.

Question 5

How does SQL server track where pages and extent belong?

Answer 5

Using a special page called Index Allocation Map to trace which page and extent belong to an object.

Question 6

How many levels does a clustered index (TREE) has?

Answer 6

• Root - contains pointers
• Intermediate (when needed) - contains pointers
• Leaf - contains data

Question 7

What is internal fragmentation?

Answer 7

• Clustered index only
• Caused by page split and when pages are not filled.
• Can be controlled by FILLFACTOR or PAD_INDEX

Question 8

What is external fragmentation?

Answer 8

Clustered index only

When pages are physical out of order.

Question 9

Non clustered index levels?

Answer 9

Similar to clustered index

• Root and intermediate level are pointers
• leaf level does not hold data instead it contains index keys and the RID (row identifier)

Question 10

How is row identifier used?

Answer 10

It depends if the underlying table is a heap or a balanced tree.

• Balance tree - RID is the clustered index. Points to a logical structure
• Heap - RID has the dbs file ID, page ID, target row ID…etc. Points to a physical structure.

Question 11

What is a filtered index?

Answer 11

Filter index is a nonclustered index optimized to cover queries that select from a well defined subset of data.

Question 12

What is columnstore index?

Answer 12

• When a query references a single column that is a part of the columnstore index, SQL server fetches only that column from disk. The rows are reconstructed.

Question 13

What are the steps to create indexed view?

Answer 13

• Verify that all the SET options are correct
• Verify that the view is deterministic
• create using WITH SCHEMABINDING
• First index create on the view must be clustered index
• If there is GROUP BY, don’t use HAVING. Use COUNT_BIG(*) instead.

Question 14

Waht does SQL server use to aggregate data?

Answer 14

• hash
• Stream aggregate operator - faster but requries a sorted input

Question 15

Why use INCLUDE with CREATE INDEX?

Answer 15

Add nonkey column to the non clustered index so that the queries are covered.

Question 16

What does DBCC SHOW_STATISTICS return?

Answer 16

• STAT_HEADER
• DENSITY VECTOR
• HISTOGRAM