February 6, 2018 at 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?
February 6, 2018 at 9:41 am
leehbi - Tuesday, February 6, 2018 8:51 AMHi,
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
February 6, 2018 at 9:45 am
Yep I agree - I was more hoping for best practice but on reflection I guess there are trade offs all over the place.
February 6, 2018 at 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.
February 6, 2018 at 3:56 pm
Jason A. Long - Tuesday, February 6, 2018 1:44 PMWhen 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
February 6, 2018 at 4:55 pm
TheSQLGuru - Tuesday, February 6, 2018 3:56 PMJason A. Long - Tuesday, February 6, 2018 1:44 PMWhen 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.
February 7, 2018 at 4:36 am
Jason A. Long - Tuesday, February 6, 2018 4:55 PMTheSQLGuru - Tuesday, February 6, 2018 3:56 PMJason A. Long - Tuesday, February 6, 2018 1:44 PMWhen 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!! 🙂
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
February 7, 2018 at 10:29 am
TheSQLGuru - Wednesday, February 7, 2018 4:36 AMI 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!! 🙂
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.
February 7, 2018 at 8:21 pm
Thanks for the thoughts.
The Koenigseggs are FAR more than pure art. There is some SPECTACTULAR engineering function behind their form!! 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
February 7, 2018 at 9:08 pm
TheSQLGuru - Wednesday, February 7, 2018 8:21 PMThanks for the thoughts.The Koenigseggs are FAR more than pure art. There is some SPECTACTULAR engineering function behind their form!! 🙂
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply