Best way to reindex large tables/indexes

Question

Post reply

Best way to reindex large tables/indexes

Viewing 11 posts - 16 through 25 (of 25 total)

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

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

Not "ShowConfig"... DBCC SHOWCONTIG

--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

lklein Ten Centuries Points: 1234 More actions · Answer 2

lklein

Ten Centuries

Points: 1234

September 30, 2008 at 7:28 am

#878844

yes I did SHOWCONTIG - sorry - but I still don't see example E... found some other examples ie SELECT TOP 10 ObjectName, IndexName,MaxRecSize - so something like that or Row Count to limit the number of tables?

tosscrosby SSCoach Points: 18045 More actions · Answer 3

tosscrosby

SSCoach

Points: 18045

September 30, 2008 at 7:59 am

#878869

Hmmm....BOL and DBCC ShowCONTIG yields this:

E. Use DBCC SHOWCONTIG and DBCC INDEXDEFRAG to defragment the indexes in a database

Seems to be what Jeff is/was pointing to...

-- You can't be late until you show up.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

If the database (or at least the tables of concern) can be unavailable for a while I would recommend a drop/create cycle since it seems you have never done index maintenance before and have very bad frag.

Oh, and if you don't have a lot of free space in the db, add sufficient size to the db before doing this so the indexes will have contiguous space to lay down in. Otherwise you will wind up with fragmented indexes right off the bat.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

lklein Ten Centuries Points: 1234 More actions · Answer 5

lklein

Ten Centuries

Points: 1234

September 30, 2008 at 9:43 am

#878948

Found 'example E' - thanks for your help.

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

You bet. Thanks for the feedback. Just curious because other folks have had the problem of finding "Example E" and I'd like to be able to better direct them, what were you doing where you couldn't find the example?

--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

lklein Ten Centuries Points: 1234 More actions · Answer 7

Well I wasn't actually smart enough to go look at BOL. I ran the SHOWCONTIG and was looking in that output. See I learned a lesson too! Beginner DBA's need a push in the right direction. And, I've found you really learn this stuff through forums and trial and error. Thank you for you patience - I've come to really appreciate peoples posts - it's a lifeline!

free_mascot One Orange Chip Points: 27168 More actions · Answer 8

Somebody has recomemded lower fillfactor value to grow the data pages faster.

I am fully aggried that this settings should follow for OLTP but would like to know whether it would be better for VLDB too as each datapage contain 30% free space if fillfactor is 70 or 20% in case of fillfactor 80. Hence lead to more space for database and increase in Hardware cost?

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

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

Yep... but with "VLDB's", hardware costs should be the least of your worries.

--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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

TheSQLGuru

SSC Guru

Points: 134017

October 4, 2008 at 8:27 am

#880820

Jeff Moden (10/3/2008)

Yep... but with "VLDB's", hardware costs should be the least of your worries.

Certainly from a storage perspective, although a GOOD SAN can be a tad pricey.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

nmoore SSCrazy Points: 2354 More actions · Answer 11

As well as the degree of fragmentation you should consider the sort of queries run against the tables. If these result in mostly index seeks with no or almost no scans then you may be able to live with the fragmentation. There is the space issue to consider amongst others but this scenario is not unknown.

Nigel Moore
======================