| Author |
Topic |
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
 Posted - 2007-01-01 : 20:58:47
|
| happy new year everyone,i just have a quick question, we're refining our index rebuilds and reorganisation, i was wondering if anyone had any suggestions that may help us decide how often to do these tasks and on which indexes. |
|
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
Posted - 2007-01-01 : 22:01:46
|
| okay im using this criteria:> 5% and < = 30% ALTER INDEX REORGANIZE > 30% ALTER INDEX REBUILD |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-02 : 02:51:19
|
| In 2000-speak if a table's fragmentation is > 10% we REBUILD if the Count of Pages < 10,000, otherwise we Defrag.Rebuild is expensive for large tables because it needs as-much-space-again to do the job, and locks the table during the process. However, I don't know how that translates into SQL2005Kristen |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2007-01-02 : 02:55:58
|
| Does it really lock the table in SQL 2000? or you are talking about cluster index? for non-cluster, don't think it will lock the table. isn't it?and in sql 2005, there is online index rebuild, so no more locking :) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-02 : 04:23:12
|
| "for non-cluster"Not sure, but it "copies" the whole index to a new area of the database/disk, so I can't see how it can allow concurrent updates ... but I suppose if it is copying page-by-page it would only need to lock the current pages - bit tricky when it is coercing multiple pages, or splitting them to get back to the original fill-factor, but still possible I guess - especially as you say there is "online" rebuild in 2005.I imagine the issue still holds regarding the amount of database / disk space required for the Reindex rather than Defrag ...Kristen |
|
|
|
dturner
Starting Member
24 Posts |
Posted - 2007-01-02 : 13:59:54
|
| Can someone recomend a good book on indexing?The world has more information to offer than I can hold in my head |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2007-01-02 : 15:58:39
|
| the best is [url=http://www.amazon.com/Microsoft-Server-Performance-Optimization-Handbook/dp/1555582419]Microsoft-Server-Performance-Optimization-Handbook[/url] by Ken England.Still 100% relevant for sql 2005 |
|
|
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
Posted - 2007-01-02 : 18:19:49
|
| why have u guys decided on those brackets? |
|
|
|
dturner
Starting Member
24 Posts |
Posted - 2007-01-02 : 19:02:07
|
| thanks RussellThe world has more information to offer than I can hold in my head |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-02 : 19:34:32
|
quote:
Originally posted by rnbguy
why have u guys decided on those brackets?
What brackets are you referring to?Tara Kizer |
|
|
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
Posted - 2007-01-02 : 19:48:55
|
| "In 2000-speak if a table's fragmentation is > 10% we REBUILD if the Count of Pages < 10,000, otherwise we Defrag."im just trying to decide which values need to be met before an index is rebuilt or even which values need to be met before the fill factor is changed |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2007-01-02 : 20:15:02
|
| i never index defrag. always dbcc dbreindex on sql2k and alter index rebuild on sql2k5. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
Posted - 2007-01-02 : 22:10:52
|
| sorry i rebuild indexes which are fragmented, but i dont know which range to set |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2007-01-02 : 22:19:00
|
quote:
Originally posted by tkizer
That's a bad thing in 2000.
i wouldnt say its a bad thing...what in that article makes u say so? of course understanding impact while db is in use is important. but rebild clustered idx is hardly going to create any issues (so long as run during maint window)... not sure what part u mean. nevertheless, a good article. |
|
|
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
Posted - 2007-01-02 : 23:49:27
|
| should all indexes have names theres quite a few that are showing up null |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-03 : 03:46:11
|
| "sorry i rebuild indexes which are fragmented, but i dont know which range to set"We defrag/reindex when the fragmentation > 10%. As we are running this every night in a "quiet" period I favour rebuilding everything that could possibly benefit the user, (rather than, for example, doing the minimum to keep the system running smoothly)"but rebild clustered idx is hardly going to create any issues"My knowledge is only based on 2000, but:(so long as run during maint window)... ... well that's one reason!!Reindex increases the size of the MDF file, which it turn makes a restore to a new database take longer (I think this is no longer true under 2005, but not sure). Under 2000 a database with, say, 100MB of "used space" will take MUCH longer to restore if the original database was 1GB rather than 100MB (obviously the physical backup file will be the same-ish in both cases)The increased size may itself be an issue of course (either its "wasting" disk space, which is a minor issue unless disk space has become tight - but bare in mind its likely to become tight at some point in the future! - but around these forums it seems to encourage people to use SHRINK - which wastes all the benefit of the reindex!)Reindex is likely to generate more Log transactions that Defrag. That increases backup load, and log shipping traffic etc.Defrag can be interrupted (i.e. the work-done-so-far is retained). This is unlikely to happen in the real world, but OTOH causing the server to shutdown during a Reindex is going to require the whole lot to be rolled back (presumably on restart of the server - increasing the startup time) whereas a shutdown during the Defrag will just retain the work-done-so-far.The white paper implies that there is no gain in performance for Reindex over Defrag - which is what has caused me to choose the more lightweight Defrag - except for small indexes.Kristen |
|
|
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
Posted - 2007-01-03 : 19:53:31
|
| is it possible to rebuild an index with a null name? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-04 : 02:19:28
|
| I didn't know that you could create an index with a null name!Kristen |
|
|
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
Posted - 2007-01-04 : 02:32:41
|
| when is earch for index information some indexes have a id but the name is NULL |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-04 : 02:38:55
|
| Are they real indexes? (or maybe temporary statistics or somesuch)Kristen |
|
|
|
Next Page
|
index maintenance