Chapter 11 Implementing Indexes

1
Chapter 11 Implementing Indexes
2
Overview
P391

• Introduction to Indexes
• Index Architecture
• How SQL Server Retrieves Stored Data
• How SQL Server Maintains Index and Heap
  Structures
• Deciding Which Columns to Index

3
Introduction to Indexes
P392-393

• How SQL Server Stores and Accesses Data
• Whether to Create Indexes

4
How SQL Server Stores and Accesses Data
P392-393

• How Data Is Stored
• Rows are stored in data pages
• Heaps are a collection of data pages for a table
• How Data Is Accessed
• Scanning all data pages in a table
• Using an index that points to data on a page

Data Pages
Page 8
Page 9
Page 7
Page 4
Page 5
Page 6
5
Whether to Create Indexes
P392-393

• Why to Create an Index
• Speeds up data access
• Enforces uniqueness of rows
• Why Not to Create an Index
• Consumes disk space
• Incurs overhead

6
Index Architecture
P393-395

• SQL Server Index Architecture
• Using Heaps
• Using Clustered Indexes
• Using Nonclustered Indexes

7
Multimedia Presentation SQL Server Index
Architecture
8
Using Heaps

• SQL Server
• Uses Index Allocation Map Pages That
• Contain information on where the extents of a
  heap are stored
• Navigate through the heap and find available
  space for new rows being inserted
• Connect data pages
• Reclaims Space for New Rows in the Heap When a
  Row Is Deleted

9
Using Clustered Indexes
P393

• Each Table Can Have Only One Clustered Index
• The Physical Row Order of the Table and the Order
  of Rows in the Index Are the Same
• Key Value Uniqueness Is Maintained Explicitly or
  Implicitly

10
Using Nonclustered Indexes
P394

• Nonclustered Indexes Are the SQL Server Default
• Existing Nonclustered Indexes Are Automatically
  Rebuilt When
• An existing clustered index is dropped
• A clustered index is created
• The DROP_EXISTING option is used to change which
  columns define the clustered index

11
Finding Rows in a Heap with a Nonclustered Index
sysindexes
SELECT lastname, firstname FROM member WHERE
lastname BETWEEN 'Masters' AND 'Rudd'
12
Finding Rows in a Clustered Index
Martin
SELECT lastname, firstname FROM member WHERE
lastname 'Ota'
Martin
13
Finding Rows in a Clustered Index with a
Nonclustered Index
sysindexes
SELECT lastname, firstname, phone FROM
member WHERE firstname 'Mike'
Nagata
14
Creating Index Options
P396-397

• Using the FILLFACTOR Option
• Using the PAD_INDEX Option

15
Using the FILLFACTOR Option
P396-397

• Specifies How Much to Fill the Page
• Impacts Leaf-Level Pages

Data Pages Full
Fillfactor 50 Leaf Pages 50 Full
16
Using the PAD_INDEX Option
P396-397

• The PAD_INDEX Option Applies to Non-Leaf-Level
  Index Pages
• If PAD_INDEX Is Not Specified, the Default Leaves
  Space for One Row Entry in Non-Leaf-Level Pages
• Number of Rows on Non-Leaf-Level Pages Is Never
  Less Than Two
• PAD_INDEX Uses the Fillfactor Value

USE Northwind CREATE INDEX OrderID_ind ON
Orders(OrderID) WITH PAD_INDEX, FILLFACTOR70
17
Creating Indexes
P403

• Creating and Dropping Indexes
• Creating Unique Indexes
• Creating Composite Indexes
• Creating Indexes on Computed Columns
• Obtaining Information on Existing Indexes

18
Creating and Dropping Indexes
P405-408

• Using the CREATE INDEX Statement
• Indexes are created automatically on tables with
  PRIMARY KEY or UNIQUE constraints
• Indexes can be created on views if certain
  requirements are met
• Using the DROP INDEX Statement

USE Northwind CREATE CLUSTERED INDEX
CL_lastnameON employees(lastname)
USE Northwind DROP INDEX employees.CL_lastname
19
Creating Unique Indexes
P405-408
USE Northwind CREATE UNIQUE NONCLUSTERED INDEX
U_CustID ON customers(CustomerID)
Duplicate key values are not allowed when a new
row is added to the table
20
Creating Composite Indexes
P405-408
21
Obtaining Information on Existing Indexes

