How to determine if data_engine partitioning is working as expected in DX UIM

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)
• CA Unified Infrastructure Management On-Premise (Nimsoft / UIM)
• CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/Introduction

DX UIM (Nimsoft) customers sometimes need to check if data_engine partitioning is working or not, i.e., if partitions are being dropped as expected.

Environment

• DX UIM 9.x or higher
• MS SQL Server Enterprise Editions

Cause

• partitions not being dropped
• data exists in 1 or more tables that is older than the data_engine raw/historical retention setting

Resolution

Run the query shown below to examine partition details in MS SQL Server (Query to check partitions and if the drop of the partitions is adhering to the data_engine raw and historical retention settings)

 

Headers include: Table Name, Partition Number, Rows, Partition Range, and Days from Today

--------------------------------------------------------------------------------------------------------------------------------

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

GO

DECLARE @lTableName varchar(max) = 'RN_QOS_DATA_0001';

 

WITH PartitionInfo AS (

    SELECT o.name AS TableName,

           p.partition_number AS PartitionNumber,

           p.Rows,

           CONVERT(datetime, prv.value) AS PartitionValue

    FROM sys.objects o

    JOIN sys.indexes i ON o.object_id = i.object_id AND i.index_id = 1

    JOIN sys.partitions p ON p.object_id = i.object_id AND p.index_id=i.index_id

    JOIN sys.partition_schemes ps ON i.data_space_id=ps.data_space_id

    LEFT JOIN sys.partition_range_values prv ON ps.function_id=prv.function_id AND prv.boundary_id+1=p.partition_number

    WHERE o.name like @lTableName

)

SELECT TableName,

       PartitionNumber,

       Rows,

       'Partition Range' = CASE

           WHEN PartitionValue IS NULL THEN

               '< ' + CONVERT(varchar(10), (SELECT MIN(PartitionValue) FROM PartitionInfo), 120)

           ELSE

               '>= ' + CONVERT(varchar(10), PartitionValue, 120) + ' and < ' + CONVERT(varchar(10), DATEADD(DD, 1, PartitionValue), 120)

       END,

       'Days From Today' = ISNULL(

           CASE CONVERT(nvarchar(5), DATEDIFF(day, SYSDATETIME(), PartitionValue))

               WHEN '0' THEN '0 *** TODAY ***'

               ELSE CONVERT(nvarchar(5), DATEDIFF(day, SYSDATETIME(), PartitionValue))

           END,

           NCHAR(8734)

       )

FROM PartitionInfo

GROUP BY TableName, PartitionNumber, Rows, PartitionValue

ORDER BY PartitionNumber ASC;

-------------------------------------------------------------------------------------

Example output:

 

 

As shown above, if partitions are being dropped successfully, there should be no values in the 'Days From Today' column that are greater than the raw data_engine retention setting, e.g., 35 days.

 

Using the query above you can edit the table name to check 1 or more tables. This applies to RN_QOS_DATA_nnnn as well as HN_QOS_DATA_nnnn tables.

Additional Information

How to manually partition the UIM Database for Microsoft SQL Server

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.

************Warning Use at your own Risk************

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 is normally 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.