SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DB compression with San compression


DB compression with San compression

Author
Message
leehbi
leehbi
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1392 Visits: 661
Hi,
We have table compression on for our big DW fact tables: Columnstore for some and traditional row store compression. Currently working with SAN engineer to see if we can get higher read speeds. SAN (all flash) box is compressed too.
Does turning compression off on the database help read speeds? Even if read speeds went up the level of IO would go up, possibly negating any improvement, plus don't want to lose the value of column store :-)
Does anyone have a view on how DB compression plays with SAN compression? Should one be turned off or do they play well together?
TheSQLGuru
TheSQLGuru
SSC Guru
SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)

Group: General Forum Members
Points: 101053 Visits: 8964
leehbi - Tuesday, February 6, 2018 8:51 AM
Hi,
We have table compression on for our big DW fact tables: Columnstore for some and traditional row store compression. Currently working with SAN engineer to see if we can get higher read speeds. SAN (all flash) box is compressed too.
Does turning compression off on the database help read speeds? Even if read speeds went up the level of IO would go up, possibly negating any improvement, plus don't want to lose the value of column store :-)
Does anyone have a view on how DB compression plays with SAN compression? Should one be turned off or do they play well together?

If you think your IO isn't performing at its best, the right way to go about things is to BENCHMARK various configurations. Then you are not guessing or basing a HUGE decision off of some forum posts!!

I would first start with the server hardware (including CPU and especially RAM) and then have a HARD look at EVERYTHING in the IO stack from RAM to actual ones and zeros in some dots somewhere. Find bottleneck,. remove, lather-rinse-repeat until you have "acceptable" IO.


Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
leehbi
leehbi
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1392 Visits: 661
Yep I agree - I was more hoping for best practice but on reflection I guess there are trade offs all over the place.
Jason A. Long
Jason A. Long
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13550 Visits: 6736
When we moved to a Pure Storage SAN, one of their boastful claims was their data compression and how good & fast it was. When we compared it SQL Server PAGE compression and presented them with our results... they shut up...

So, in our situation, sticking with SQL Server compression yielded the best results... Both in terms of speed and size on disk... BUT... As Kevin said, testing is the only way to know for sure what will work best in your specific environment.
TheSQLGuru
TheSQLGuru
SSC Guru
SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)

Group: General Forum Members
Points: 101053 Visits: 8964
Jason A. Long - Tuesday, February 6, 2018 1:44 PM
When we moved to a Pure Storage SAN, one of their boastful claims was their data compression and how good & fast it was. When we compared it SQL Server PAGE compression and presented them with our results... they shut up...

So, in our situation, sticking with SQL Server compression yielded the best results... Both in terms of speed and size on disk... BUT... As Kevin said, testing is the only way to know for sure what will work best in your specific environment.

Did you hit up Argenis Fernandez with your testing and results? I am sure he would be interested in that.


Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Jason A. Long
Jason A. Long
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13550 Visits: 6736
TheSQLGuru - Tuesday, February 6, 2018 3:56 PM
Jason A. Long - Tuesday, February 6, 2018 1:44 PM
When we moved to a Pure Storage SAN, one of their boastful claims was their data compression and how good & fast it was. When we compared it SQL Server PAGE compression and presented them with our results... they shut up...

So, in our situation, sticking with SQL Server compression yielded the best results... Both in terms of speed and size on disk... BUT... As Kevin said, testing is the only way to know for sure what will work best in your specific environment.

Did you hit up Argenis Fernandez with your testing and results? I am sure he would be interested in that.

Hopefully my original post didn't sound like I was bagging on Pure Storage... That wasn't my intention. Moving from spinning rust to Pure was like moving from grand dad's old Ford to a Koenigsegg...
As far as what got communicated to who... It was roughly 3 years ago and my personal, hands on, involvement was pretty limited. I have no idea who our contact at Pure was at that time.
I just remember working through the test plans with the two DBA's who worked on the project, helping to do some follow-up validation and general discussions about what needed to be done with index maintenance procedures in terms of keeping or dropping the existing SQL Server PAGE/ROW compression setting.
Of course that was 3 years ago, so I have no idea if Pure has updates their proprietary compression algorithm since then. It may be worth a retest...
Neither of the two aforementioned DBA's are still with the company but I may try to contact them and see if either held on to their original test procedures.

Keven - Let me know if you have specific tests you'd like to see run... I'm always interested in your insights and I'd be happy to work with you if you're interested.

TheSQLGuru
TheSQLGuru
SSC Guru
SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)

