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 |
|
Westley
Posting Yak Master
229 Posts |
 Posted - 2006-08-25 : 04:24:16
|
| Something I forgot and like to confirm:for non-clustered index rebuild, it will only use the trans log to perform the rebuild and it will not have anything to do with the mdf files?but for clustered index rebuild, it will need to create a clustered index on mdf file before it can drop the old one, so it will use both the tran log and data to perform this right?And as far as i remember, even if the table doesn't have clustered index, to rebuild non-clustered index, it won't use the data file as well since its doesn't need to re-orangise the data as its all in the heap anyway right?getting old, and keep forgetting :) |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-08-26 : 17:00:54
|
| THe mdf is te file containing the data icluding indexes. It holds both the clustered and non clustered indexes.The only difference for a non clustered index with and without a clustered index is that with a clustered index it points to the clustered index entry (has the clustered index key) and without it points to the data page (has the rid).The data rows are part of the clustered index and will be reorganised by rebuilding it. They are pointed to by te non-clustered index and so will not be reorganised by rebuilding it.In both cases entries will be added to the transaction log.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2006-08-27 : 22:17:21
|
| nr, I do understand that, what I'm asking is, with a clustered index, when you do rebuild index, it will need to create the new clustered index before removing the old one, which means the data file will need to increase along with the log, but while doing non-clustered index, it might not? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-01 : 18:20:10
|
| Either way, why not use DEFRAG instead of REINDEX?Kristen |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2006-09-05 : 06:49:34
|
| So you are saying Defrag only use the log, and not going to use mdf at all? defrag do take a bit of log space tho....i remember reindex do need to take space in mdf for cluster, but i forgot for non-clustered...:) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-05 : 07:03:50
|
| No, I'm not saying that DEFRAG will only use the LOG!But REINDEX will make a brand new copy, within MDF, before it throws away the old one. And Log the lot too I assume. Hadn't thought of the situation with a non-clustered index, clearly that is less dramatic that making a brand new Clustered Index which involves moving all the data too, but its still quite a lot of work.The white-paper I read seemed to indicate that:a) Defrag will be less "CPU effort"b) Defrag is in-situ, so less effect on MDFc) Stuff not moved by Defrag obviously won't need Log space (but I presume that Reindex moves the lot, so therefore more log space)d) Tables Defrag'd generally show at least as good performance as Reindex (and sometimes better - maybe because Reindex would have moved the data further "out" on the disk?)and that apart:e) Defrag can be interrupted, so it is worthwhile in scenarios with short maintenance windows (or even where, for whatever reason, you need to kill off the process)Downside is that you need to manually kick of the associated Stats Rebuild, as that doesn't happen automatically, and there are possibly issues with Recompile - although I'm not sure Reindex handles that well enough that it can be ignored if using Rebuild rather than Defrag!.If you haven't already done so its well worth reading MS's white-paper on Reindex v. Defrag.Kristen |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2006-09-06 : 01:37:14
|
| Thanks Kristen, Do you have the link to the white-paper? If you don't thats fine, just being lazy to search :)I do know that Defrag "might" have the same effect with rebuild "sometimes", and I do agree on only do it if it require, and not just do it anyway type of thing, What I'm asking is pretty general as in does rebuild non-cluster index use mdf at all (or as much), since I only remember Clustered index does, as it needs to create the new one before it dump of the old one, but since non-cluster shouldn't, so I'm just thinking will that be different, I remember I read it somewhere before but I can't find where.But yeah, I do use both rebuild and defrag here and there :) |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-07 : 08:56:33
|
| Google forSQL server index defrag "white paper"its the first one in the list |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
|
|
|
|
|
|
|
index rebuild