© 2008 Quest Software, Inc. ALL RIGHTS RESERVED.

Conquering the Giants:Very Large Databases

For audio, call the phone number in your meeting invite.

Audio will not come through the computer speakers.

• SQL Server Expertfor Quest Software

• Editor-in-Chief at SQLServerPedia.com

• Former SQL DBA• Managed data

warehouses,>80tb SAN storage for $7b company

Conquering the Giants: Agenda• What’s a VLDB?• Ginormous Growth• Big Backups• Tremendous Tables• Slow SQL Statements• Your Wingman: Quest• Resources

Photo Licensed with Creative Commons from http://www.flickr.com/photos/pagedooley/2172001078/

What Makes a VLDB?• 1 terabyte• Billion rows

• But Not:User Qty,Criticality,Transactions Per Second

4

Photo Licensed with Creative Commons from http://www.flickr.com/photos/tcmhitchhiker/1053095394

5

VLDB Space Challenges

Default File Growth Settings

6

Starts Out Small…

7

Ginormous Growths

8

Instant File Init in Secpol.msc

9

VLDBs = Big Backups• Time Problem• Nightly Load Windows• Time to Recover• Dev, QA, Test, DR

10

Giant Monstrous Backups

11

Cutting Them Down to Size

12

Next Weapon: SAN Snapshots

13

Post-Snapshot

14

Making the Snap Available

15

Weapon: Backup Compression

16

Photo Licensed with Creative Commons from http://www.flickr.com/photos/tcmhitchhiker/1053045338/

Or – Compress Everything!

17

VLDB Problem: Giant Tables

18

Hunt Down and Kill Unused Indexes

19

Breaking Giants Into Pieces

20

The New Way: Partitioning

21

Partitioning Basics• Create filegroups• Create files in the filegroups• Design partition function• Design partition scheme• Alter table’s clustered index

22

Sliding Window Loads• Partition by day• Create new

table similarto Sales table,on same filegroup

• Load one day’sdata into empty table

• Swap out a partition for that table

23

Giant Drawbacks• Not easy to change• Requires lots of

experimentation• Partition elimination

doesn’t always work• Requires familiarity

with queries, data• Storage skills help• Hardware-dependent

24

Photo Licensed with Creative Commons from http://www.flickr.com/photos/cellphonesusie/3224335140/

SQLCAT MAXDOP FAQ

25

The New-New Way: 2008 R2

26

Slow SQL Statements

27

Your Shovel: Resource Governor• Slows zombie

queries down• Throttle CPU,

memory• Can throttle jobs,

backups too• Doesn’t throttle

storage (yet)

28

Photo Licensed with Creative Commons from http://www.flickr.com/photos/archiemcphee/3612278108/

Implementing The Governator• Workload groups:

– ETL processes– Reports– AdHoc

• Resource pool:– ETL– Reports

• Classification function based on:– Login name– Application name

29

Photo Licensed with Creative Commons from http://www.flickr.com/photos/shaunwong/2467881648/

End Results in SSMS 2008

30

Resource Governor Caveats• Ask the network team about QoS• Get manager consensus on SLAs• Keep it absolutely transparent• Avoid false alarms about performance

problems• Teach managers how to “see” it working

31

Your Wingman: Quest Software• Capacity Manager v3.0

– Capacity Planning– Index Defragmentation– Partitioning GUI

• LiteSpeed v5.1– SmartDiffs– Throttled Compression

32

Showing the Giant Who’s Boss• Use Instant File Initialization• Consider Differential Backups• Compress Data• Drop Baggage• Partitioning FTW• Resource

Governator• Check Out Quest

Capacity Manager

Photo Licensed with Creative Commons from http://www.flickr.com/photos/keeg/1661401660/

VLDB Resources• Presentation Resources:

http://sqlserverpedia.com/wiki/VLDB

• Quest Software:http://www.quest.com/sql-server/

• Upcoming Webcasts:http://www.quest.com/events