At the end of each month I reindex a critical financial database on a relatively new SQL 2014 Server that was installed July 2017.
I manually reindex using the following query: EXEC sp_MSforeeachtable @command1="print '?' DBCC DBREINDEX('?')
The following shows the dates and the processing time to reindex the DB:
31JUL2017 = 3:46 Min
31AUG2017 = 4:50 Min
29SEP2017 = 4:51 Min
31OCT2017 = 4:58 Min
30NOV2017 = 5:00 Min
29DEC2017 = 5:01 Min
31JAN2018 = 5:02 Min
28FEB2018 = 5:08 Min
30MAR2018 = 6:21 Min çNot sure why it suddenly jumped close to a minute between months. We have not applied SQL or Windows server changes other than applying Windows Server 2016 updates.
21APR2018 = 7:48 Min çRan it again this Saturday to see if the overall reindex processing time would go back to around 5min+, and it increased further.
How many pages is the largest index? Also, have you determined the correct FILL FACTOR for every index in this database?