DX Infrastructure Management

Expand all | Collapse all

Determine the level of database fragmentation (for key USM tables fragmentation should be less than 30%)

  • 1.  Determine the level of database fragmentation (for key USM tables fragmentation should be less than 30%)

    Posted 08-15-2018 01:14 PM
      |   view attached

    Hi all.

     

    I am not expert in db mssql but I trying checking the level of database fragmentation of CAUIM.

    My db is Microsoft SQL Server 2014 (SP2-GDR) (KB4057120) - Standard Edition (64-bit).

     

    When I execute this command

    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

     

    Return this tablas with fragmentation very high, according the documentation (UMP (USM) Slow Performance Guide and Troubleshooting Checklist 1.1) the tables fragmentation should be less than 30%.

     

     

    I attached the file of all tables with fragmentation if your need see.

     

    What can I do with this high fragmentation?

    How does I reduce this fragmentation? because it feel certain slow when consulting data in the USM and maybe this is the problem.

     

    M.E

    Attachment(s)



  • 2.  Re: Determine the level of database fragmentation (for key USM tables fragmentation should be less than 30%)

    Posted 08-16-2018 10:29 AM

    So it would look like either the data_engine does not have the re-index enabled or it is failing for some reason.

    the best thing to do is to engage your DBA.

    have them setup a nightly job to re-index all of the tables in the database.

    you both can review the UIM MS Sql best practice guide here:

    CA UIM (Nimsoft) Database Best Practices for MS SQ - CA Knowledge 



  • 3.  Re: Determine the level of database fragmentation (for key USM tables fragmentation should be less than 30%)

    Posted 08-16-2018 11:07 AM

    Gene, the re-index not enable because I think understand that is only support MSSQL Enterprise and my CANIS is MSSQL2014 Standart.



  • 4.  Re: Determine the level of database fragmentation (for key USM tables fragmentation should be less than 30%)

    Posted 08-16-2018 11:14 AM

    That is fine. Then you will need to work with your DBA to setup a nightly schedule to re-index all tables in the UIM database using native database tools.



  • 5.  Re: Determine the level of database fragmentation (for key USM tables fragmentation should be less than 30%)

    Posted 08-16-2018 04:55 PM

    Also the data engine reindexing only does the RN tables….

    Best to get the DBA to setup a standard maintenance job



  • 6.  Re: Determine the level of database fragmentation (for key USM tables fragmentation should be less than 30%)

    Posted 08-16-2018 05:07 PM

    As a side note, High fragmentation and low page count is fine, high fragmentation and high page count is going to impact performance.

    for reference:

    How does data_engine maintenance and retention wor - CA Knowledge 



  • 7.  Re: Determine the level of database fragmentation (for key USM tables fragmentation should be less than 30%)

    Posted 08-17-2018 12:16 AM

    Yes ,It will cause slowness in UMP .Please do index rebuild in CA_UIM database and enable auto indexing in data_engine probe and ensure that it is working .