How do you reindex a very large database?

Question

How do you reindex a very large database?

Viewing 10 posts - 31 through 39 (of 39 total)

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

What version of SQL Server is this for?

DBADave SSChampion Points: 10962 More actions · Answer 2

DBADave

SSChampion

Points: 10962

January 5, 2009 at 1:56 pm

#921422

2005 Enterprise Edition - 64bit

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

MAXDOP only applies to Rebuilds and not reorganizations. Reorganization is supposed to be a single threaded operation so an CXPACKET Wait that you get will be what you get there, or at least from what I have read regarding the operations.

DBADave SSChampion Points: 10962 More actions · Answer 4

DBADave

SSChampion

Points: 10962

January 5, 2009 at 4:24 pm

#921495

Do you recommend it with DBCC CHECKDB?

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 5

I can ask Paul Randal to answer that. I don't know what a good answer would be to be perfectly honest, but my gut says no.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

Jim Johnston (1/5/2009)
I Guess I hadnt thought about from a speed perspective, but more so from an availability standpoint. Defrags dont prevent other processes from running like a reindex on a cluster. I'm not in a situation where the window is critical where a few extra clicks of CPU is going to slow anything down to the point of causing performance issues while the defrag does its thing. The Defrag seems to have a pretty small footprint, and stopping and restarting it hasnt ever caused any calamities for me, like a full reindex has.
Now, If I am headed for a brick wall with this I would appreciate a heads up, but it just seems to work well for my clients at this point, no outages, no angry calls/emails/pages so far. When I check the fragmentation levels of the indexes, they seem to be staying stable. I generally set a threshold of 20 percent and its worked so far for me. I dont know that any of the major tables have ever gone over 30 percent, so perhaps that has been my saving grace. When I put up a new server - or take on an existing server, Since for some reason not everyone seems to be checking these things, I start by setting the threshold high, then bringing it down over a period of time, so if an index is really whacked out, we catch it a little at a time, starting with the most fragmented and moving down.

Jim,

Based on what I have gotten back, the only thing you are missing with a reorg vs rebuild is that you get a FULLSCAN update of your table statitistics with the rebuild since it is touching the data already. (Thanks Erland Sommarskog)

If you have processes in place to update your statistics then you are probably all good. However one other MVP has commented that a UPDATE STATISTICS WITH FULLSCAN can take longer than a ALTER INDEX REBUILD might, but they didn't provide further information on that. If anything else comes up in the discussion then I'll repost here.

noeld SSC Guru Points: 96590 More actions · Answer 7

Jonathan Kehayias (1/5/2009)
noeld (1/5/2009)
I join to the "FREQUENT REORGANIZE" idea... In my 24x7 Servers Reindexing is banned!
At some point I would expect that you have to do a rebuild to get it back in order. Even Microsoft recommends that you do a rebuild if the fragmentation climbs over 30% as it is better for performance. This is why Enterprise Edition has Online Indexing as an option, so that the impact to 24X7 servers is minimized. If you don't do index rebuilding ever, do you ever run DBCC CHECKDB against the servers, because that is going to be more invasive than spreading index rebuilds out over time.

On VLDB you should NEVER run DBCC CHECKDB because it does simply too much. You can split the checks (ALLOCATION only, CHECKTABLE only, PHYSYCAL only, etc ...) or Like what we do restore backups elsewhere and immediately run DBCC CHECKDB there.

BTW: Because of the relative frequency we set the threshold at 10% and "so-far" we have never reached 30% at reorg time.

Now UPDATE STATISTICS on the other hand is tricky and we do have to occasionally run it with fullscan. We do it on very large tables "one at a time".

* Noel

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 8

noeld (1/6/2009)
On VLDB you should NEVER run DBCC CHECKDB because it does simply too much. You can split the checks (ALLOCATION only, CHECKTABLE only, PHYSYCAL only, etc ...) or Like what we do restore backups elsewhere and immediately run DBCC CHECKDB there.
BTW: Because of the relative frequency we set the threshold at 10% and "so-far" we have never reached 30% at reorg time.
Now UPDATE STATISTICS on the other hand is tricky and we do have to occasionally run it with fullscan. We do it on very large tables "one at a time".

You are basically doing CHECKDB, just in steps, probably following Paul Randals post:

http://blogs.msdn.com/sqlserverstorageengine/archive/2006/10/20/consistency-checking-options-for-a-vldb.aspx

For the UPDATE STATISTICS, you might try doing a REBUILD instead, or using "FULLSCAN, INDEX" (thanks to Erland Sommarskog on this one as well). This will hit your index stats and should be pretty fast, but it won't get your statistics where the leading column isn't the leading key of an index. Those require multiple table scans which is why it is faster to do the REBUILD operation.

noeld SSC Guru Points: 96590 More actions · Answer 9

Jonathan Kehayias (1/6/2009)
noeld (1/6/2009)
On VLDB you should NEVER run DBCC CHECKDB because it does simply too much. You can split the checks (ALLOCATION only, CHECKTABLE only, PHYSYCAL only, etc ...) or Like what we do restore backups elsewhere and immediately run DBCC CHECKDB there.
BTW: Because of the relative frequency we set the threshold at 10% and "so-far" we have never reached 30% at reorg time.
Now UPDATE STATISTICS on the other hand is tricky and we do have to occasionally run it with fullscan. We do it on very large tables "one at a time".
You are basically doing CHECKDB, just in steps, probably following Paul Randals post:
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/10/20/consistency-checking-options-for-a-vldb.aspx
For the UPDATE STATISTICS, you might try doing a REBUILD instead, or using "FULLSCAN, INDEX" (thanks to Erland Sommarskog on this one as well). This will hit your index stats and should be pretty fast, but it won't get your statistics where the leading column isn't the leading key of an index. Those require multiple table scans which is why it is faster to do the REBUILD operation.

Jonathan, you are correct we are following similar direction as Paul's post. The UPDATE STATISTICS with the FULLSCAN is "required" only on tables that are skewed in data and there is not a lot we can do about it. So far doing the largest tables at lowest Peak usage has worked pretty well for us with "ALL". Maybe eventually we will have to hit the "INDEX" option but so far we are Ok. Thanks for that tip by the way.

* Noel

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 10

Noel,

If you feel sporty one time, try the rebuild for one table over the update stats command. From everything I am getting it is going to be faster with the same outcome, and if uptime/minimized impact is the true goal, then you would probably be best served for those rare cases by doing the rebuild. Just something to think about/consider.