| Author |
 Topic  |
|
m_saad
Starting Member
22 Posts |
 Posted - 03/04/2004 : 13:52:55
|
| so that means one should schedule reindexing of indexes in such a way that one day one set of big size indexes get reindexed and another day the second set of huge size indexes get reindexed? kind of load balancing? |
 |
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 03/04/2004 : 13:53:45
|
Well, that's just what we do here. It's up to you to determine what is best for your environment. You might be able to reindex all of them each day. We can't.
Tara |
|
|
|
TSQLMan
Posting Yak Master
USA
160 Posts |
Posted - 03/04/2004 : 13:55:26
|
| Thanks for the input Tara. |
|
|
|
TSQLMan
Posting Yak Master
USA
160 Posts |
Posted - 03/04/2004 : 14:05:49
|
| How do I determine the size of the index. I am sure it is in Books on line, but I must not be looking in the right place. |
|
|
|
tkizer
Almighty SQL Goddess
USA
35007 Posts |
Posted - 03/04/2004 : 14:17:53
|
sp_spaceused
Or
In Enterprise Manager, left click on the data, then right click, go to view, then to taskpad. Then hit the table info tab. Then find your index.
Tara |
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 03/04/2004 : 23:28:01
|
| One thing you might want to consider is putting the nonclustered indexes on a separate filegroup (preferably on a separate physical disk) This is not only a potentially huge performance enhancer but it also makes the files more homogenous/less fragmented, and will greatly improve the reindexing operation. Keeping clustered and nonclustered indexes on the same file/filegroup is more likely to incur page reallocations if the clustered indexes are rebuilt, and will fill the log far more rapidly. |
|
|
|
TSQLMan
Posting Yak Master
USA
160 Posts |
Posted - 03/05/2004 : 09:05:59
|
robvolk
Could you elaborate. I understand what you are saying, but I am not sure I know how.
|
|
|
|
m_saad
Starting Member
22 Posts |
Posted - 03/05/2004 : 09:13:16
|
so will you guys recommend running DBCC INDEXDEFRAG and DBCC REINDEX on alternate basis on each index or just DBCC REINDEX will be enough?
Thanks |
|
|
|
MuadDBA
Aged Yak Warrior
USA
623 Posts |
Posted - 03/05/2004 : 15:25:03
|
| It depends on how your indexes are structured. INDEXDEFRAG is (IMO) a bandage measure that will move stuff around within already allocated pages of your index and according to the existing B-tree structure. DBREINDEX completely rebuilds the index and the B-tree structure and can allocate new pages if it is necessary. If your indexes are very fragmented, INDEXDEFRAG can actually take longer to run that DBREINDEX. |
|
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 03/05/2004 : 23:47:00
|
This should get you started, as an example:
ALTER DATABASE myDB ADD FILEGROUP NC_INDEXES
GO
ALTER DATABASE myDB ADD FILE(NAME=NC_INDEX1, FILENAME='E:\indexes\NC_INDEX1.ndf', SIZE=100MB, MAXSIZE=500MB, FILEGROWTH=50MB) TO FILEGROUP NC_INDEXES
GO
CREATE NONCLUSTERED INDEX ix_FirstName ON myTable(FirstName) ON NC_INDEXES
GO
This will create the new filegroup, add a file to it, and create the ix_FirstName index on the NC_INDEXES filegroup, separate from the PRIMARY filegroup used for the other objects. |
|
|
|
Topic |
|
Rebuilding Indexes
