Compression

Get-DbaDbCompression

Jess Pomfret (@jpomfret), jesspomfret.com

Get-DbaDbCompression View Source Jess Pomfret (@jpomfret), jesspomfret.com Windows, Linux, macOS Synopsis Retrieves compression settings, sizes, and row counts for tables and indexes across SQL Server databases. Description This function analyzes data compression usage across your SQL Server databases by examining tables, indexes, and their physical partitions. It returns detailed information including current compression type (None, Row, Page, Columnstore), space usage, and row counts for each object. This is essential for compression optimization analysis, identifying candidates for compression to save storage space, and generating compliance reports on compression usage across your database environment.

Set-DbaDbCompression

Jason Squires (@js_0505), [email protected]

Set-DbaDbCompression View Source Jason Squires (@js_0505), [email protected] Windows, Linux, macOS Synopsis Applies data compression to SQL Server tables and indexes to reduce storage space and improve performance. Description Compresses tables, indexes, and heaps across one or more databases using Row, Page, or intelligent recommendations based on Microsoft’s Tiger Team compression analysis. Automatically handles the complex process of analyzing usage patterns, applying appropriate compression types, and rebuilding objects online when possible.

Test-DbaDbCompression

Jason Squires (@js_0505), [email protected]

Test-DbaDbCompression View Source Jason Squires (@js_0505), [email protected] Windows, Linux, macOS Synopsis Analyzes user tables and indexes to recommend optimal compression settings for storage space reduction. Description Performs comprehensive compression analysis on user tables and indexes to help DBAs identify storage optimization opportunities. Uses SQL Server’s sp_estimate_data_compression_savings system procedure combined with workload pattern analysis to recommend the most effective compression type for each object. This function analyzes your database workload patterns (scan vs update ratios) and calculates potential space savings to recommend ROW compression, PAGE compression, or no compression.