Implement and Maintain Indexes

Question 1

What is a table called when it doesn’t have an index attached?

Answer 1

Heap

Question 2

What are the two fixes for Index Fragmentation?

Answer 2

Reorganize

Rebuild

Question 3

What does Reorganize do for an index?

Answer 3

It restructures the ROOT level node. It takes less resources. It also compacts the pages together.

Question 4

What does Rebuild do for an index?

Answer 4

It completely rebuilds the index. Much more system intensive solution.

Question 5

What is the t-sql syntax to REORGANIZE (Or Rebuild)?

Answer 5

ALTER INDEX [INDEXNAME] ON [TABLENAME] REORGANIZE/REBUILD

When you use REBUILD, make sure to add WITH (ONLINE=ON)

Question 6

What is the system table that has Index statistics?

Answer 6

SYS.DM_DB_INDEX_PHYSICAL_STATS

PARAMETERS: 
   database_id (OR NULL)
  , object_id (OR NULL)
  , index_id (OR NULL)
  , partition_number (OR NULL)  
  , mode (OR NULL)

Question 7

When should you rebuild or reorganize an index?

Answer 7

If the fragmentation is over 30% - Rebuild

If the fragmentation is between 5 - 30% - Reorganize

Question 8

What is an index Fill Factor?

Answer 8

It is the ability to leave some space at the end of a page instead of trying to fill it up. Makes it so

Question 9

How do you set the FILL FACTOR when creating (or rebuilding) and index?

Answer 9

In the WITH statement:

ALTER INDEX [INDEXNAME] ON [TABLENAME] WITH (FILLFACTOR = 80)

Question 10

What does the fill factor number mean?

Answer 10

It is the Percentage of the page that will be filled up (80% full, 20% will be left empty)

Question 11

What is the system table that you can use to see how much an index is being used?

Answer 11

SYS.DM_DB_INDEX_USAGE_STATS

Question 12

What do you look for in SYS.DM_DB_INDEX_USAGE_STATS?

Answer 12

High number of SEEKS, Low number of SCANS and LOOKUPS and a low number of USER_UPDATES compared to SEEKS (That means that the index is having to be maintained while giving no benefit)

Question 13

What are Index STATISTICS? How are they created?

Answer 13

It is basically a hint that the database uses to have an idea of what data exists in an index.

Created with the same syntax as an index, but use STATISTICS instead of INDEX

Question 14

How can you update statics?

Answer 14

• Execute STATS_DATE function with the object_id of the table and the stats_id of the statistic.
• execute the sp_autostats stored procedure with the table name