Manually partitioning the UIM database for Microsoft SQL Server

Document ID : KB000034658
Last Modified Date : 14/02/2018
Show Technical Document Details

Introduction

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.

The following document describes a manual process for partitioning tables on a UIM Database hosted on Microsoft SQL Server 2008 R2 Enterprise or above.

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.

Generally speaking, we always recommend that partitioning should be enabled where possible; even on a smaller database, it will improve the performance of data maintenance.  On a larger database (over 10GB in size) this can be especially important, as data maintenance on an unpartitioned database can take many hours to complete.

While it is difficult to pinpoint an exact number of rows which constitutes a "large" table that is a candidate for manual partitioning, a rule of thumb is to manually partition any table with >50 million rows.  The following query can be used to determine the number of rows in your RN_QOS_DATA tables, sorted largest to smallest:

Select a.Name as TableName,Sum(b.Rows) as Rows from Sys.Tables as a,Sys.Partitions as b where a.Object_Id=b.Object_Id and a.name like '%RN_QOS%' Group By a.Name Order By Sum(Rows) Desc

Procedure

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. 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) 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. Enable data maintenance in data_engine again once you are finished.  Data_engine's next maintenance run will automatically partition any remaining unpartitioned tables.