SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Rebuilding Indexes
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

m_saad
Starting Member

22 Posts

Posted - 03/04/2004 :  13:52:55  Show Profile  Reply with Quote
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?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37316 Posts

Posted - 03/04/2004 :  13:53:45  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

TSQLMan
Posting Yak Master

USA
160 Posts

Posted - 03/04/2004 :  13:55:26  Show Profile  Click to see TSQLMan's MSN Messenger address  Reply with Quote
Thanks for the input Tara.
Go to Top of Page

TSQLMan
Posting Yak Master

USA
160 Posts

Posted - 03/04/2004 :  14:05:49  Show Profile  Click to see TSQLMan's MSN Messenger address  Reply with Quote
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.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37316 Posts

Posted - 03/04/2004 :  14:17:53  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Go to Top of Page

robvolk
Most Valuable Yak

USA
15683 Posts

Posted - 03/04/2004 :  23:28:01  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

TSQLMan
Posting Yak Master

USA
160 Posts

Posted - 03/05/2004 :  09:05:59  Show Profile  Click to see TSQLMan's MSN Messenger address  Reply with Quote
robvolk
Could you elaborate. I understand what you are saying, but I am not sure I know how.

Go to Top of Page

m_saad
Starting Member

22 Posts

Posted - 03/05/2004 :  09:13:16  Show Profile  Reply with Quote
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
Go to Top of Page

MuadDBA
Aged Yak Warrior

USA
628 Posts

Posted - 03/05/2004 :  15:25:03  Show Profile  Reply with Quote
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.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15683 Posts

Posted - 03/05/2004 :  23:47:00  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000