Often in the process of database operation, some of its objects become very large, which negatively affects performance and maintenance time. Sectionalization can be one of the options to solve this problem.
In this article, we will tell you about the pros and cons of DBMS sectionalization and show you how DB-Service uses it in a concrete example.
Pros and cons of MS SQL Server sectionalization
Implementing sectionalization can provide the following benefits:
● Simplification of maintenance in DBMS versions starting from SQL Server 2016. This is the ability to perform index and statistics maintenance at the section level instead of the whole index.
● Speeding up queries that use sectioned field filtering. It consists of the possibility of searching not the whole index, but its “fragments”.
● Gain enhanced data management capabilities. These include moving archive sections to special file groups on slow disks or easier cleanup of old sections in DBMS versions starting from SQL Server 2016.
However, the implementation of sectionalization also comes with certain complexities and risks:
● Implementing sectionalization becomes difficult when dealing with large objects: clustered indexes are particularly difficult to sectionalize.
● Sectionalization requires changing the structure of some objects. For example, adding new fields to indexes may cause a conflict with the application.
● If the SQL Server version is older than 2016, it is not possible to maintain sectioned indexes online (it can only be done manually, which will take a very long time).
If you compare these pros and cons, it becomes clear that the implementation of sectioning MS SQL Server – is a useful thing, but it is better to entrust it to professionals. If you turn to DB-Service engineers, you will not only get SQL Server DBMS maintenance but also assistance in changing the infrastructure to the requirements of your business.
Preparing for the implementation of sectionalization
Before implementing sectionalization, it is recommended to evaluate:
1. The size of the table and indexes (the larger the objects, the more complex the work);
2. Number of indexes (whether all are planned to be sectioned, in what order, whether there is a cluster index);
3. Index fields and their usage activity (field composition will determine the most successful field for sectioning, and usage activity will show prospective benefit).
How DB-Service works with sectionalization
Let us now consider how DB-Service evaluates the possibility and necessity of sectionalization using the example of a table in the Axapta (Microsoft Dynamics AX) database of one of Russia’s largest food retailers.
1. Estimating size. The table and indexes have a small size (everything weighs less than 50 GB).
2. Select fields for sectionalization. The table has two main indexes that are searched; the others are rarely used.
3. We are looking for a suitable column for sectioning. In this case, it is difficult because the structures of the two main indexes selected above are very different.
4. Analyze the data and conclude:
RECID (unique record identifier) appears only in the cluster index. If it is added to the other indexes, it may increase their size and not give a speed increase (requires a detailed study).
Nevertheless, RECID is monotonically increasing, which makes the construction of a sectioning function based on it quite simple. OPERDATE (time) is in several indexes. Adding it to the others should not significantly increase the size, but may increase performance (requires a detailed study).The column itself is a date that ranges from 2015-08-05 00:00:00:00.000 – 2023-05-25 00:00:00:00.000, and the column currently contains 347188668 unique values.
You can try sectioning the table by OPERATE with half-year or 3-month breakdowns.
5. We send the results of the analysis to the client for decision making. It is the client who will conduct detailed studies to make the final decision on the implementation of sectionalization.
Conclusions
We have described the advantages of MS SQL Server sectionalization, as well as the difficulties it can cause. On the example of a table in the Axapta database, an example of how DB-Service performs an analysis based on the results of which it gives recommendations on sectioning to its customers was analyzed.