DX Unified Infrastructure Management

Hi everyone.

I am checking my db CAUIM mssql. I am not expert in admin db or in query construction mssql, but I know that with this query i search tables with high level of fragmentation. The rule said that (for key USM tables fragmentation should be less than 30%)

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

What query do you recommended to lower the tables with high fragmentation?

Thank for your help.

M.E

If you have MS SQL Server Enterprise you can enable partitioning and then you don't need to defragment tables other than the following tables in this list (run this as a daily job off hours):

For USM performance these tables in particular need to be defragmented:

ALTER INDEX ALL ON CM_COMPUTER_SYSTEM REBUILD;

ALTER INDEX ALL ON CM_DEVICE REBUILD;

ALTER INDEX ALL ON CM_COMPUTER_SYSTEM_ATTR REBUILD;

ALTER INDEX ALL ON CM_DEVICE_ATTRIBUTE REBUILD;

ALTER INDEX ALL ON CM_CONFIGURATION_ITEM REBUILD;

ALTER INDEX ALL ON CM_CONFIGURATION_ITEM_METRIC REBUILD;

ALTER INDEX ALL ON CM_CONFIGURATION_ITEM_DEFINITION REBUILD;

ALTER INDEX ALL ON CM_CONFIGURATION_ITEM_METRIC_DEFINITION REBUILD;

ALTER INDEX ALL ON CM_NIMBUS_ROBOT REBUILD;

ALTER INDEX ALL ON CM_DEVICE REBUILD;

ALTER INDEX ALL ON CM_COMPUTER_SYSTEM_ORIGIN REBUILD;

ALTER INDEX ALL ON CM_CONFIGURATION_ITEM_ATTRIBUTE REBUILD;

ALTER INDEX ALL ON CM_RELATIONSHIP_CI_CI REBUILD;

ALTER INDEX ALL ON CM_RELATIONSHIP_CI_CS REBUILD;

ALTER INDEX ALL ON CM_RELATIONSHIP_CS_CI REBUILD;

ALTER INDEX ALL ON CM_DISCOVERY_NETWORK REBUILD;

ALTER INDEX ALL ON S_QOS_DATA REBUILD;

ALTER INDEX ALL ON NAS_TRANSACTION_SUMMARY REBUILD;

ALTER INDEX ALL ON NAS_ALARMS REBUILD;

Ideally the db admin will have this scripted to maintain fragmentation automatically.

They can be rebuilt via the MS SQL Studio

Databases > selecting the database > selecting the table > expand the tree > right click on Indexes > select Rebuild All.
There will be a popup window that will list each index and its percentage of fragmentation.
Click OK to run the rebuild and the window will automatically be closed when it is complete.

Index Fragmentation (RN tables)

If your DB is partitioned you do not need to run an index defrag on the RN tables because it may take too long and take up too many DB resources during the job, especially on very large tables (~100M rows). Even if the table index frag is reduced, on large tables, it can re-fragment again in a few days. You can maintain excellent performance even with fragmented RN tables, e.g., 99.8% frag as long as the DB remains partitioned.

Thanks but this not apply for me, because my mssql is standard. Partition only are availability on mssql enterprise.

I have this query that I run once in a while to hit the top tables:

If using the queries, it is safest to run them after hours to help ensure there will not be a performance problem. Also those are only the most common tables for which fragmentation equates to USM performance problems. The alter index query can be modified with the name of another table that is not listed there if need be.

Miller,

We have an official document:

Defragmentation of UIMSLM Database - SQL Server - CA Knowledge 

and the Best Practices:

https://comm.support.ca.com/kb/slm-database-best-practices-indexing-strategy-and-maintenance/kb000034247

yasal01 how are you?. I have other question in this post.

I was working to determine the level of database fragmentation. And this is the level of my tables key.

How you see my db? the theory said that should be less than 30%.

Are my tables very bad?

What is the index? I am sorry so many questions, but I don't know much about db mssql.