size does matter

Database Design: Size DOES Matter!

Thomas LaRock

Technical Evangelist and Senior DBA

Confio Software

3/19/2013 1

Why Are You Here?

• You have data

• It is growing larger

• Performance suffers

• You want to make things better

– Or you have been told to make things better

3/19/2013 2

A little about me…

Thomas LaRock

[email protected] thomaslarock.com

@sqlrockstar

http://www.microsoft.com/sqlserverlabs

Agenda

• What’s the problem?

• Why datatypes matter

• Solution options

3/19/2013 4

THE PROBLEM

3/19/2013 5

The Problem

• Your database has a design

– Just like a ship, or a car, has a design

3/19/2013 6

The Problem



3/19/2013 7

The Problem



• Data goes in, rarely falls off

3/19/2013 8

The Problem




• Databases are mixed-use

3/19/2013 9

The Problem





• Data professionals are often asked to convert a pickup truck into

3/19/2013 10

The Problem





• Data professionals are often asked to convert a pickup truck into a Ferrari

3/19/2013 11

The Problem





• Data professionals are often asked to convert a pickup truck into a Ferrari

• Great database performance starts with great database design

3/19/2013 12

How Database Designs Fail

• Many designs do not account for size

3/19/2013 13



• Rowcounts might be considered

3/19/2013 14



• Rowcounts might be considered

• What about inside the rows?

– Those are the columns

– Their width is often overlooked

3/19/2013 15

WHY DATATYPES MATTER

3/19/2013 16

3/19/2013 ‹#›

What Is an Integer?

• Math major says: integers are numbers, positive and negative, including 0, without any fractions

3/19/2013 25

What Is an Integer?


• Database designer: integers are the range of numbers from -2,147,483,648 to 2,147,483,647 and require 4 bytes of storage

3/19/2013 26

What Is an Integer?


• Database designer: integers are the range of numbers from -2,147,483,648 to 2,147,483,647 and require 4 bytes of storage

• Do you see a disconnect there?

3/19/2013 27

Visual Studio Defaults

3/19/2013 28


3/19/2013 29


3/19/2013 30


3/19/2013 31

Mismatch Likely!

SSMS Defaults

3/19/2013 32

SSMS Defaults

3/19/2013 33

SSMS Defaults

3/19/2013 34

SSMS Defaults

3/19/2013 35

Date Example

Datatype Name Length (bytes)

Date 3

Smalldatetime 4

Time 5

Datetime2 6, 7, or 8

Datetime 8

Datetimeoffset 10

3/19/2013 36

Where Will This Hurt?

• Disk space

• Memory space

• Performance

3/19/2013 37

Datatype Pain #1: Disk Space

• Disk is cheap…sure it is…but there are hidden costs!

– More storage = longer maintenance

– More storage = longer database backups

– More storage = longer tape backups

3/19/2013 38

Datatype Pain #1: Disk Space

• Disk is cheap…sure it is…but there are hidden costs!

– More storage = longer maintenance

– More storage = longer database backups

– More storage = longer tape backups

• Most are unaware how that tiny piece of data is stored multiple times

– Wide clustering key is spread to EVERY NC index!

3/19/2013 39

Datatype Pain #2: Memory Space

• Those extra bytes are read from disk into SQL Server buffer cache

3/19/2013 40



• Extra Logical I/O needed to return a query result

3/19/2013 41



• Extra Logical I/O needed to return a query result

• What about indexes?

– Same thing, they drag around the extra I/O

3/19/2013 42

EXTRA LOGICAL I/ODEMO

3/19/2013 43

Datatype Pain #3: Performance

• Datatype mismatch results in implicit conversions

3/19/2013 44



• Optimizer knows this is bad, will display a warning for you

3/19/2013 45




• It is up to you to make a change, SQL Server won’t change your code for you!

3/19/2013 46




• It is up to you to make a change, SQL Server won’t change your code for you!

• Issue with code generators (ADO.NET 3.5, EMF, nHibernate, LINQ)

– Supposedly fixed?

3/19/2013 47

IMPLICIT CONVERSIONSDEMO

3/19/2013 48

SOLUTION OPTIONS

3/19/2013 49

First Step: Admit You Have a Problem!

• First sign: when OLAP queries interfere with OLTP processing

3/19/2013 50



• For SQL Server, that typically means high levels of locking waits and blocking

3/19/2013 51