• Using the sp_helpindex System Stored Procedure
• Using the sp_help tablename System Stored
  Procedure

USE Northwind EXEC sp_helpindex Customers
22
Deciding Which Columns to Index
P410-411

• Understanding the Data
• Indexing Guidelines
• Choosing the Appropriate Clustered Index
• Indexing to Support Queries

23
Understanding the Data
P410-411

• Logical and Physical Design
• Data Characteristics
• How Data Is Used
• The types of queries performed
• The frequency of queries that are typically
  performed

24
Indexing Guidelines
P410-411

• Columns to Index
• Primary and foreign keys
• Those frequently searched in ranges
• Those frequently accessed in sorted order
• Those frequently grouped together during
  aggregation
• Columns Not to Index
• Those seldom referenced in queries
• Those that contain few unique values
• Those defined with text, ntext, or image data
  types

25
Choosing the Appropriate Clustered Index
P410-411

• Heavily Updated Tables
• A clustered index with an identity column keeps
  updated pages in memory
• Sorting
• A clustered index keeps the data pre-sorted
• Column Length and Data Type
• Limit the number of columns
• Reduce the number of characters
• Use the smallest data type possible

26
Indexing to Support Queries
P410-411

• Using Search Arguments
• Writing Good Search Arguments
• Specify a WHERE clause in the query
• Verify that the WHERE clause limits the number of
  rows
• Verify that an expression exists for every table
  referenced in the query
• Avoid using leading wildcards

27
Maintaining Indexes
P412

• Data Fragmentation
• DBCC SHOWCONTIG Statement
• DBCC INDEXDEFRAG
• DROP_EXISTING Option

28
Data Fragmentation
P412

• How Fragmentation Occurs
• SQL Server reorganizes index pages when data is
  modified
• Reorganization causes index pages to split
• Methods of Managing Fragmentation
• Drop and recreate an index and specify a
  fillfactor value
• Rebuild an index and specify a fillfactor value
• Business Environment
• Data fragmentation can be good for OLTP
  environment
• Data fragmentation can be bad for Analysis
  Services environment

29
DBCC SHOWCONTIG Statement
P412

• What DBCC SHOWCONTIG Determines
• Whether a table or index is heavily fragmented
• Whether data and index pages are full
• When to Execute
• If tables have been heavily modified
• If tables contain imported data
• If tables seem to cause poor query performance

30
DROP_EXISTING Option
P412

• Rebuilding an Index
• Reorganizes leaf pages
• Removes fragmentation
• Recalculates index statistics
• Changing Index Characteristics
• Type
• Index columns
• Options

CREATE UNIQUE NONCLUSTERED INDEX
U_OrdID_ProdID ON Order Details (OrderID,
ProductID) WITH DROP_EXISTING, FILLFACTOR65
31
Setting Up Indexes Using the Index Tuning Wizard

• Use the Index Tuning Wizard to
• Recommend or verify optimal index configuration
• Provide cost analysis reports
• Recommend ways to tune the database
• Specify criteria when a workload is evaluated
• Do Not Use the Index Tuning Wizard on
• Tables referenced by cross-database queries that
  do not exist
• System tables, PRIMARY KEY constraints, unique
  indexes

32
Performance Considerations

• Create Indexes on Foreign Keys
• Create the Clustered Index Before Nonclustered
  Indexes
• Consider Creating Composite Indexes
• Create Multiple Indexes for a Table That Is Read
  Frequently
• Use the Index Tuning Wizard

33
Recommended Practices
Use Indexes to Enforce Uniqueness
34
Recommended Practices
35
Review

• Introduction to Indexes
• Index Architecture
• How SQL Server Retrieves Stored Data
• How SQL Server Maintains Index and Heap
  Structures
• Deciding Which Columns to Index
• Creating Indexes
• Creating Index Options
• Maintaining Indexes
• Setting Up Indexes Using the Index Tuning Wizard
• Performance Considerations

36
Lab Implementing Indexes
37
Lab Implementing Indexes

• Exercise 1
• Pages 399-401 Viewing Index Properties and Using
  an Index
• Exercise 2
• Pages 413-416 Creating a Clustered Index