Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sarahmfr
Posting Yak Master
214 Posts |
 Posted - 2012-02-04 : 01:03:32
|
| How to rebuild all the database indexes at once.Is it advisable to do that Does rebuilding indexes help in increasing memory spacesarah |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
sarahmfr
Posting Yak Master
214 Posts |
Posted - 2012-02-04 : 10:05:30
|
| ThanksIs the sequence of selecting the indexes important.i.e if the primary key userid is used with many foreign keys shall I start by this one.I.e. I have a lot of tables having foreign key referencing userid in users tableShall I start by rebuilding the userid index to keep it to the end.sarah |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-02-04 : 13:27:44
|
Only rebuild the indexes that are fragmented. I don't think the order that you do them matters (although if you are going to rebuild for ONLY a FIXED TIME (e.g. only for you maintenance-window duration) then do the most fragmented ones first  ) |
|
|
|
sarahmfr
Posting Yak Master
214 Posts |
Posted - 2012-02-05 : 17:36:34
|
| Thanks a lotanother questionPlease I have a primary key of a table that was set to be a non cluster index by mistake If I changed it to cluster now will this ruin anythingI tried to rebuild it but it is still 71% fragmented.Also I have sql server 2008 developer edition so I can not do that onlinesarah |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-02-05 : 18:22:24
|
| "I tried to rebuild it but it is still 71% fragmented."If the table is small (dunno precisely, but say less than 20 pages, maybe less than 50 or 100 pages) then the fragmentation percentage doesn't mean very much.Or maybe your index has a fill-factor allowing quite a bit of reserved space after index rebuild? |
|
|
|
sarahmfr
Posting Yak Master
214 Posts |
Posted - 2012-02-05 : 18:36:03
|
| how can I reduce the fragmentation by adjusting the fill factorsarah |
|
|
|
sarahmfr
Posting Yak Master
214 Posts |
Posted - 2012-02-05 : 18:37:21
|
| The table is big but I am worried that the primary key was set to be a noncluster type I have a lot of other tables referencing this primary key as foreign keyssarah |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-02-06 : 03:13:07
|
| You can set the fill factor to be 100% ... but ... you system may well have been designed with a specific fill factor so that Inserts are faster.Personally (and there has been lots of debate on here, and most people agree, but some do not) I think that Fill Factor of 100% is fine for any table except one which is insert-only and uses a non-sequential primary key. We use 100% Fill Factor on all our secondary indexes (i.e.including ones which are random-insert) provided that the number of reads is considerably more than the number of inserts. |
|
|
|
|
|
|
rebuilding indexes