SQL Internals, Recovery Models, & Backups! OH MY!

By Bradley Ball MCTS, MCITP

Speaker Introduction Bradley Ball      

Over 10 Years IT Experience Previous experience DBA, for the U.S. Army, The Executive Office of the President Currently a Sr. SQL DBA Staff Specailist at Publix MCITP SQL 2005 DBA, MCTS SQL 2008 DBA Blog: http://www.SQLBalls.com Twitter: @SQLBalls

Agenda Internal Components  Internal Storage Structures  Recovery Models 



Backups Types

Why Learn the Internals

http://www.flickr.com/photos/billtimmins/26794483/sizes/z/in/photostream/

ACID

http://www.flickr.com/photos/sammyblot/5360856791/

Atomicity

Consistency Isolation Durability

Transaction Isolation Levels Serializable  Read Committed  Read Uncommitted  Repeatable Read  Snapshot Isolation 

Demo

On The Inside SNI

Relational Engine Command Parser

Optimizer Query Executor

Buffer Pool Storage Engine Transaction Manager

Plan Cache Access Methods

Buffer Manager

Data Cache DATA DATA

SQL OS

USER

SQL OS 

Preemptive Scheduling (OS)



Cooperative Scheduling (SQL)

Locks, Latches, Waits Locks (show a stop light)  Row, Page, and Table Escalation  Latches (who watches the watchmen)  Locks for Locks  Waits Cooperative Scheduling  Giving up our place in line willingly 

What Did We Just Cover 

SQL Internal Components

SQL Data Hierarchy IAM Chains and Allocation Units

Allocation Bit Maps

Extents

Pages

Records

What in the Shire Do Hobts Have to Do With My Data?! 

Allocation Units are made of 3 types  IN_ROW_DATA ○ Also known as HoBTs  Stands for Heap or B-Trees

 LOB_DATA ○ Also known as LOBs  Stands for Large Object Data

 ROW_OVERFLOW_DATA ○ Also known as SLOBs  Small Large Object Data

 Partitions

Heaps vs. Tables

B-Tree

Demo

What Did We Just Cover  SQL Internal

Components  Internal Storage Structures

Recovery Models Full  Bulk Logged  Simple 

Demo 

Show Recovery Model section in SSMS

What Did We Just Cover  Internal

Components  Internal Storage Structures  Recovery Models

Transaction Log

http://www.flickr.com/photos/jo-ah-chim/2350979305/sizes/z/in/photostream/

Transaction Log In Action In Simple Recovery

Virtual Log File

Demo

Backups Full  Transaction Log  Differential  Filegroup 

Demo

What Did We Just Cover  Internal

Components  Internal Storage Structures  Recovery Models 

Backups Types

Don’t Beat Yourself Up Nobody Knows everything  Nobody can know everything  Do your best  Never stop learning 

Resources

References   



Paul Randal MCM Video Series Data Structures http://bit.ly/hQO8ln Paul Randal MCM Video Series Log Files Internals & Maintenance http://bit.ly/hQO8ln Kalen Delaney, Paul Randal, Kimberly Tripp, Conor Cunningham, Adam Machanic Microsoft SQL Server 2008 Internals http://amzn.to/obxJim Christian Bolton, Justin Langford, Brent Ozar, James Rowland-Jones, Jonathan Kehayias, Cindy Gross, Steven Wort Professional SQL Server 2008 Internals and Troubleshooting http://amzn.to/na4idG

Questions

?