DBCC DBREINDEX - Everynight?

Question

DBCC DBREINDEX - Everynight?

Johnny B

SSCrazy

Points: 2965
More actions
January 15, 2016 at 9:56 am

#323633

We are running DBCC DBREINDEX on every table every night. This is for a long term archive which is updated every 15 minutes then purged nightly. After being updated (INSERT only) every fifteen minutes all day, defrag is run then a purge. The defrag takes 3 hours and the purge starts before defrag is done.
We usually have success but occasionally they both fail.
If I turn off defrag, how do I monitor the affects? Where do I go to evalute any defragmentation?
John
SQL 2012 Standard VPS Windows 2012 Server Standard

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

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

Johnny B (1/15/2016)
We are running DBCC DBREINDEX on every table every night. This is for a long term archive which is updated every 15 minutes then purged nightly. After being updated (INSERT only) every fifteen minutes all day, defrag is run then a purge. The defrag takes 3 hours and the purge starts before defrag is done.
We usually have success but occasionally they both fail.
If I turn off defrag, how do I monitor the affects? Where do I go to evalute any defragmentation?
John

First of all, do the purge before you do a defrag.

Lookup sys.dm_db_index_physical_stats for how to "monitor" fragmentation.

Also, how many rows are contained in this table and what is the criteria to purge rows?

--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)
Intro to Tally Tables and Functions

ZZartin SSC-Dedicated Points: 30758 More actions · Answer 2

Just out of curiosity why are you defragging(or rebuilding) before purging?

Johnny B SSCrazy Points: 2965 More actions · Answer 3

This is how I found the system. I'm not sure why it was set up this way.

SQL 2012 Standard VPS Windows 2012 Server Standard

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Jeff Moden (1/15/2016)
Johnny B (1/15/2016)
We are running DBCC DBREINDEX on every table every night. This is for a long term archive which is updated every 15 minutes then purged nightly. After being updated (INSERT only) every fifteen minutes all day, defrag is run then a purge. The defrag takes 3 hours and the purge starts before defrag is done.
We usually have success but occasionally they both fail.
If I turn off defrag, how do I monitor the affects? Where do I go to evalute any defragmentation?
John
First of all, do the purge before you do a defrag.
Lookup sys.dm_db_index_physical_stats for how to "monitor" fragmentation.
[font="Arial Black"]Also, how many rows are contained in this table and what is the criteria to purge rows?[/font]

Just to ask the question again, please see above.

--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)
Intro to Tally Tables and Functions