3
I was suggested to partition the main table of my system to improve the performance of some queries.
The dev who made the suggestion suggested using the information from ano registry registration to partition, because as the system is not working there are more than 10 years old there would be few partitions (which according to him would be the ideal).
But from what I understood of the functioning of the partition, it would be more profitable for me to partition using the identifier of the company/agency to which the record belongs (are approximately 300 different). I thought about doing so because in most queries the key is used. But I don’t know if I can or should do this.
What are the precautions, and what should I worry about when partitioning a table in the SQL Server?
On the table, it is a very voluminous table with more than one million records that has relationships at different levels with much of the other tables of the system.
UPDATE:
After reading the links posted in the comments, I saw that it would be impracticable in this situation to partition using the company/organ identifier (due to the amount of partitions generated) and that would not bring partitioning results per year since I do not use this parameter in the queries.
Even if it does not apply to that particular situation, I would be happy to get an enlightening answer on what I should be worried about when creating a Partified table?
Have a great read here, you can help with your questions: https://docs.microsoft.com/pt-br/sql/relational-databases/partitions/partitioned-tables-and-indexes
– Ricardo Pontual
On table partitioning, here is the compilation on the subject: https://www.brentozar.com/sql/table-partitioning-resources/
– José Diz
1 million lines is change. I suggest you check other ways to optimize before deciding on partitioning. // Don’t worry about most queries, but the ones that consume the most resources. // Partitioning by year will only be advantageous if queries also filter by date.
– José Diz
@Josédiz on partitioning by year tbm have the same opinion. The queries that get slower are not exactly those made directly in this table. But others that involve her...
– Guilherme
After the recommended readings I have almost given up partitioning this table. But I still seek a definitive answer about my doubt
– Guilherme
Hi, give us a read on this Microsoft article and see if it helps you https://docs.microsoft.com/pt-br/sql/relational-databases/partitions/create-partitioned-tables-and-indexes?view=sql-server-ver15
– Jéssica Nathany