troubleshooting Question

help moving to partitioning

Avatar of websss
websssFlag for Kenya asked on
Microsoft SQL Server
3 Comments1 Solution58 ViewsLast Modified:
I have a DB of around 3.5TB in size
It stores time series IOT data

99.9% of data resides in 1 table (approx 7 billion rows)

Currently the table is not partitioned and it sits of one NVME drive

I'm fast running out of space (20GB per day growth)

So I need to do the following (without any downtime - maybe 1 hour max)

1) convert existing data on current hard drive to be partitioning by month (creating as many partitions as there is data)
2) create a new partition on new NVME (E: drive) and start saving April 2020 data on this NEW drive (and all new partitions) ....all old data will live on old drive.
3) each new month should create a new partition and automatically put data into the new partition

Here is a some info about the workload:

-Data older than 30 days is not saved to database, and no UPDATES are ever done, just inserts and deletes.
-Data retention differs - customers choose from 1,3,6, 24 month data retention, I have a script which deletes old data, it can cause blocking on inserts, so partitioning solution needs to ensure this does not happen
-Clustered index is currently on SensorId, DateTime, EventId, PkId (the pkid bit is to make it unique)... when i switched specificity so DateTime was first, it wasn't as performant, but this might be something that i have to do???
-All indexes are heavily fragmented as i'm scared to death about down time (its 24/7 app with no maintenance window)

Can someone please help me attack this as we dont have long left for space and new month approaching.

Any pitfalls or plan of action I should follow.
My main concern is down time, and rebuilding indexes with such little space remaining

sql server 2017 ent
Máté Farkas
SQL Server Consultant
Join our community to see this answer!
Unlock 1 Answer and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros