DX Infrastructure Manager

Expand all | Collapse all

Maintenance_defrag_indixes_UIM they take a long time to finish

Jump to Best Answer
  • 1.  Maintenance_defrag_indixes_UIM they take a long time to finish

    Posted 01-09-2020 10:11 AM
    Hi everyone
    The DBA have these maintenance (*Defragmentation and *Statistics), we are see that the duration time is very high and in occasion cause queuing in the data_engine subscriber.

     - DBA.desfragmentar indices tabla UIM - DBA.desfragmentar indices tabla UIMuse [msdb]GOexec [dbo].[DBA_RebuildIndexesBasedOnFragmentation] @maxfrag = 30.0, @databasename = 'UIM'

    - DBA.ejecucion de EstadisticasEXECUTE dbo.IndexOptimize@Databases = 'UIM',@FragmentationLow = NULL,@FragmentationMedium = NULL,@FragmentationHigh = NULL,@UpdateStatistics = 'ALL'


    Anyone to know how optimize this maintenances? other queries or configuration?


  • 2.  RE: Maintenance_defrag_indixes_UIM they take a long time to finish

    Posted 01-09-2020 10:16 AM
    Hi Miller,

    We recommend implementing partitioning if your backend database supports it, and running a defrag on a specific set of tables, instead of trying to defrag large RN, HN, etc, tables.

    Steve

    ------------------------------
    Support Engineer
    Broadcom
    US
    ------------------------------



  • 3.  RE: Maintenance_defrag_indixes_UIM they take a long time to finish

    Posted 01-09-2020 03:56 PM
    Hi @Stephen Danseglio Thanks for you suggerence. We haven't feature partition in our backen​


  • 4.  RE: Maintenance_defrag_indixes_UIM they take a long time to finish

    Posted 01-09-2020 10:18 AM

    Is your back end SQL MS server?
    If so what version and type IE standard or enterprise?
    If you are using enterprise you can enable table partitioning which breaks the data down into daily tables so the indexing does not take as long.

    The indexing op[timization is nothing we can control from the application level.

    This is a database operation. We can only tell it what thresholds to check and take operations on based on those.

    From the application side the only real way to lower this time it to reduce the amount of data you are collecting and storing so there is not so much to do.

    You DBA can double-check the performance of the SQL server itself to make sure there are no bottlenecks in CPU, memory or disk as well.



    ------------------------------
    Gene Howard
    Principal Support Engineer
    Broadcom
    ------------------------------



  • 5.  RE: Maintenance_defrag_indixes_UIM they take a long time to finish

    Posted 01-09-2020 04:22 PM
    @Gene Howard We have SQL Standard isn't possible to use partition option​​.
    I know that the indexing cannot be controlled from the application but i thinked that maybe with another script queries on the backen could help me with to optimize this maintenances


  • 6.  RE: Maintenance_defrag_indixes_UIM they take a long time to finish

    Posted 01-09-2020 04:43 PM
    no standard does not have partitioning only enterprise.
    you can disable the auto index in the data_engine and then have your DBA handle it on the back end.


    ------------------------------
    Gene Howard
    Principal Support Engineer
    Broadcom
    ------------------------------



  • 7.  RE: Maintenance_defrag_indixes_UIM they take a long time to finish

    Posted 01-10-2020 03:46 PM
    @Gene Howard thanks for your answer.

    The dba has configure this query for defragment indexes ----->

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

    The data_engine probe execute this same query on "Index Maintenance"?


  • 8.  RE: Maintenance_defrag_indixes_UIM they take a long time to finish
    Best Answer

    Posted 01-10-2020 04:11 PM
    We do a reorg when it is above 5% and a rebuild when it is above 30%.
    you will need to monitor performance and see how often you need to run these.
    It will be up to your DBA to make this determination.

    ------------------------------
    Gene Howard
    Principal Support Engineer
    Broadcom
    ------------------------------