Group: General Forum Members
Points: 101053 Visits: 8964
Jason A. Long - Tuesday, February 6, 2018 4:55 PM
TheSQLGuru - Tuesday, February 6, 2018 3:56 PM
Jason A. Long - Tuesday, February 6, 2018 1:44 PM
When we moved to a Pure Storage SAN, one of their boastful claims was their data compression and how good & fast it was. When we compared it SQL Server PAGE compression and presented them with our results... they shut up...

So, in our situation, sticking with SQL Server compression yielded the best results... Both in terms of speed and size on disk... BUT... As Kevin said, testing is the only way to know for sure what will work best in your specific environment.

Did you hit up Argenis Fernandez with your testing and results? I am sure he would be interested in that.

Hopefully my original post didn't sound like I was bagging on Pure Storage... That wasn't my intention. Moving from spinning rust to Pure was like moving from grand dad's old Ford to a Koenigsegg...
As far as what got communicated to who... It was roughly 3 years ago and my personal, hands on, involvement was pretty limited. I have no idea who our contact at Pure was at that time.
I just remember working through the test plans with the two DBA's who worked on the project, helping to do some follow-up validation and general discussions about what needed to be done with index maintenance procedures in terms of keeping or dropping the existing SQL Server PAGE/ROW compression setting.
Of course that was 3 years ago, so I have no idea if Pure has updates their proprietary compression algorithm since then. It may be worth a retest...
Neither of the two aforementioned DBA's are still with the company but I may try to contact them and see if either held on to their original test procedures.

Keven - Let me know if you have specific tests you'd like to see run... I'm always interested in your insights and I'd be happy to work with you if you're interested.

I would love to do some playing around with you on this, but with compressed data testing can be somewhat of a mixed bag. If you have (or fabricate) data that fits well into a particular compression scheme things can rock, if not they may not. I am also unbelievably behind on work/life due to some really bad medical issues over the last 8 months. Crying My time posting on here is something of an escape for me, and even that has been greatly restricted.

BTW, I love your vehicular comparison!! Smile

I won't make it to many SQL Saturday's this year, but here's hoping we get the chance to catch up at one anyway.


Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Jason A. Long
Jason A. Long
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13550 Visits: 6736
TheSQLGuru - Wednesday, February 7, 2018 4:36 AM

I would love to do some playing around with you on this, but with compressed data testing can be somewhat of a mixed bag. If you have (or fabricate) data that fits well into a particular compression scheme things can rock, if not they may not. I am also unbelievably behind on work/life due to some really bad medical issues over the last 8 months. Crying My time posting on here is something of an escape for me, and even that has been greatly restricted.

BTW, I love your vehicular comparison!! Smile

I won't make it to many SQL Saturday's this year, but here's hoping we get the chance to catch up at one anyway.

Sorry to hear about the health issues. I hope everything is alright or at least on the mend.
I'll look forward to you getting back on your feet and producing more SQL Saturday content. I think I've seen everything I could find of yours on YouTube... Some, several times.
As far as testing... I hate to admit it but we have a lot of redundant/flat data that does lend itself to being compressed fairly substantially regardless of the algorithm but I don't know enough about the specific flavors to speak knowledgeably about one versus the other. In any case, I'll leave the offer as an open invitation. Just hit me up if you find a hole in your schedule and want to see how the Pure Storage stacks up against your other benchmarks.

As far as the vehicular comparison... I'm not much of a car guy but the Koenigseggs are pure art.

TheSQLGuru
TheSQLGuru
SSC Guru
SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)SSC Guru (101K reputation)

Group: General Forum Members
Points: 101053 Visits: 8964
Thanks for the thoughts.

The Koenigseggs are FAR more than pure art. There is some SPECTACTULAR engineering function behind their form!! Smile

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Jason A. Long
Jason A. Long
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13550 Visits: 6736
TheSQLGuru - Wednesday, February 7, 2018 8:21 PM
Thanks for the thoughts.

The Koenigseggs are FAR more than pure art. There is some SPECTACTULAR engineering function behind their form!! Smile

The fact that the company was only founded in 1994 by a guy that just wanted to build fast cars makes it all the more impressive.
New guy kicks down the door, and punches Ferrari, Lamborghini and Bugatti in the face and essentially creates an entirely new category of automobile.
If you want a good chuckle, watch the hour long documentary on the development of the Bugatti Chiron and their quest to create the ultimate 400 kmph hyper car.. Then watch any one of several videos of the Agera RS stomping it into the dirt... And the Regera is supposedly even faster than the Agera. Crazy Almost makes to feel bad for Bugatti.

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search