/
SQL Server Index Internals SQL Server Index Internals

SQL Server Index Internals - PowerPoint Presentation

jane-oiler
jane-oiler . @jane-oiler
Follow
460 views
Uploaded On 2018-02-22

SQL Server Index Internals - PPT Presentation

Tim Chapman Premier Field Engineer Please silence cell phones Explore Everything PASS Has to Offer Free SQL Server and BI Web Events Free 1day Training Events Regional Event Local User Groups Around the World ID: 634193

rebuild index indexes table index rebuild table indexes statistics data fragmentation clustered online structure logical pages sql columns page

Share:

Link:

Embed:

Download Presentation from below link

Download Presentation The PPT/PDF document "SQL Server Index Internals" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.


Presentation Transcript

Slide1

SQL Server Index Internals

Tim Chapman

Premier Field

EngineerSlide2

Please silence

cell

phonesSlide3

Explore Everything PASS Has to Offer

Free SQL Server and BI Web Events

Free 1-day Training Events

Regional Event

Local User Groups Around the World

Free Online Technical Training

This is Community

3

Business Analytics Training

Session Recordings

PASS Newsletter Slide4

Session Evaluations

ways to access

Go to

passsummit

/

evals

Download

the

GuideBook

App and search:

PASS Summit 2013

Follow the QR code link displayed on session signage throughout the conference venue and in the program guide

Submit by 5pm

Friday Oct. 18 to

WIN prizes

Your feedback is

important and valuable.

4Slide5

About Me

Tim Chapman

Dedicated Premier Field Engineer at Microsoft

Contributing author SQL Server 2012 Bible

SQL Server MVP Deep Dives 2

@

chapmandewSlide6

Session Goals

Index structure familiarity

I won’t break out a debugger

But, I will show you some neat tools for viewing indexesHave some fun talking about indexes!Slide7

Heaps

Tables without a clustered index

Unordered masses of data

Data rows fit where they can – PFS pages usedGreat for quickly importing large sets of data

Not great for most production environments

Use ALTER TABLE…REBUILD to “rebuild”Slide8

Index Structures

Clustered

Indexes

Non-Clustered

IndexesSlide9

Clustered Indexes

Implemented as a

B-Tree data structure

Logical order must always be maintained

The

leaf level of the index

contains all table columns

Why there is only one per table – the index IS the table

We always implement these as uniqueSlide10

Clustered Index StructureSlide11

Non-clustered Indexes

Also a

B-Tree structure data structure

Is NOT part of the table it is defined onIt MUST point to the base table somehow

Only a subset of the table columns

A

skinny table for fast searching and

sortingSlide12

Non-clustered Index StructureSlide13

Included Columns

Added columns of data in the

leaf level

of an NC indexUsed for covering queriesNot restricted to the NC index 900 byte size restriction

You can use (n)

varchar

(max), but not (n)text or image data typesSlide14

Included ColumnsSlide15

Demo

Investigate Index/Data PagesSlide16

Statistics

A sampling of the data in a given table/index column

The

optimizer relies on these for decision makingOut of date or skewed statistics can

lead to sub-optimal execution

plansSlide17

Showing Statistics…Slide18

Database Statistics Options (1)

AUTO_CREATE_STATISTICS

AUTO_UPDATE_STATISTICS

AUTO_UPDATE_STATISTICS_ASYNCSlide19

Database Statistics Options – Best Practices

Use the defaults unless you NEED to do

otherwise

Often large DW workloads are the exceptionNote: Trace Flag 2371 can help with RT issues.Slide20

Demo

Out of date statisticsSlide21

Index Maintenance

Reviewing fragmentation

Affects

of fragmentationRebuild vs ReorganizationSlide22

Page Splits

A record must always be placed on a specific page

We must maintain the index logical order

If the record doesn’t fit, we must do some rearrangingThis is resource intensive - causes

logical fragmentationSlide23

Logical Fragmentation

Index/Data pages

not physically and logically aligned

Can hurt scan performance, but not seek operationsSlide24

Page Density

How full a page is

upon a (re)build/reorganization

More full pages can cause page splitsLess full pages can waste Buffer Pool spaceSlide25

When does fragmentation matter?

Negligible for singleton lookup seek

operations

Matters most for scanning purposesNote: If your index is highly fragmented, there is a good chance your statistics are skewed or out of date.Slide26

Demo

The effects of Index FragmentationSlide27

Rebuilding an index (1)

Very few uses for DROP INDEX…CREATE INDEX

All NC indexes rebuilt twice if you do this with the clustered index

You must know the exact index structure for recreationALTER INDEX…REBUILD

NC indexes

not automatically rebuilt if done on a clustered index

ALTER TABLE…REBUILD

Use for Heap tables – will always rebuild all NC indexes tooSlide28

Rebuilding an Index (2)

Offline rebuilds

locks the index during the operation

NOT the entire table – though that can certainly be the case ALTER INDEX ALLRebuilds all indexes on the table in

index_id

order

DROP_EXISTING

Part of the CREATE INDEX syntax

Great for changing the index definitionSlide29

Index Reorganization

Exclusively locks, compacts and reorders 8 pages

at a

timeRemoves leaf level fragmentation

Tries

to establish the original fill factor

Always: single threaded, OnlineSlide30

Rebuild vs. Reorganize Strategy

(1)

When to do what

?Common wisdom is:If <= 30% logical fragmentation THEN ReorganizeIf > 30% logical fragmentation THEN Rebuild

…your mileage will vary. Choose what works for you.Slide31

Rebuild vs.

Reorganize

Strategy (2)

Reorganize…Is always an online operationCan be stopped and you won’t lose the work accomplished

Generates a LOT more t-log records than Rebuild

Typically will not remove as much fragmentation as Rebuild

Never updates StatisticsSlide32

Rebuild vs.

Reorganize

Strategy (3)

Rebuild…Is an atomic operation – all happens or none happensWill update statistics with FULLSCAN One caveat to this in 2012Will involve some blocking – even Online operations

Can be minimally loggedSlide33

Demo

Looking at transaction log usage from an index rebuildSlide34

SQL Server 2012 Index Changes

Rebuild indexes with (B)LOB columns online

Online rebuilds of partitioned tables do not always result in a 100% sample rateSlide35

SQL 2014 Index Changes

Online operation lock priority

Hash and

Bw-tree Indexes in Hekaton

Create an index in table definitionSlide36

Questions?Slide37

Resources

37

@sqlserver

@microsoftBI

#powerbi

microsoft.com/sqlserver

Exams 50% off

Virtual Labs

Windows Azure

Power BI

Microsoft Virtual Academy

mva

Power BI

L A B S