Defragmentation of UIMSLM Database - SQL Server

Document ID : KB000073421
Last Modified Date : 15/03/2018
Show Technical Document Details
Introduction:

De-fragmenting Nimsoft SLM Database

Background:

READ THROUGH THIS ENTIRE ARTICLE FIRST BEFORE DOING ANYTHING.

- Slow Performance/INSERTS

- or queues, e.g., data_engine queue backing up.

- or errors like:

"Microsoft OLE DB Provider for SQL Server] Insert bulk failed due to a schema change of the target table."

or

"Failed to insert QoS data into the database, check that the database is running."

Environment:
CA UIM 8.x or later with MS SQL Server
Instructions:
To check the fragmentation of your Nimsoft database you can run the query:

SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats
(DB_ID(N'NimsoftSLM'), NULL, NULL, NULL , 'SAMPLED')
ORDER BY avg_fragmentation_in_percent DESC

assuming that NimsoftSLM is the name of your database - if not adjust the name.

If S_QOS_DATA and the CM_* tables are fragmented over 90%, NMS/data_engine performance is adversely affected.

Note that if you/the customer is using MS SQL Server 2008 R2 Standard Edition as per the query SELECT @@version they cannot take advantage of partitioning.

The script "GenericDefrag_Master.txt" is attached. The script may take a while to run but it won't lock the tables. First, you have to execute the statement in the attached sql script to add the stored procedure, then you have to run it. The script creates a stored procedure that can be run to de-fragment the database.

The command to run the SP once its in place is:

use [msdb]
GO
exec [dbo].[DBA_RebuildIndexesBasedOnFragmentation] @maxfrag = 30.0, @databasename = 'NimsoftSLM'

(assuming your Nimsoft database is named NimsoftSLM.)

Run the script once, then optionally create an MS SQL Server job to run the commands for running the script each night.

If it detects that you're on MS SQL standard edition it will NOT rebuild any indexes, only defrag them.
Rebuilds are what locks the tables. The data_engine maintenance routines only de-fragments the RN_ and HN_ tables, not the others. This script will do them all.

***If youre going to use this defrag approach and run it as a nightly job, then disable data_engine maintenance option in the probe (by deselecting "Automatically reindex tables") and click ok.

The script will run until it's finished.

Free space required = enough for the temp tables which are not that big in the first place. Re-indexing may take some overhead/resources but not enough to be concerned about.

Here is a query to check current space available for the Nimsoft SLM:

use NimsoftSLM
go
sp_spaceused
go

Check the fragmentation of your Nimsoft database again when the script has completed to confirm lower percentage of fragmentation. Check queues, data_engine performance, data_engine throughput, etc.
Additional Information:
Please check the File Attached named: GenericDefrag_Master.txt
File Attachments:
GenericDefrag_master.txt