Find dismissed updates here
Edit My Preferences
Visit Pure.ai Sales +61 800 976 6494 Everpure Careers
Visit Pure.ai Sales +61 800 976 6494 Everpure Careers
Choose Your Region
Choose Your Region
Our Platform Products Solutions Support Partners Resources Built for AI
Contact Us Start Here
Sales 1-800-976-6494
Contact Us
Main Menu
Our Platform
Products
Solutions
Support
Partners
Resources
Built for AI
Contact Us

Shrink your Database Footprint

Explore how deduplication, compression, and efficient storage architecture can shrink database footprints, cut costs, and boost performance without sacrificing efficiency.
Walkthrough | 1427 min.
Watch Now
Resume Watching
The Everpure Platform
00:04
Hi, everyone. Take a moment. Think of your very largest databases in your environment today. Of those databases, how many of them happen to have a very large percentage of data never change again, things like sales order history data or maybe financial transaction information?
00:22
And do you happen to need to query that data a lot? Generally speaking, no. You might need to look back at the last couple of months of trade data, transactional information, or sales order history, but do you really need to look at the last ten years' worth of that STaaS? Well, why don't we just backup and clean-room
00:38
stuff out? It's bloating our database. Nope, nope, no, can't do that because, sorry, we need to run quarterly reports going back seven years. We might get audited. We need to keep that data around whether we like it or not. So unfortunately, that results in bloat for our databases, and that bloat results
00:56
longer backups and restores, other operational headaches that have consequences around the size of our databases, or even from a query performance perspective. Many of us happen to have code that love to do full table scans whether they need to which results in more IOPS. Well, today, I'd like to introduce you to a solution to that to help solve that problem,
01:17
data virtualization presented to you in SQL Server 2022 backed by Everpure FlashBlade Let's learn more. What is data virtualization? Let's take a table that's contains data that will never change again, and we're going to exfiltrate it out of our database, and we're going to convert it or materialize it as Parquet files.
01:37
These Parquet files are going to be stored in S3 object storage. Now remember, S3 object storage doesn't mean that you have to store this data on-prem. There are S3 object storage platforms that exist on-premises, like our FlashBlade, exactly where this data's going to go today. And then we'll be able to query it seamlessly with our SQL Server code
01:56
external table and combine that with a partitioned view to combine our Parquet files and our current data that we want to keep adding to and inserting into. And as a result, our database is gonna be a heck of a lot smaller, and that helps us out from an operational perspective and from a performance perspective. Let's see how this all works. First, I'm gonna be using this simple JSON
02:15
demo database, and I'm gonna show you the external data source that I have already created. This external data source, that's the underlying name of it, and here's its location, which is my FlashBlade, and I'm using an S3 access key that I've defined here as this particular credential. And then I've also defined what's called a
02:31
external file format, and we're gonna be using the Parquet file format today. And just to show you all of this works, I'm actually going to use a simple example using OPENROWSET to query a CSV file that also sits inside of one of my buckets on the FlashBlade. So as you can see, I can do this kind of really interesting ad hoc querying against CSV
02:51
files that are res- that reside in object storage. But, you know, we can do a lot better than just JSON files. So let's see how all this works. First of all, I'm gonna show you the reviews table. I'll just do a quick select top, ten records out of it.
03:04
And then I'm also gonna show you, the size of it and the data distribution by calendar year of this data. So you see that we have a bunch of different IDs in here for the recipe, that it is associated with, the author who wrote it, the rating that they happen to have given for that given recipe, and other information, including a lot of text information of the, recipe review itself.
03:24
And we see that this table contains 1.4 million records. And finally, we see that it contains many years' worth of data, the most recent year being 2020. We'll pretend that that is the current. Of time even though we know it's not. But let's just pretend with me that it's currently twenty twenty, haha.
03:40
And if we go back, we see that this data goes back twenty years to the year 2000. We also, more interestingly, see that the heyday of this particular website was really in the, you know, twenty, two thousand and five, two thousand and six through two thousand and ten. So there's a lot more data back then that's older data that's, again, never gonna change, but do we really need those
03:59
recipe reviews as often? Cause our metrics show us that we really hit recipes only in the last three years' worth or so. It's really uncommon for us to hit, recipe reviews, much, much older than that, but we have to keep it around anyway. So what we're gonna do here is we're gonna use
04:15
CTAS, or create external table as select, and my select statement is gonna select data out of the reviews table where year submitted is less than twenty eighteen. And I'm going to create this external table, inside the schema called Parquet demo. I'm just using a separate JSON here more for organization purposes, because this is a demo, and I'm just gonna call it, this particular table name reviews_2017_prior_parquet.
04:38
It's just named this way more for example purposes. In addition to that, I'm also gonna take the remaining reviews and move them into another STaaS table. We're gonna pretend that this is our current data table. So it's gonna be called reviews_2018_present. So let's run all of this together.
04:57
And this will take just about a moment or two even though it's 1.4 million records, and we're done. And we see that of the 1.4 some odd million records, most of it got moved into Parquet, and it only took about four seconds or so. And about 445 thousand records are now remain in
05:14
our current table, here. And finally, what I'm going to do is I'm gonna create a partition view that's gonna combine the Parquet external table and the current base table, which is the 2018 and present data here. Now in a normal environment, I would also swap this out.
05:34
Instead of using a, separate, STaaS schema, this would actually replace the, DFM table. But again, I have to keep this side by side for demonstration purposes 'cause we're gonna do some, demos down here. But let me just do a quick side-by-side count of each of the two different tables tables as it is now because it's a bit of a hybrid cloud being a partition view, but we see that
05:54
we have the same 1.4 million records in each of them. And then I'm also gonna show you the external table definition. This is the name of the external table. It resides on my FlashBlade in this particular location on the FlashBlade. And in fact, I can even go into the S3 browser here and jump over to, Pure's 360 demo,
06:11
jump into reviews, and see that there are four Parquet files that have been created of the underlying, data records here. Pretty coolNow, let's do a little bit of query testing here. I'm gonna turn on SET STATISTICS TIME ON, and we're gonna do just kind of an interesting aggregate query where we're looking, for reviews across multicloud different years.
06:32
We're gonna do some, averages and other calculations here just to try and, find some information here. And let's see how this aggregate query happens to perform. They both executed pretty quickly, and from a duration perspective, the first one which ran against just a regular old single table took 105 milliseconds.
06:49
The second one took a little bit longer, yes, but keep in mind, working with Parquet is not necessarily a performance play. This is 1touch to try and help out the operational situations. And on the occasions that you do need to go after that older data, well, then, yeah, that might take a little bit longer.
07:07
But if we were only going after newer data, data that's in 2018, I don't even have to 1touch those Parquet files. And we'll get to understanding why that's the case a little bit later. And that's where there's gonna be huge benefits from a performance perspective. If I were to just have queried instead of, you know, 2013 through 2019, just show me
07:22
everything from the current date of 2020 on and, you know, and, more recent, for example. I don't even need to 1touch those Parquet files, and they are out of my database. Speaking of being out of my database, how much storage did this actually consume? Of the 1.4 million, in this case, about 800 MB. But now with the, the, 2018 to present table, now I'm only looking at around 45,000 records
07:46
and around 22 MB/Sec. A tremendous savings as far as from a percentage perspective as far as reducing size of the underlying table and reducing the amount of data in my database. Well, Andy Brown, you might say, "this is a really, really small, table. Why don't we look at something bigger?" And we shall.
08:04
So what we need, what we happen to have here is a much, much larger table inside the FT demo database. I'm not gonna run this, select count query right now because it will take a bit of time. But as you can see here, I have order data that spans from 1992 to 1998, and each calendar year happens to have a couple hundred of million records inside each calendar year.
08:25
So a heck of a lot more data in this particular case. I have already pre-created my partition view and created my Parquet files. In fact, let's take a quick look at them here in the S3, browser up here. So I happen to have a whole bunch of different, Parquet files in here. There's 16 files to be exact.
08:41
And if I go up to the underlying folder here, I can see the summary. The total size of this is about 102 GB of Parquet data. And let's compare that to the sizes of the base tables underneath. So we started with 1.5 billion records of orders, which, total about 180 GB. The remaining data that resides inside my SQL Server is th- 43 GB in size.
09:08
43 plus what? 102 or so. 145 GB. So we were able to realize even more savings underneath the covers because of the Parquet format, because the data inside Parquet is compressed. So that's also a really cool benefit too.
09:22
Oh, and by the way, it gets even better. Because when I look at the FlashBlade, remember how it's 102 GB on the FlashBlade? We have deduplication on the FlashBlade. So really, on the FlashBlade, I only burned down just under 42 GB worth of capacity. So that's even better.
09:37
So even though it's compressed data, the Parquet files, we're still able to deduplication it yet even further and realize even more savings on the FlashBlade. That's super impressive. Now what I'm gonna do to do a, a, a simple, sample query for you is that I'm gonna just grab the top 200 records out of the orders table, and now I'm gonna grab customer keys.
09:56
I'm gonna grab three random customer keys. I have no idea how this is gonna turn out. I kinda do actually, but three ransomware keys here. And then I'm going to copy and paste that into a set of side-by-side, queries here. This is just a simple select, select top 1,000 star from the Parquet demo orders and then the
10:15
DBO orders here. And to make this even more challenging and make sure that I have no tricks up my sleeve, I'm going to drop clean-room buffers, issue a checkpoint, and, free the proc cache. This should take about two minutes. So in the meantime, while this runs, I wanna explain to you why are we using Parquet and
10:29
why are we using S3 object storage in the first place. Because Andy, didn't you show us earlier that you used a JSON file? So here's the thing. Parquet first is compressed. We already saw that. But the data inside Parquet files is also physically stored in a columnar format.
10:44
This is particularly useful for columns of data where there's a lot of repeating deduplication. Date, time, like order dates, is a fantastic example of this because with order dates, there's only a finite number of possible dates that, a given value could possibly be. So that is something that compresses really, really well.
11:02
The data physically is also, broken up into chunks, which we like to think of as row groups. Think of it simplistically. Maybe I happen to have 1,000 records such that, I'll have row group one of record one through 100 and then another row group of 101 through 200 and so on and so forth in these different chunks.
11:18
And then it's chunked out in a column format. But the key here is that we also store metadata for the groups of underlying data, so I know the min and the max values for each given row group for each given column. So if we're looking at order data, for example, a given row group might have, April 1st as its min value and April 17th as its max value.
11:40
So I know that row group only contains data for orders between those two different dates. And then for each of those different row groups, we also have physical GB ranges within the given Parquet file.Why is all that important? That's where the other half of this comes into play, the S3 protocol.
11:56
Because the S3 protocol is able to take that metadata information and issue requests, not for an entire file, but for chunks or subsets of a file using the byte ranges and the byte offsets. So therefore, we just read the metadata first, we know what predicates we're looking for the underlying data, and then now we can issue not just the one, but many S3 requests
12:18
simultaneously, enabling us to do parallel seeks into the underlying Parquet files. So you saw earlier in the S3 browser that there were, what, 16 Parquet files of, what, six, seven GB, each, give or take. But now behind the scenes, I'm issuing hundreds, if not thousands, of S3 requests that are very granular seeks going into specific, row groups.
12:39
And if, I did a select star, but if I was only selecting maybe three columns of the underlying data, I could e- do even more, granular seeks because I'd only need column one, column five, and column seven, rather than just doing a simplistic select * giving me all of the columns. Think of this as the equivalent, functional equivalent of doing a whole bunch of index
12:56
seeks within SQL Server rather than range scan operations or avoiding the dreaded full table scan. I see our example query has completed, so let's look at how long it actually took. Well, first of all, this example only returned 30 records each. 30 records out of the hundreds or 1.5 billion, in sum total.
13:14
That's, so that's quite a bit. We're definitely trying to look for a needle in a haystack STaaS kind of scenario here. And how long did this actually take us to run? The first query against DBO orders took almost 60 seconds. Okay, how long did the other one run?
13:31
42 seconds. Just over 42 seconds. That's actually an improvement here. Pretty cool, huh? Just because we were able to, again, go after a much smaller subset of our underlying data. So I hope this little introduction to SQL Server 2022's data virtualization
13:45
you an idea of how we are able to combine the power of STaaS, Parquet files, and S3 object storage on FlashBlade to reduce our database size and enable us to query underlying data in a reasonably per- performant manner using our existing SQL Server. Next, I dare you to give this a shot yourself. We have a test drive available that happens to have this functionality set up for you, and a
14:09
work lab that you can walk through the different steps to try this out for yourself. Hope you have fun, and until next time, we'll see you soon.

