Squeezing Data With Compression
Compañeros! Did you know that according to an unofficial Twitter poll, only 60% percent of SQL Server Enterprise users said that they took advantage of the compression features? In this episode of the SQL Data Partners Podcast I chat with Jason Brimhall, about why that he thinks that is. He also answers your SQL Server compression questions: Why is it a bad idea to compress everything in your database? How does compression work? Why doesn’t compression always work? We also talk about why compression sometimes doesn’t help and he reveals a case study where compression moved the performance needle way back.
Listen to Learn
- The changes to compression in 2016
- How compression affects CPU cycles and performance
- The link between compression and indexes
- When to use compression
- How to tell if compression will work for your database
- The deprecated SQL Server feature that compression relies on
- The importance of test environments when using compression
- The company motto that Jason says is the best advice he’d ever received
- The mid-nineties movie that Jason would love to live out
This episode is sponsored by DBmaestro
As a Microsoft Certified Master/Microsoft Certified Solutions Master and SQL Server MVP, I have over 20 years’ experience in the technology industry, including more than 10 with SQL Server. I also earned a Bachelor’s Degree in Business Information Systems from Utah State University. One of the highlights of my career was co-authoring multiple volumes in the SQL Server T-SQL Recipes series of books. I am a frequent presenter at SQL Server events worldwide, which includes SQLSaturdays and User Groups.
I love finding that magic turbo button to make queries go faster which ultimately leads to huge relief and satisfaction for the client. I have done that sort of work for clients of all sizes throughout the world. I have experience with multi-TB databases as well as environments with thousands of SQL Server instances to manage.
…Most of the time, because of the huge decrease in IO that is occurring, you see an improvement in performance. Queries improve dramatically in performance. I have seen, consistently, five to six times performance improvement when you compress tables.
Meet the Hosts
With more than 10 years of working with SQL Server, Carlos helps businesses ensure their SQL Server environments meet their users’ expectations. He can provide insights on performance, migrations, and disaster recovery. He is also active in the SQL Server community and regularly speaks at user group meetings and conferences. He helps support the free database monitoring tool found at databasehealth.com and provides training through SQL Trail events.
Eugene works as an independent BI consultant and Pluralsight author, specializing in Power BI and the Azure Data Platform. He has been working with data for over 8 years and speaks regularly at user groups and conferences. He also helps run the GroupBy online conference.
Kevin is a Microsoft Data Platform MVP and proprietor of Catallaxy Services, LLC, where he specializes in T-SQL development, machine learning, and pulling rabbits out of hats on demand. He is the lead contributor to Curated SQL, president of the Triangle Area SQL Server Users Group, and author of the books PolyBase Revealed (Apress, 2020) and Finding Ghosts in Your Data: Anomaly Detection Techniques with Examples in Python (Apress, 2022). A resident of Durham, North Carolina, he can be found cycling the trails along the triangle whenever the weather's nice enough.