May 16, 2009 at 11:38 am
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
May 16, 2009 at 12:33 pm
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.
---------------------------------------------------------------------
May 17, 2009 at 4:23 pm
SQLFool also has a very well written one here http://sqlfool.com/2009/03/automated-index-defrag-script/
May 18, 2009 at 12:58 am
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."
May 18, 2009 at 6:08 am
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
May 18, 2009 at 7:07 am
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/
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
May 19, 2009 at 9:39 am
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
May 19, 2009 at 3:38 pm
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?
May 19, 2009 at 4:40 pm
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. 🙂
---------------------------------------------------------------------
May 19, 2009 at 4:44 pm
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
May 20, 2009 at 7:22 am
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
June 20, 2009 at 5:24 am
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
June 20, 2009 at 8:59 am
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
---------------------------------------------------------------------
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply