Note that all of the proposed guidelines/recommendations included in this Article and document MUST be discussed with a DBA first before taking any action/making any changes to the database.
Please review this KB Article and all queries with your DBA before following the steps.
We also highly recommend that these queries and their results be tested in a Test/DEV environment before running in Production.
When enabling partitioning in an environment with a very large database, it may be advisable to manually partition the largest tables before allowing the data_engine to partition the smaller ones; the data_engine may otherwise 'time out' during maintenance periods if it is allowed to attempt partitioning the tables on its own.
Below is the full process and query you will use to manually partition the tables associated with a QoS.
Note that once you begin this process, you have 14 days to complete it. As soon as you begin the next step here there's no turning back.
Warning: If the process is not completed within 14 days, then you risk losing 1 days’ worth of data each day until the process is completed.
1. Open the data_engine GUI in IM and select the box to enable partitioning.
2. When the popup dialog appears asking if you want to partition the DB now, or at maintenance time, choose "at next maintenance."
3. This will commit a change to the database which is needed for the next steps.
4. Now disable all data_engine maintenance entirely in the data_engine GUI by un-checking all related boxes for maintenance and index maintenance, and then restart the probe.
5. From this point on, it’s permissible to leave data_engine active – there is no need to shut it down.
6. Determine which table to partition first -- in the below example we'll use RN_QOS_DATA_0035 but this will depend on table size. (It’s advisable to start with the largest tables as mentioned above.)
7. The number of this table (e.g. 0035) will be the number you will plug into the following query to partition the desired table (strip the leading zeroes). This will also match the qos_def_id associated with this table in S_QOS_DEFINITION.
declare @RCR int, @HCR int, @RSR int, @HSR int, @RC int
exec @RC = spn_de_DataMaint 35, 'all' , null, 5, @RCR output, @HCR output, @RSR output, @HSR output
select @RCR as RCR, @HCR as HCR, @RSR as RSR, @HSR as HSR, @RC as RC;
Note the "35" in this query - this should be changed to whatever the qos_def_id (RN/HN/DN table number) is that you wish to partition. The rest of the values should be left as-is.
8. Run this same query for each table that you wish to partition (it will take awhile!). Make sure to change the table number (e.g. “35”) each time while leaving all other parameters the same.
9. Finally, then enable data maintenance in the data_engine again once you are finished. The data_engine's next maintenance run will automatically partition any remaining un-partitioned tables.