Table partitioning questions

Question

Table partitioning questions

sqlguy80

SSCrazy

Points: 2765
More actions
March 7, 2024 at 4:21 pm

#4391374

We have state of the art latest and greatest managed SQL Azure MI Business critical. Table has 84 M rows, transactions table with data from 2019 to 2024 and beyond, I was asked to implement partitioning to improve performance and management , I read around and will use the GUI and implement partitioning by month from 2019-2027 partitions by month , since its meta data can someone confirm that there will be NO downtime , and table locking etc while this operation will be done in production on the fly?
March 9, 2024 at 10:17 am

This was removed by the editor as SPAM
March 11, 2024 at 4:47 am

This was removed by the editor as SPAM
March 11, 2024 at 11:21 am

This was removed by the editor as SPAM

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1358 More actions · Answer 1

Does your table already have a clustered index on the column you will use for partitioning? If not, it will need to. That presents the likelihood of downtime & locking unless you copy data to a different table to use that until you have partitioned original table, & then change back to use the partitioned table via renaming and/or a view.

Do not expect performance improvements for queries, inserts, updates. You may even see decreases in performance.

You probably should see improvements for data management -- archiving, deleting (truncate partition).

sqlguy80 SSCrazy Points: 2765 More actions · Answer 2

datetime used as partition column is Non clustered ix.

the id column of the table is pkey , clustered index,

frederico_fonseca SSCoach Points: 16154 More actions · Answer 3

as ratbak said you will need to change the clustered index to have the partitioned column as its first (not sure if unique) column.

and in order to allow for the maintenance of archival ALL your indexes will also need to have the column added to them, and need to be partition aligned as well - or you will need to drop/recreate each time you do the archival steps.

sqlguy80 SSCrazy Points: 2765 More actions · Answer 4

question is if I use the GUI and do it, it will do that automatically correct?

drop and creating index as needed? will partitioning a 80 M row table on the fly in prod cause any issues is the question?

frederico_fonseca SSCoach Points: 16154 More actions · Answer 5

why don't you try it on dev, on a copy of the table with all required indexes? asking us here for something you can try yourself is a waste of our and yours time.

but yes it will cause issues - while the change is being done no one will be able to access the table - and if on a busy server, the change will also be held by the other processes and may timeout

This reply was modified 8 months, 2 weeks ago by frederico_fonseca.

sqlguy80 SSCrazy Points: 2765 More actions · Answer 6

Thanks for the response, I will test it with a small data set, but wanted to hear from Gurus who already did it and experienced issues,

Ed B Hall of Fame Points: 3666 More actions · Answer 7

I would look at some tutorials and script it yourself. This link has an example of a partition scheme based on months, but it would be easy to modify for years.

https://www.sqlshack.com/database-table-partitioning-sql-server/

The example uses a range right partition function, which is preferable when your partition key is a datetime. By learning the syntax you can automate the generation of new partitions so someone doesn't have to relearn it in three years and you can avoid having to split the end partition after it contains data.

Once you have a partition scheme created, you can try creating a clustered index on your table with the same name as the old one, using drop existing = on. I don't know whether you can make that an online process, but you can try (in dev).

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

sqlguy80 wrote:

I was asked to implement partitioning to improve performance and management.

We need to really emphasize what RatBak posted (re-quoted below)...

ratbak wrote:

Do not expect performance improvements for queries, inserts, updates. You may even see decreases in performance.
You probably should see improvements for data management -- archiving, deleting (truncate partition).

You also really need to study what is going to happen to your Clustered Index... it's probably NOT going to be "unique" for the original column anymore. I strongly recommend you spend the time reading about it and all the limitations (and it IS quite scattered in BOL... lots and lots of links). You might also want to consider using Partitioned Views instead of Partitioned Tables even though most folks will poo-poo that notion solely based on a possible bad recommendation from MS (IMHO).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pietlinden SSC Guru Points: 63503 More actions · Answer 9

Disclaimer: I'm not a DBA.

But, have you read Gail Shaw's T-SQL Howler about Partitioning? If you haven't read it, you owe it to yourself to do so before doing anything else. Do what she says.