Ready to connect?

Contact an Everpure Expert
Watch more from this series
Completion
Unlock premium content.

* indicates a required field.

Gain exclusive access to all premium Pure360 demo content and explore even more in-depth insights and features of the Everpure platform.

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

Your Browser Is No Longer Supported!

Older browsers often represent security risks. In order to deliver the best possible experience when using our site, please update to any of these latest browsers.

safari
chrome
firefox
edge
Close
Personalize for Me
Steps Complete!
1
2
3
Edit My Preferences
Start a Chat
Continue where you left off
Personalize your Everpure experience
Select a challenge, or skip and build your own use case.
Future-proof virtualisation strategies

Storage options for all your needs

Enable AI projects at any scale

High-performance storage for data pipelines, training, and inferencing

Protect against data loss

Cyber resilience solutions that defend your data

Reduce cost of cloud operations

Cost-efficient storage for Azure, AWS, and private clouds

Accelerate applications and database performance

Low-latency storage for application performance

Reduce data centre power and space usage

Resource-efficient storage to improve data centre utilization

Build My Use Case
Confirm your outcome priorities
Your scenario prioritizes the selected outcomes. You can modify or choose next to confirm.
Primary
Reduce My Storage Costs
Lower hardware and operational spend.
Primary
Strengthen Cyber Resilience
Detect, protect against, and recover from ransomware.
Primary
Simplify Governance and Compliance
Easy-to-use policy rules, settings, and templates.
Primary
Deliver Workflow Automation
Eliminate error-prone manual tasks.
Primary
Use Less Power and Space
Smaller footprint, lower power consumption.
Primary
Boost Performance and Scale
Predictability and low latency at any size.
Start Over
Select an outcome priority
Select an outcome priority
Next
What’s your role and industry?
We've inferred your role based on your scenario. Modify or confirm and select your industry.
Select your industry
Financial services
Government
Healthcare
Education
Telecommunications
Automotive
Hyperscaler
Electronic design automation
Retail
Service provider
Transportation
Which team are you on?
Technical leadership team
Defines the strategy and the decision making process
Infrastructure and Ops team
Manages IT infrastructure operations and the technical evaluations
Business leadership team
Responsible for achieving business outcomes
Security team
Owns the policies for security, incident management, and recovery
Application team
Owns the business applications and application SLAs
Back
Select an industry
 Select a team
Select an industry
 Select a team
Next
Describe your ideal environment
Tell us about your infrastructure and workload needs. We chose a few based on your scenario.
Select your preferred deployment
Hosted
Dedicated off-prem
On-prem
Your data centre + edge
Public cloud
Public cloud only
Hybrid
Mix of on-prem and cloud
Select the workloads you need
Databases
Oracle, SQL Server, SAP HANA, open-source

Key benefits:

  • Instant, space-efficient snapshots

  • Near-zero-RPO protection and rapid restore

  • Consistent, low-latency performance

 

AI/ML and analytics
Training, inference, data lakes, HPC

Key benefits:

  • Predictable throughput for faster training and ingest

  • One data layer for pipelines from ingest to serve

  • Optimised GPU utilization and scale
Data protection and recovery
Backups, disaster recovery, and ransomware-safe restore

Key benefits:

  • Immutable snapshots and isolated recovery points

  • Clean, rapid restore with SafeMode™

  • Detection and policy-driven response

 

Containers and Kubernetes
Kubernetes, containers, microservices

Key benefits:

  • Reliable, persistent volumes for stateful apps

  • Fast, space-efficient clones for CI/CD

  • Multi-cloud portability and consistent ops
Cloud
AWS, Azure

Key benefits:

  • Consistent data services across clouds

  • Simple mobility for apps and datasets

  • Flexible, pay-as-you-use economics

 

Virtualisation
VMs, vSphere, VCF, vSAN replacement

Key benefits:

  • Higher VM density with predictable latency

  • Non-disruptive, always-on upgrades

  • Fast ransomware recovery with SafeMode™

 

Data storage
Block, file, and object

Key benefits:

  • Consolidate workloads on one platform

  • Unified services, policy, and governance

  • Eliminate silos and redundant copies

 

What other vendors are you considering or using?
Back
Select a deployment
 Select a workload
Select a deployment
 Select a workload
Finish
Thinking...
Your personalized, guided path
Get started with resources based on your selections.
Start a Chat
My Updates
No updates at this time.