Maintenance_defrag_indixes_UIM they take a long time to finish

[Miller Echagarreta]

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?

[Stephen Danseglio]

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
------------------------------

[Miller Echagarreta]

Hi @Stephen Danseglio Thanks for you suggerence. We haven't feature partition in our backen​

[Gene Howard]

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
------------------------------

[Miller Echagarreta]

@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

[Gene Howard]

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
------------------------------

[Miller Echagarreta]

@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"?

[Gene Howard]

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
------------------------------