November 22, 2009 at 4:57 pm
Hi ,
Does anyone have the script to rebuild/Reorg the indexes based on the fragmentation for a database in sql server 2005.
Thanks for your help
November 22, 2009 at 5:13 pm
srilu_bannu (11/22/2009)
Does anyone have the script to rebuild/Reorg the indexes based on the fragmentation for a database in sql server 2005.
I would use Maintenance plan to do it, but it does not have based on fragmentation.
I would suggest you look at the article and customize it accordingly.
http://www.mssqltips.com/tip.asp?tip=1367
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 23, 2009 at 6:53 am
but the query doesnot depend on fragmentation...
November 23, 2009 at 7:19 am
srilu_bannu (11/23/2009)
but the query doesnot depend on fragmentation...
There you go ...
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/4671b8db-cedd-4059-8761-f50483f09bff/[/url]
Search for a considerable amount of time for such scripts when you need.. you should get them some where like these...
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 23, 2009 at 8:35 am
There are several out there. 2 I recommend are (in no particular order):
1. Ola Hallengren's script which is part of his entire maintenance solution at www.ola.hallengren.com[/url]
2. Michelle Ufford's Index Defrag Script found on her blog here[/url].
They are very similar and each has some things I like better than the other one. At some point I may merge the 2.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 23, 2009 at 8:40 am
I am sorry to bother you but my server is using the compatibility Sql server 2000(80) which will only run DBCC showcounting...
And also, if i want to rebuild the index with or without using fragmentation and schedule it on weekly basis .....can i do it by creating tempdb if so how?please give me the best suggetion ............
November 23, 2009 at 9:10 am
srilu_bannu (11/23/2009)
I am sorry to bother you but my server is using the compatibility Sql server 2000(80) which will only run DBCC showcounting...And also, if i want to rebuild the index with or without using fragmentation and schedule it on weekly basis .....can i do it by creating tempdb if so how?please give me the best suggetion ............
Search Books-on-Line for DBCC SHOWCONTIG there is a script at the bottom of the page that shoud work for you.
December 4, 2009 at 2:04 pm
The script in Books on line defrags the indexes that ran good .i also need some help to rebuild the indexes based on fragmentation for sql server 2000.
I found the query to rebuild the indexes for sql server 2005 ,i have tried it on my server even though my DB Compatbility is sql server 2000(80).Please see the attachements below:
As you can see it is not rebuilding all the indexes with Avg frag in percent>30%..
correct me if i am wrong...
December 4, 2009 at 2:16 pm
I believe column L in the spreadsheet is the # of pages in the index and the indexes that did not defragment have 26 or fewer pages. This likely indecates that they are in mixed extents so that they will not be defragmented. It also means that defragmentation will likely not improve performance because the entire index will likely be cached anyway. A general rule of thumb is that anything under about 100 pages probably doesn't need to be fragmented/won't be.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 4, 2009 at 2:40 pm
So ,does this means i can proceed with this script eventhough my DB compatbility is Sql Server 2000(80) and Query is based on SQL Server 2005?
December 4, 2009 at 2:45 pm
Well, I just successfully ran it on a DB in 200 compatibility mode. Try it in a dev environment first.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 14, 2009 at 10:01 am
Hi ,
I did try it on dev and test and it ran good .i am attaching the results..
Based on results:
1.In excel spread sheet one that is marked in pink is a heap.but when i looked into the object_name it actually has 2 non_clustered indexes.
why is it showing as heap?
2.Executed: ALTER INDEX [PK_tb_ProductFab] ON [dbo].[tb_ProductFab] REBUILD
This is a clustered index .why is it altering clustered index ?
Also this table has a non_clustered index why is it ignoring that and only altering this primary key clustered index?
Remaining results(index rebuilding ) are good matches with the fragmentation results ....
Can you also tell me how much page_count should exceed to rebuild the index ?
Thanks for the help
December 14, 2009 at 11:59 am
Here are some answers to your questions:
1. Any table without a clustered index is a heap, irregardless of the number on non-clustered indexes on it.
2. ALTER INDEX with a specific index name only REBUILDS the index that is specified. Clustered indexes do need to be defragmented so it is doing exactly what it has been told to do.
3. Books on Line says about 1000, but you need to read this to see where it came from, http://www.sqlskills.com/BLOGS/PAUL/post.aspx?id=676d2c0a-04ae-4068-bad9-8d813286f219
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
December 14, 2009 at 3:10 pm
Thank you
Ok i got it since the fragmentation is above 30% for clustered index it is rebuilding it .As i mentioned earlier this table has non_clustered index too and for each and every update in clustered index non_clustered index has to be rebuilt.
since clustered index is rebuilding here do i need to rebuild the non_clustered index too.and the fragmentation for the non_clustered index in this table is 0.
Please correct me if i am wrong
December 14, 2009 at 3:20 pm
Nope you don't need to rebuild or reorg a non-clustered index if you rebuild a clustered index.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply