Index Index Optimize job is running hours

_{Monday, August 7, 2017 6:25 AM}

Hi All,

One of our IndexOptimize job (rebuild & re-organize) is running 18-20hours and due to this, we have to stop the job manually every week.It's a weekly job.The same job is running fine on other servers.

The SQL version is 2012, enterprise edition, it has 2 <g class="gr_ gr_1000 gr-alert gr_gramm gr_inline_cards gr_run_anim Grammar multiReplace" data-gr-id="1000" id="1000">db's</g>:

DB1-- Devdv:1GBinternal DB which we don't use much.

DB2--ProdDB:400GBUser db

Here is the Code:

EXEC [DevDB].dbo.IndexOptimize @Databases= N'ALL_DATABASES',
@FragmentationMedium = N'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = N'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', 
@FragmentationLevel1 = N'5', @fragmentationLevel2 = N'30', @PageCountLevel = N'1000',
@SortInTempdb = N'N', @LOBCompaction = N'Y', @UpdateStatistics = N'ALL',
@OnlyModifiedStatistics = N'N', @StatisticsReSample = N'N', @PartitionLevel = N'N', @LogToTable = N'Y',
@Execute = N'Y', @MSShippedObjects = N'N'

This job is geeting failed with the below error message always at same table and this table size is only 1MB.Both the db's have few indexes and the fragmented indxes also less.

Command: UPDATE STATISTICS [DevDB].[dbo].[test] [PK_test] [SQLSTATE 01000] (Message 50000)  Outco...  The step failed.

The server has enough memory, temp DB has enough space and there are no IO related messages at that time.Could some suggest what would be the reason to run this job many hours?

Thanks in advance.

_{Monday, August 7, 2017 6:50 AM}

I see you use Ola's script, can you remove @UpdateStatistics = N'ALL', 

During rebuild INDEX SQL Server updates statistics as well ...You can specifie Only modified statistics ='Y'

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

_{Monday, August 7, 2017 7:08 AM}

Thanks for your reply Uri.

We will make that change.Does it really solve our issue?Because the same script is running fine on all other servers.Also, the <g class="gr_ gr_33 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="33" id="33">db</g> has few tables and indexes.Usually, it takes an hour to complete.

Just want to make sure if there would be any other possibilities for this issue.

Thanks.

_{Monday, August 7, 2017 7:10 AM}

In the job definition, go to the Step page, and select the Advanced tab. There you can specify an output file. Use this so that you can see the full message.

The most obvious reason why the job is taking longer time on this server is that the databases on this server are bigger.

I don't know Ola's solution too well, since in my role I don't need to maintain indexes, but I see @LogToTable = 'Y'. So supposedly there is a table which the job is logging to, and this table should tell you which table(s) in which database(s) that is taking a long time.

_{Monday, August 7, 2017 7:48 AM}

Hi Can you also remove INDEX_REBUILD_ONLINE option?  I am almost sure it should improve performance . I have more than 400 databases on the server (the biggest one is about 300 GB)  and Ola's script runs 2:20 hours 

EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 40,
@OnlyModifiedStatistics = 'Y'

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

_{Monday, August 7, 2017 8:42 AM}

Thanks, Erland.i will check it.

_{Monday, August 7, 2017 8:43 AM}

Thanks,Uri.

_{Thursday, August 24, 2017 2:37 AM}

Hi mito access,

Was the issue resolved? If it does, please mark the corresponding replies as answer as it would benefit others when they are reading this thread. If not, could you please provide more information so we can have a better understanding about it?

If you have any other questions, please let me know.

Regards,
Lin

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

_{Tuesday, September 12, 2017 7:21 AM}

We have done index optimize manually and it was completed successfully.

Thanks to All for your assistance.