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 |
|
mAdMaLuDaWg
Starting Member
42 Posts |
 Posted - 2004-08-18 : 11:04:16
|
| I've been reading up on SQL Server over the past months and I have a question about the DBCC REINDEX. Its formal definition says it allows you to rebuild one or more indexes on a table. And it should be done when you make changes in the data after the index was built?Why does the Index need to be rebuilt after I made changes to the data? What exact changes neccesitate the rebuilding of an index?Another question about Indexes which may/may not be related:Say I have a Primary key that is an identity and I delete some rows, now there are gaps in the column. Does this affect performance in any way? Is there a simple command to close the gaps in the columnns.. something more flexible than DBCC CheckIdent. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-18 : 13:11:06
|
| Indexes get fragmented over time, DBCC DBREINDEX rebuilds the index which defragments it. You can also use DBCC INDEXDEFRAG to defragment. But DBCC DBREINDEX is more powerful, but you can't always use this one as it blocks access to the table until it completes. Only run these when there is very little activity on the system. We run ours in the middle of the night.No there is not a simple command to close the gaps.Tara |
 |
|
|
mAdMaLuDaWg
Starting Member
42 Posts |
Posted - 2004-08-18 : 13:26:12
|
| Ok, when you say that Indexes get fragmented, do you mean the file in which indexes are placed gets fragmented. If so, would a disk defrag fix the problem? I'm just wondering...Also do those gaps that I mentioned effect performance when compared to a table with no gaps.Thanks again for the help |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-18 : 13:29:21
|
| Indexes are inside a database not in a file. But the idea is the same, files getting fragmented on disk and indexes in a database. No a disk defrag would not fix the problem. But you should also run this to optimize the files.The gaps could affect fragmentation which causes performance degradation.Tara |
|
|
|
mAdMaLuDaWg
Starting Member
42 Posts |
Posted - 2004-08-18 : 13:37:20
|
| Right.. actually I meant filegroup.So the reindexing fixes the fragmentation that occurs because of those gaps, and then there won't be any reason to worry about those gaps. So I shouldn't be concerned at all about those gaps as long as I reindex. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-18 : 13:49:40
|
| That's correct. The reindexing doesn't get rid of the gaps though. But reindexing gets rid of the fragmentation.Tara |
|
|
|
|
|
|
n00b questions about Indexes