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
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.
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