CA UIM 8.x or later with MS SQL Server
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,
(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:
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:
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.