Share
Embed
Slide 1
Slide 1 text
WE DON'T NEED ROADS:
A LOOK INTO SQL SERVER
INDEXES
RICHIE RUMP
JORRISS LLC
@JORRISS
HTTP://JORRISS.NET
Slide 2
Slide 2 text
WHO IS THIS GUY?
Slide 3
Slide 3 text
WHAT WE’RE GOING
TO TALK ABOUT
• Why indexes?
• How indexes are used
• DB storage basics
• Index structure
• Clustered indexes vs non-clustered
• Includes, fill factor and covering
Slide 4
Slide 4 text
A TRAGEDY
Slide 5
Slide 5 text
Great Scott!
Slide 6
Slide 6 text
No content
Slide 7
Slide 7 text
WHY ARE INDEXES
IMPORTANT?
“An index makes the
query fast.”
Reference: http://use-the-index-luke.com/sql/anatomy
Slide 8
Slide 8 text
WHY ARE INDEXES
IMPORTANT?
“An index makes your
query fast.”
Reference: http://use-the-index-luke.com/sql/anatomy
Slide 9
Slide 9 text
HOW ARE INDEXES USED
BY SQL SERVER?
SQL Server creates a plan to
determine what’s the best way to
execute the query.
(Unlike some developers)
Slide 10
Slide 10 text
EXECUTION PLANS
Demo
Slide 11
Slide 11 text
TYPES OF INDEXES
• Clustered
• Nonclustered
• Heaps
• Column Store
• XML
• Spatial
• Full-Text
• Clustered
• Nonclustered
• Heaps
• Column Store
• XML
• Spatial
• Full-Text
Slide 12
Slide 12 text
HOW DO INDEXES
WORK?
In order to understand the magic of
indexes we need to understand how
SQL Server stores data.
Slide 13
Slide 13 text
HOW SQL SERVER
STORES INFORMATION
Pages
• Page Size: 8K
• Used to store just about
everything.
• Page Header: Metadata
about the page. (Page
Number, Type, etc.)
• Offset Array: Pointers to
where each row begins.
Page Header
Records
Offset Array
Slide 14
Slide 14 text
HOW SQL SERVER
STORES INFORMATION
Page Header
Row 1
1
Page Header
Row 1
1
Row 2
2
Page Header
Row 1
1
3
Row 2
Row 3
2
Slide 15
Slide 15 text
WHAT HAPPENS WHEN
A PAGE IS FULL?
Page Header
Row 1
1
Row 2
Row 3
Row 4
Row 5
2
3
4
5
Page Header
Row 6
6
Row 7
Row 8
Row 9
Row 10
7
8
9
10
Page Header
Row 11
11
Row 12
Row 13
Row 14
Row 15
12
13
14
15
Slide 16
Slide 16 text
HOW INDEXES ARE
STORED
• B-Tree or Balanced Tree
• The tree is made up of Pages
Slide 17
Slide 17 text
Leaf Level
Intermediate Level
Root Level
HOW ARE INDEXES
STORED
1 -
400
201 -
400
1 -
200
151 -
200
101 -
150
251 -
300
201-
250
101 -
200
201 -
300
301 -
400
1 -
100
301 -
350
51 -
100
1 -
50
351 -
400
Slide 18
Slide 18 text
HOW ARE INDEXES
STORED
1 -
400
201 -
400
1 -
200
151 -
200
101 -
150
251 -
300
201-
250
101 -
200
201 -
300
301 -
400
1 -
100
301 -
350
51 -
100
1 -
50
351 -
400
SELECT UserId, Title
FROM Posts
WHERE UserId = 160
CREATE NONCLUSTERED INDEX
IX_Posts_OwnerUserId
ON Posts (OwnerUserId)
Slide 19
Slide 19 text
CLUSTERED INDEXES
• What is a table, Alex?
• Only one per table!
• Can the key be more than one
column.
• Must be unique (SQL Server will
help if it isn’t.)
• Can’t be more than 900 bytes
combined
Slide 20
Slide 20 text
Leaf Level – THIS IS SPARTA THE DATA!
Intermediate Level
Root Level
CLUSTERED INDEXES
1 -
400
201 -
400
1 -
200
151 -
200
101 -
150
251 -
300
201-
250
101 -
200
201 -
300
301 -
400
1 -
100
301 -
350
51 -
100
1 -
50
351 -
400
Slide 21
Slide 21 text
NONCLUSTERED
INDEXES
• Subset of data optimized for
searching.
• Key can be more than one column
• Can’t be more than 900 bytes
combined
• Doesn’t have to be unique.
• Max of 999 per table.
(If I find 999 indexes on your table I will find you…)
Slide 22
Slide 22 text
Leaf Level – Key Data and Included columns.
Intermediate Level
Root Level
NONCLUSTERED
INDEXES
1 -
400
201 -
400
1 -
200
151 -
200
101 -
150
251 -
300
201-
250
101 -
200
201 -
300
301 -
400
1 -
100
301 -
350
51 -
100
1 -
50
351 -
400
Slide 23
Slide 23 text
CLUSTERED AND
NONCLUSTERED INDEXES
Demo
Slide 24
Slide 24 text
DEMO REVIEW
• Key lookups are slow. Use Includes
to combat.
• Use columns in the index key if you
intend on searching them.
• Index order matters!
• Suggested indexes may not be the
best way to go.
Slide 25
Slide 25 text
SORRY, YOUR PAGE IS
IN ANOTHER CASTLE
Page Header
Page Header
Id: 2
1
Id: 3
Id: 7
Id: 12
Id: 14
2
3
4
5
Id: 6
1
2
3
Slide 26
Slide 26 text
FILL FACTOR
• Minimizes page splits by leaving empty
space in the page.
• Is a number 0 to 100.
• A value of 80 means that 80% of the
page will be filled with data.
• 0 and 100 both mean that 100% of the
page will be filled with data.
• Only applies to leaf pages
Slide 27
Slide 27 text
DISADVANTAGES OF
INDEXES
• Increases storage.
• Creating duplicate copies of data.
• Insert, Updates and Deletes are slower.
• The DB has to delete data from the table
and all of the related indexes.
• Additional maintenance
• Cannot create index on
NVARCHAR(max)
Slide 28
Slide 28 text
BONUS! ENTITY
FRAMEWORK!
Demo
Slide 29
Slide 29 text
REFERENCES
Slide 30
Slide 30 text
THANK YOU
Richie Rump
@Jorriss
http://jorriss.net
http://dotnetmiami.com
http://statisticsparser.com