Rebuild/Reorganize index script

Question

Post reply

Rebuild/Reorganize index script

RPSQL

Hall of Fame

Points: 3229
More actions
May 16, 2009 at 11:38 am

#131189

Hi All,
First of all short description about our env.
Database size-1 TB
No. of Tables-about 700
Now, we have problem with reindex jobs....it takes a lot of time as well as consume a lot of transaction log space to run. We don't have enough space in our transaction log drive to handle it and client is not ready to give us that space. It failed many times in past and filling up the whole drive space.
Now , This is what we are planning to accomplish through scripts..
1.Instead of running rebuild index on whole database we are planning to run it against each table.
2.For that plan we will break those tables into seven groups and everyday run script against selected about 100 tables.
3.So if suppose on monday, we run script against 100 tables then it will first of all check fragmentation of indexes on those 100 tables first.
-if percent fragmentation value is less then 5% then we will not run rebuild/reorg index against that index.
-if percent fragmentation value is between 5% to 30% then we will run reorganize index against that index.
-if percent fragmentation value is more than 30% then we will run rebuild index with online option against that index.
We need a script to accomplish this task.
any help will be highly appreciated.
Thanks

Viewing 13 posts - 1 through 12 (of 12 total)

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

george sibbald SSC Guru Points: 104210 More actions · Answer 1

most people use the script found in BOl, here:

http://technet.microsoft.com/en-us/library/ms188917(SQL.90).aspx

you will just have to add a further filter on object_id or tablename depending on what works best in your environment. I would also filter out tables with less than about 1000 pages as there is no benefit in defrag for tables that small.

---------------------------------------------------------------------

Ted Krueger Ten Centuries Points: 1171 More actions · Answer 2

SQLFool also has a very well written one here http://sqlfool.com/2009/03/automated-index-defrag-script/

free_mascot One Orange Chip Points: 27169 More actions · Answer 3

Both the link provided is v.good.

But I guess you need to slightly modify the code in both of the above link to meet your requirement.

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

maechismo_8514 SSCoach Points: 17510 More actions · Answer 4

RPSql (5/16/2009)
Hi All,
Now, we have problem with reindex jobs....it takes a lot of time as well as consume a lot of transaction log space to run.

Probably you are rebuilding all the indexes against the tables re building only the fragmented ones.

We don't have enough space in our transaction log drive to handle it and client is not ready to give us that space. It failed many times in past and filling up the whole drive space.

Are you changing the recovery model to bulk-logged while rebuilding the indexes?

1.Instead of running rebuild index on whole database we are planning to run it against each table.

no necessarily on all first Identify out the indexes that are fragmented and rebuild/reorg your indexes.

-if percent fragmentation value is less then 5% then we will not run rebuild/reorg index against that index.
-if percent fragmentation value is between 5% to 30% then we will run reorganize index against that index.
-if percent fragmentation value is more than 30% then we will run rebuild index with online option against that index.

1.) Identify the indexes that are fragemented

2.) Reorganize the indexes that are less fragmented (30% as a rule of thumb)

4.) Update statistics with full scan on indexes from step 2

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

I personally use the code on Ola Hallengren's blog for newer servers or when I am consulting. It is very well written and extremely flexible.

http://blog.ola.hallengren.com/

GabyYYZ SSCertifiable Points: 7913 More actions · Answer 6

RPSql (5/16/2009)
-if percent fragmentation value is less then 5% then we will not run rebuild/reorg index against that index.
-if percent fragmentation value is between 5% to 30% then we will run reorganize index against that index.
-if percent fragmentation value is more than 30% then we will run rebuild index with online option against that index.
...

You may be giving yourself too much work here. As well as George's recommendation of a minimum 1000 pages, use the following, modified from the previously mentioned BOL link:

30%: Reindex

Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein

maechismo_8514 SSCoach Points: 17510 More actions · Answer 7

Gaby Abed (5/19/2009)
RPSql (5/16/2009)
-if percent fragmentation value is less then 5% then we will not run rebuild/reorg index against that index.
-if percent fragmentation value is between 5% to 30% then we will run reorganize index against that index.
-if percent fragmentation value is more than 30% then we will run rebuild index with online option against that index.
...
You may be giving yourself too much work here. As well as George's recommendation of a minimum 1000 pages, use the following, modified from the previously mentioned BOL link:

Rule of thumb is 2000 pages.....

30%: Reindex

This depends on your business and who much you can accept?

george sibbald SSC Guru Points: 104210 More actions · Answer 8

Krishna Potlakayala (5/19/2009)

Gaby Abed (5/19/2009)
RPSql (5/16/2009)
-if percent fragmentation value is less then 5% then we will not run rebuild/reorg index against that index.
-if percent fragmentation value is between 5% to 30% then we will run reorganize index against that index.
-if percent fragmentation value is more than 30% then we will run rebuild index with online option against that index.
...
You may be giving yourself too much work here. As well as George's recommendation of a minimum 1000 pages, use the following, modified from the previously mentioned BOL link:

Rule of thumb is 2000 pages.....

your thumb must be bigger than mine, 1000 pages (8mb) will do me. 🙂

---------------------------------------------------------------------

maechismo_8514 SSCoach Points: 17510 More actions · Answer 9

george sibbald (5/19/2009)

Krishna Potlakayala (5/19/2009)
Gaby Abed (5/19/2009)
RPSql (5/16/2009)
-if percent fragmentation value is less then 5% then we will not run rebuild/reorg index against that index.
-if percent fragmentation value is between 5% to 30% then we will run reorganize index against that index.
-if percent fragmentation value is more than 30% then we will run rebuild index with online option against that index.
...
You may be giving yourself too much work here. As well as George's recommendation of a minimum 1000 pages, use the following, modified from the previously mentioned BOL link:
Rule of thumb is 2000 pages.....
your thumb must be bigger than mine, 1000 pages (8mb) will do me. 🙂

😀 rule of thumb eh?? so no bother probably the DBA would have to investigate it against his business

GabyYYZ SSCertifiable Points: 7913 More actions · Answer 10

Krishna Potlakayala (5/19/2009)
Gaby Abed (5/19/2009)
RPSql (5/16/2009)
-if percent fragmentation value is less then 5% then we will not run rebuild/reorg index against that index.
-if percent fragmentation value is between 5% to 30% then we will run reorganize index against that index.
-if percent fragmentation value is more than 30% then we will run rebuild index with online option against that index.
...
You may be giving yourself too much work here. As well as George's recommendation of a minimum 1000 pages, use the following, modified from the previously mentioned BOL link:
Rule of thumb is 2000 pages.....
30%: Reindex
This depends on your business and who much you can accept?

Too true. If 10% is equivalent to 1,000,000 rows, maybe reorganizing/reindexing on lower threshholds may be better.

Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein

KKK17 SSChasing Mays Points: 603 More actions · Answer 11

Hi All,

The script provided in both the url's is very good for 2005 environment. Is there a script for SQL Sever 2000 also. If so, can I have the link or the script please.

Thanks in advance,

KKK

george sibbald SSC Guru Points: 104210 More actions · Answer 12

Theres one in the SQL 2000 BOL under dbcc showcontig

http://msdn.microsoft.com/en-us/library/ms175008.aspx

It could be amended slightly to do either indexdefrag or dbreindex

---------------------------------------------------------------------