To compress or decompress that is this session Melissa Connors Melissa Connors Senior Technical Writer Special Projects Lead Dog person Befriender of chipmunks Reader of books Baker of cakes ID: 930802
Download Presentation The PPT/PDF document "Understanding SQL Server Data Compressio..." 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
Understanding SQL Server Data Compression
To compress or decompress – that is this session!
Melissa Connors
Slide2Melissa Connors
Senior Technical Writer / Special Projects Lead
Dog person / Befriender of chipmunks / Reader of books / Baker of cakes
Slide3S1 Provides End-to-End Visibility Across the Data Platform
Monitor, diagnose, and optimize SQL Server performance.
Find and fix SQL Server Analysis Services (SSAS) performance problems
Monitor resource utilization (memory, CPU, network, storage) on VMware environments.
Boost Windows Server and Hyper-V virtualized environment performance.
See performance information for Microsoft APS, including data movement and details about distributed queries.
Accelerate Azure SQL Data Warehouse performance with visibility into workload impacts.
Keep Azure SQL Databases running at peak efficiency with key performance metrics and top queries.
Slide4Agenda
SQL Server Editions
Compression Basics
Compression ExamplesCase Study with SentryOne Database
Compression ChecklistSome Helpful T-SQL
We’re going to save the storage!
Slide5SQL Server Editions
SQL Server 2008, 2012, 2014 Enterprise-Only Feature
SQL Server 2016 (SP 1+)
https://sqlperformance.com/2016/11/sql-server-2016/big-deal-sp1
This is a BIG DEAL!
Slide6SQL Server Compression
Often avoided because of performance concerns
Done carefully, performance overhead is not an issue
Data Compression: Strategy, Capacity Planning, and Best Practices
https://msdn.microsoft.com/en-us/library/dd894051.aspx
Slide7Compression Methods
None
(the “default” or “storage inconsiderate”)
Row (the “kind of compressed” or “storage considerate”)Page (the “actually compressed” or “storage activist”)
Slide8Estimating Compression
ROW
PAGE
What’s going on over here? Calculations w/ fragmentation, fill factor variables
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-estimate-data-compression-savings-transact-sql
Slide9Row Compression
I think of it as more “smart-storage” than “data compression”
Essentially stores data types using only the bytes necessary for the value.
An int uses 4 Bytes of storage, whether it is 2 or 2,147,483,647. With row compression, 2 should use 1 Byte of storage space.
Trailing zeros, padded, and blank characters are not stored, where applicable. Row Compression Implementation
https://msdn.microsoft.com/en-us/library/cc280576(v=sql.130).aspx^Table showing “Data type” | “Is storage affected?” | “Description”
Slide10Row Compression Example
AdventureWorks2014 database
Slide11Page Compression
Actual
Compression
(like, for real this time)Row Compression PLUS the followingPrefix Compression
Prefix value selected for efficient storageStored values modified based on prefix values
Dictionary CompressionEliminates redundant storage of repeated values
Slide12Page Compression
Page Compression
Implementation
https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/page-compression-implementation?view=sql-server-2017
Slide13Page Compression Example
Compression = NONE
Compression = ROW
Compression = PAGE
?
Slide14DBCC IND | PAGE
You don’t need to know anything about DBCC IND or DBCC PAGE to use Data Compression.
The following slides are informational-only. (i.e. extra credit)
They’re here for your future reference. Allows you to see how individual pieces of data are affected by compression.
http://erinstellato.com/resources/dbcc/https://www.pluralsight.com/courses/sql-server-understanding-using-dbcc-commands
For the DBCC curious
Slide15DBCC IND
DBCC PAGE Info:
http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-page/
(
Database|Table|IndexID)
Slide16DBCC PAGEDBCC PAGE Info:
http://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-page/
TRACEON so you see the results.
(
DatabaseName|DatabaseID|FileNumber|PageNumber|PrintOption)
Slide17DBCC PAGE | sys.fn_PhysLocCracker
sys.fn_PhysLocCracker
Info:
http://www.sqlservercurry.com/2015/02/sql-server-find-physical-location-of.html
NONE
ROW
PAGE
Slide18n = 1 Compression = NONE
Slide19n = 1 Compression = ROW
Slide20n = 1 Compression = PAGE
Slide21n = 100 Compression = NONE
Slide22n = 100 Compression = ROW
Slide23n = 100 Compression = PAGE
Slide24n = 1000 Compression = NONE
Slide25n = 1000 Compression = ROW
Slide26n = 1000 Compression = PAGE
Slide27n = 12345 Compression = NONE
Slide28n = 12345 Compression = ROW
Slide29n = 12345 Compression = PAGE
Slide30n = 100000 Compression = NONE
Slide31n = 100000 Compression = ROW
Slide32n = 100000 Compression = PAGE
Slide33n = 1000000 Compression = NONE
Slide34n = 1000000 Compression = ROW
Slide35n = 1000000 Compression = PAGE
Slide36DBCC PAGE Results (Summary)
https://blogs.sentryone.com/melissaconnors/visualizing-data-compression/
Slide37What About Person.Person?
I’ll look for the most Melissa page in the table. Page 3089 has 6
Melissas
!
CompressionInfo
Raw Bytes0000000000000000: 02000048 05210d40 00080002 0a18494e 104d656c ...H.!.@......IN.
Mel
0000000000000014: 69737361 8180a23b 00000000 01010023 05dfff02
issa
..¢;.......#.ßÿ.
Slot 0 Column 1 Offset 0x9 Length 4 Length (physical) 2
BusinessEntityID
= 7342
Slot 0 Column 2 Offset 0x0 Length 4 Length
(physical) 0PersonType =
IN Slot 0 Column 3 Offset (see CD array entry) Length 1NameStyle = 0 Slot 0 Column 4 Offset 0x0 Length 0 Length (physical) 0Title = [NULL]
Slot 0 Column 5 Offset 0x0 Length 14 Length (physical) 0FirstName = Melissa
Table Compression = PAGE
Tip: Check the PowerPoint notes to see none (14) vs. row (7) vs. page (0) info for this data
Slide38Will My Data Compress Well?
You’ve seen how compression works with repeating values and prefixes
Knowing how compression works and what your data looks like will assist in deciding what to compress
Become a Data Compression Whisperer
Slide39Data Compression: A Case Study
Examples explore data from a SentryOne database
What to compress? (decisions, decisions,
decision tables)Which compression method should you use? Why not all
?If you have three indexes on a table, you could row compress one, page compress another, and leave one of them not compressed.
Mix ‘n’ Match Compression
Slide40Data Compression & SentryOne
Slide41Does it Compress Well?
Slide42Why Did it Compress Well?
Slide43Quick Look at the DBCC PAGE…
No Compression
Slot 0 Column 3 Offset 0x14 Length 16 Length
(physical) 16EventSourceID = 0c260691-16c3-4bc9-8457-220bc4457749 Slot 0 Column 6 Offset 0x24 Length 8 Length (physical) 8RemoteSequenceID = 1 Slot 0 Column 7 Offset 0x9b Length 72 Length (physical) 72RemoteObjectID
= 731dfa50-9458-48b3-9640-a3aec5b0da11 Page CompressionSlot 0 Column 3 Offset 0x0 Length 16 Length (physical) 0EventSourceID = 0c260691-16c3-4bc9-8457-220bc4457749
Slot 0 Column 6 Offset 0x12 Length 8 Length (physical) 1RemoteSequenceID = 1 Slot 0 Column 7 Offset 0x0 Length 72 Length
(physical) 0
RemoteObjectID
= 731dfa50-9458-48b3-9640-a3aec5b0da11
Storage size of 96 vs. 1 with page compression!
Slide44Does it Compress Well?
Slide45Why Didn’t it Compress Well?
Slide46Quick Look at the DBCC PAGE…
No Compression
Slot 1 Offset 0x626 Length 1343 (the entire row)
PlanHash = [Binary data] Slot 1 Column 2 Offset 0x8 Length 16 Length (physical) 160000003922AFA62E: a37e9b29 b662bd8c cd0bf936 e3261b60 £~)¶b½Í.ù6ã&.`Page CompressionSlot 1 Offset 0x629 Length 1314 (the entire row)PlanHash = [Binary data] Slot 1 Column 2 Offset 0x1c Length 16 Length (physical) 16000000350F460078: a37e9b29 b662bd8c cd0bf936 e3261b60 £~)¶b½Í.ù6ã&.`
Metadata + page compression on index with only that column uses more space than no compression.
Slide47How Much is the CPU Overhead?
You will need to test this!
You can somewhat predict this based on how the data is used:
Data is mostly read?
Lower CPU impactData is mostly updated?Higher CPU impact
You will *STILL* need to test this!
& if using partitioning, maybe that workload pattern varies by partition.
Slide48Scans vs. Updates
Two important queries within
Data Compression: Strategy, Capacity Planning, and Best Practices
U: Percent of Update Operations on the ObjectS: Percent of Scan Operations on the Object
https://msdn.microsoft.com/en-us/library/dd894051.aspx
Slide49Scans vs. Updates
Slide50Scans vs. Updates
Slide51Decision-Making Tables
Analyze the largest tables in the database
Example:
Disk SpaceTiny <= 512MBSmall >512MB && <= 1GBM
edium >1GB && <= 5GBLarge >5GB && <= 10GBHuge >10GB && <= 15GBGigantic >15GB
Slide52Decision-Making Tables
Slide53CPU Overhead
Slide54Other Considerations
Applying Compression
Overhead
TimeIndex MaintenanceIncreased Overhead
Database BackupsTransaction Log and Tempdb
Rebuilding online/offline (space considerations)Transparent Data EncryptionTest if concerned, but should not impact TDE
Slide55Applying Compression
Slide56Applying Compression
Slide57Index Maintenance
Slide58Index Maintenance
Slide59Index Maintenance
Slide60Database Backups
Slide61Database Backups
Slide62Performance Benefits
Compression allows more data per page
Benefits of compression go beyond storage
MemoryDisk IO
Slide63Memory
Slide64Disk Latency
Slide65Reads
When executing a query, the amount of memory, response time, and number of logical reads are reduced when using compression.
The following shows stats on a query completed against a SentryOne table.
Slide66Disk-Related Waits
No Compression
Page Compression
Slide67Continue to Monitor
Table usage (i.e. scans & updates) may change as application functionality changes.
Routinely verify that you are still using correct compression methods per object.
Slide68Verify that the data compresses well
Gather information on scans vs. updates
Build decision-making tables
Create a baseline before making changesApply changes in test firstTest performance & compare to baselineVerify impact to maintenance
Continually monitor performanceCompressionChecklist
Slide69But Wait, There’s More!
You just saved a LOT of space (
Congratulations!
)The system is still HOLDING that space (Now the whales are sad)Do you
A) Leave the space alone because you’ll grow into it?B) Shrink the file or database to release the unused space?
Shrinking causes fragmentation, so you’ll need to reorganize
Slide70Helpful Compression T-SQL
Examples
Slide71Find Existing Data Compression
Slide72Apply Compression to a Table
This ALTER TABLE command applies compression to the table, which might be a heap or the clustered index.
Example with
EventSourceHistory
which has multiple indexes, apply and look for existing data compression:
Slide73Apply Compression to an index
dbo.EventSourceHistory
has 9 indexes. Want to compress one?
Don’t forget you can compress at the partition level if necessary. Ex:
REBUILD PARTITION = 1
Slide74Apply Compression to ALL Indexes
Are you sure you want to do this?
Slide75Apply Compression to ALL Indexes
Won’t work if any of the indexes cannot be compressed
Person.Person
has multiple XML indexes which will result in an error
Slide76Apply Compression w/ MAXDOP
MAXDOP may be set within the REBUILD statement
It doesn’t have to be 0 (all) or 1. If you can spare 2 or 4 CPU, for example, set it to such to save time.
Slide77Links
#
EntryLevel
: Compression & Data Types (Melissa Connors)Visualizing Data Compression : See it for Yourself (Melissa Connors)
Data Compression: Strategy, Capacity Planning, and Best PracticesRow Compression Implementation
Page Compression ImplementationA Big Deal : SQL Server 2016 Service Pack 1 (Aaron Bertrand)
Slide78Thank you!
Melissa Connors
Senior Technical Writer
Special Projects Leadmconnors@sentryone.com
https://www.linkedin.com/in/melissaconnors
@MelikaNoKaOi
?
Slide79