Partition Large Tables needed

Question

Post reply

Partition Large Tables needed

sqlguy80

SSCrazy

Points: 2765
More actions
September 22, 2023 at 12:07 am

#4291389

Hello Gurus,
I have a requirement and need to implement the solution quickly. SQL DB is on Azure MI, so only one file group is allowed.
There are 4 Very large tables of 50 Million Records in each Table, and time stamp is createdon column which has data from 9/2019 to 9/2023 , These are transaction Tables and the requirement is Implement Partitioning
Please give ideas and suggestions will it improve performance? or just management.
what are the steps, can I move so many records to new partitioned Table , and then delete data from old table, seems complicated.
Will new records come in as expected?

September 28, 2023 at 10:16 am

This was removed by the editor as SPAM
October 4, 2023 at 11:44 am

This was removed by the editor as SPAM
October 10, 2023 at 6:04 am

This was removed by the editor as SPAM

Viewing 11 posts - 1 through 10 (of 10 total)

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

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 1

from https://learn.microsoft.com/en-us/answers/questions/1045928/best-practice-to-decide-partition-table-in-sql-ser

It completely depends on how you use the tables.

There are 2 main benefits of partitioning:

Partition elimination on queries

Partition swapin/out for data loading/purging

For partition elimination, the partition must be included in your query condition.

See:

https://learn.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes?view=sql-server-ver16

If you are not using these features, then you will not get any benefit from partitioning, and I recommend against it.

I do not ever recommend partition by less than a year. This becomes a maintenance nightmare when you have 100s of partitions.

sqlguy80 SSCrazy Points: 2765 More actions · Answer 2

Thanks for the message,

What are the disadvantages of partitioning? does it need manual management?

Can we fix those issues which partitioning addresses using other solutions?

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

sqlguy80 wrote:

.... SQL DB is on Azure MI, so only one file group is allowed....?

Have you tested that ?

CREATE DATABASE [MyPtDbOnSQLMI]
 CONTAINMENT = NONE

GO

ALTER DATABASE [MyPtDbOnSQLMI] ADD FILEGROUP [FGPartition_01]
GO
ALTER DATABASE [MyPtDbOnSQLMI] ADD FILE ( NAME = N'PTFile_01', FILEGROWTH = 0) TO FILEGROUP [FGPartition_01]
GO

ALTER DATABASE [MyPtDbOnSQLMI] ADD FILEGROUP [FGPartition_02]
GO
ALTER DATABASE [MyPtDbOnSQLMI] ADD FILE ( NAME = N'PTFile_02', FILEGROWTH = 0) TO FILEGROUP [FGPartition_02]
GO

ALTER DATABASE [MyPtDbOnSQLMI] ADD FILEGROUP [FGPartition_03]
GO
ALTER DATABASE [MyPtDbOnSQLMI] ADD FILE ( NAME = N'PTFile_03', FILEGROWTH = 0) TO FILEGROUP [FGPartition_03]
GO

ALTER DATABASE [MyPtDbOnSQLMI] ADD FILEGROUP [FGPartition_04]
GO
ALTER DATABASE [MyPtDbOnSQLMI] ADD FILE ( NAME = N'PTFile_04', FILEGROWTH = 0) TO FILEGROUP [FGPartition_04]
GO

....

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 4

Can you elaborate why the partitioning is requested. Because of sideloading data without blocking?

SQL Server uses statistics in order to choose what approach is best. Usually an appropriate index achieves what partitioning does.

Resources: https://www.brentozar.com/sql/table-partitioning-resources/

Disadvantages of partitioning:

Requires management of the partition function. Read https://learn.microsoft.com/en-us/sql/t-sql/statements/create-partition-function-transact-sql?view=sql-server-ver16

No benefit unless queries use the partitioning key or you swap load data.

Poor man's solution: try it out with a view based on a few tables with check constraints

Table 2000,2001,2002,2003
View that unions all 2000,2001,2002,2003
Query for 2002 will only scan the table 2002

No need for the poor man's solution if there is an appropriate index for 2002.

If you want to try it in a single filegroup https://database.guide/how-to-map-multiple-partitions-to-a-single-filegroup-in-sql-server-t-sql/

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

sqlguy80 wrote:

Thanks for the message,
What are the disadvantages of partitioning? does it need manual management?
Can we fix those issues which partitioning addresses using other solutions?

I've personally found that partitioned tables are slower than the monolithic tables tables even when both have nearly identical indexes even in the face of supposed "partition elimination".

They also claim in the first bullet point of the following that OLTP loads can be a whole lot faster. What they not telling you is that's done by SWITCH IN from an existing table, which you had to build by inserting data into an external table that, except for partitioning, is identical to the partitioned table. Further, it will occupy an entire partition. If you're doing daily loads on a monthly partitioned table, this will be no help at all.

https://learn.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes#benefits-of-partitioning

There are a ton of caveats to using partitioned tables. Done correctly, you can restore a single partition of a table. You can also restore a few partitions to bring a database back online but you can't do any backups while you have partitions that haven't been loaded yet.

Like I said, there are a ton of caveats and they're spread all over hell's little half acre in the MS documentation. You're going to have to look for them all.

For me, the only reason why I made the mistake of using a partitioned table was to take advantage of having read-only file groups so that I wouldn't have to backup nearly a TB every night and only needed to backup the latest partition. Partitioned Views will also do the same thing without so many headaches and, with the advent of SEQUENCEs in (IIRC) 2008, you don't suffer from having to juggle IDENTITY columns.

Both partitioned Tables and Partitioned views have the advantage of not having to do any form of index maintenance on the read-ony file groups.

And, like I said, query performance is NOT a reason to partition even with supposed "partition elimination" because the performance will actually be a bit worse than on a properly indexed monolithic table.

I strongly recommend that you read the dozen or so closely related/linked articles about partitioning and keep asking yourself what the disadvantages and difficulties will be and then, if you still want to partition, do so with open eyes, especially anywhere that the documentation says CAN instead of WILL.

In other words, don't drink proverbial "Purple Powered Drink" sitting on the table until you're sure it's not going to kill you down the road.

Now, it's not all negative. A lot of people have had very successful results for what they wanted to accomplish (which isn't usually some form of query performance). Just be aware that there's a cost to every feature.

--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)

sqlguy80 SSCrazy Points: 2765 More actions · Answer 6

Two questions:

On Azure MI, we cannot create FGs and add multiple files, I think.

This is Prod there are 4 largetables which they want partitioned , can partitioning be done on existing table , can you list the steps?

say I create partitions for 2020, 2021, 2022, 2023 and house the data based on Created TS into 4 partitions, should the select , update queries etc be tested from the App , and is there a manual task that 2024 partition needs to be created in 2023?

Already oltp old data is being moved to history and being purged from oltp, size of all tables is 500 GB but Azure shows 3.2 TB !

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 7

You can create different filegroups and different files on AzureSQLMI, you just cannot provide it with a filename !

2023-09-26 08_03_11-Greenshot image editor

Read Jeff's advice thoroughly!

Then decide if you have the right reasons to use partitioned objects.

If you choose to place the partitions on a live table, request sufficient downtime!

You can also choose to create a new partitioned table and migrate the data.

TEST IT - Test IT - Test it !!!!