• For SQL Server, that typically means high levels of locking waits and blocking

• Another sign: plan cache bloat

3/19/2013 52

Solutions

• Find duplicate/unused/misused indexes

• Compression

• Filtered indexes

• Archiving/partitioning

• Verify datatypes are correct

• Update code

• Update architecture

3/19/2013 53

Index Maintenance

• Want to reduce the extra I/O

– Remove duplicate indexes

– Remove unused/misused indexes

3/19/2013 54

Index Maintenance

• Want to reduce the extra I/O

– Remove duplicate indexes

– Remove unused/misused indexes

• Test thoroughly!

– Examine plan cache to find if indexes are used

– Plan cache resets on service restart (or manual)

3/19/2013 55

Solutions


• Compression




• Update code


3/19/2013 57

Compression

• Native compression has two steps

– Row compression

– Page compression

– Each table/index requires compression to be enabled

3/19/2013 58

ROW/PAGE COMPRESSIONDEMO

3/19/2013 59

Compression

• Native compression has two steps

– Row compression

– Page compression

– Each table/index requires compression to be enabled

• ColumnStore

– Dictionary compression

– Segments and batches

3/19/2013 60

Solutions


• Compression




• Update code


3/19/2013 61

Filtered Indexes

• Look like regular indexes

– Contain a WHERE clause

• Smaller footprint

• Less logical I/O

3/19/2013 62

FILTERED INDEXESDEMO

3/19/2013 63

Solutions


• Compression




• Update code


3/19/2013 64

Partitioning

• Can be viable alternative to proper archiving

3/19/2013 65

Partitioning


• Queries hit only the partitions necessary

– Better than full table scans

3/19/2013 66

Partitioning




• Easier to maintain

– Not necessarily easier to administer!

3/19/2013 67

Partitioning




• Easier to maintain

– Not necessarily easier to administer!

3/19/2013 68

Solutions


• Compression




• Update code


3/19/2013 69

Verify Datatypes

• Compare datatype definition to actual data

– i.e., BIGINT defined, but only SMALLINT used

3/19/2013 70

Verify Datatypes



• You can estimate savings

– Space as well as LIO

3/19/2013 71

Verify Datatypes





• Can be intrusive to alter

– Especially if PK/FK defined

3/19/2013 72

Verify Datatypes





• Can be intrusive to alter

– Especially if PK/FK defined

• Periodically check to make sure you are not running out!

3/19/2013 73

VERIFY DATATYPESDEMO

3/19/2013 74

Solutions


• Compression




• Update code


3/19/2013 75

Update Code/Architecture

• If you are seeing the signs, then you may consider updating code

3/19/2013 76



• Some frameworks are not optimal

– ADO.NET 3.5

– LINQ to SQL

– EMF

3/19/2013 77



• Some frameworks are not optimal

– ADO.NET 3.5

– LINQ to SQL

– EMF

• Scale out architecture

– Create real reporting solution

– AlwaysOn

3/19/2013 78

10 Steps For Right Sizing

1. Understand the storage requirements for every datatype you consider

3/19/2013 79



2. Review all design decisions based on the shape of the data – where it is now and where it is likely to be later.

3/19/2013 80



2. Review all design decisions based on the shape of the data – where it is now and where it is likely to be later.

3. Set datatypes based on business requirements, not tool defaults

3/19/2013 81


4. Measure and monitor fit of the data to its datatypes regularly

3/19/2013 82



5. Review each index creation request to see if it is duplicate

3/19/2013 83



5. Review each index creation request to see if it is duplicate

6. Measure for unused and duplicate indexes regularly

3/19/2013 84


7. Review new stored procedures to verify parameters are matching

3/19/2013 85



8. Find longest running and most often used queries

3/19/2013 86




9. Look for implicit conversions in your plan cache

3/19/2013 87




9. Look for implicit conversions in your plan cache

10.Remember that size matters

3/19/2013 88

Summary

• What’s the problem?

• Why datatypes matter

• Solution options

3/19/2013 89

For More Information

• http://tinyurl.com/sql-datatypes

• http://tinyurl.com/imp-cols-in-plan-cache

• http://tinyurl.com/data-access-perf

• http://tinyurl.com/row-compression

3/19/2013 90

size does matter

Technology

great database design319201312

range of numbers

mixedusedata professionals

math major

pickup truck into319201310

fractionsdatabase designer

things betteror

things better31920132a