SQL 2014 Standard - Reindex DB Processing Time

Question

Post reply

SQL 2014 Standard - Reindex DB Processing Time

cmp119

SSChasing Mays

Points: 626
More actions
April 23, 2018 at 10:56 am

#334402

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.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 1

Well are you adding more data to the DB every month? If so reindexing will take longer 😛

cmp119 SSChasing Mays Points: 626 More actions · Answer 2

Agree, but look at the significant difference in processing time between Feb and March 2018, and then again between March 2018 to 21APR2018. I am just wondering why such a significant difference suddenly.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

cmp119 - Monday, April 23, 2018 10:56 AM
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?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)