DBCC INDEXDEFRAG vs. DBCC DBREINDEX
-
Well, I need to learn how to properly 'defrag' a database. I took a copy of our production database, put it on a new SQL Server install (2000 SP4 here), and have been reading BoL til my eyes bleed.
I know this database in question is very fragemented. I ran DBCC showcontig and there are a lot of tables in here that need to be fixed.
Now, im very raw to SQL and have much to learn. I am still learning all the terminology and sometimes, I get confused because it seems like the lingo is almost interwoven. They look and sound the same.
So, wasn't sure what I need to do here and was hoping to get some help. Which of these two do I need to do?
I appreciate it.
BTW, smilies are are awesome!!
-Jason
-
There's no way to quickly tell you everything you probably should take into account with this, but I'll give it a go.
To defrag a table you rebuild or create (if one doesn't exist) the table's clustered index... usually it's created on the primary key column(s). The clustered index determines the sort order of the physical data. Keep in mind that for tables which have a high number of inserts and updates you might need to specify a fillfactor with some room to grow... the same goes true for nonclustered indexes.
Rebuilding indexes can be done with DBCC DBREINDEX in 2000 (in 2005 you should use ALTER INDEX). Defragging indexes can be done with DBCC INDEXDEFRAG (also ALTER INDEX in 2005). In general a defrag is not as complete as a rebuild, however it usually goes much faster and does not require quite as aggressive locks on the underlying data structures. Defragmentation is also fully logged so you may find your transaction logs bloating.
Also keep in mind that index operations can be fairly performance intensive (other than the locking issues). I'd recommend doing any operations like this outside of peak usage times.
That's the short, short, short version.
-
Thanks for the help. Makes sense really, but I just need to keep reading and learning.
Clustered index, 'fill factor'
...I've heard about those, but need to read up on it. 
I have quite a few tables in my test database setup. I have run DBCC SHOWCONTG and am currently going through it. Now, let me ask this. If I decide to DBCC DBREINDEX, would I still need to do a DBCC INDEXDEFRAG?
As im typing this, i'm glancing at BoL. I just typed in DBCC INDEXDEFRAG and was looking at the examples. The synatx seems to be straight forward, but I do have a question. What is 'fillfactor value'?
WHat are some guidelines to determing what tables need to be worked on? Are there some standards on where a table should be before you do dome reindexing?
I appreciate the help.
-Jason
-
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
MohammedU
Microsoft SQL Server MVP -
Very basic information.
1. DBCC DBReindex: locks up the tables and users may not be able to access the data until the reindex is done. Bottom line - this drops the indexes and creates them from scratch. You have brand new indexes when this is done, so they are in the 'best state' possible. Again, it ties up the database tables. This is an all or nothing action. If you stop the process, everything has to rollback.
2. DBCC INDEXDEFRAG: Does not lock up the tables as much. Users can still access the data. The indexes still exist, they are just being 'fixed'. If this is stopped, it doesn't rollback everything. So the index will be less defragged than when you started.
If you run DBReindex, you don't need to run INDEXDEFRAG. There's nothing to defrag when you have brand new indexes.
-SQLBill
-
Thanks SQLBill. That makes perfect sense.
I just need to keep reading up on Indexes.
BTW, how do most people fix their indexes? I am assuming a script? Do you do each table individually, or do them all via a script?
I have quite a few tables in this particular database. Finding the ones that need to be 'fixed' will be a challenge. Fixing them, is even more challenging (because im quite new to SQL.)
-Jason
-
At my last job, I created scripts that ran DBCC INDEXDEFRAG on each table separately. I would run DBCC SHOWCONTIG, then based on that, I would run the necessary scripts.
Where I work now, we do DBCC REINDEX after hours.
One thing to keep in mind....we do transaction replication every 2 minutes. Reindexing can 'block' that and make replication fail. Our solution was to increase the timeout for the replication job.
If you are doing replication:
Go to the server being replicated. In Enterprise Manager, find Replication Monitor. Expand that to find Agents>Distribution Agents. Right click on the Distribution Agent. Select Agent Profiles. Click on View Details. Check the value for -QueryTimeOut. We had to 'play' with that value until we found a timeout that wasn't too short and wasn't too long. DO NOT change the default profile, use the Modify button to make a new one.
-SQLBill
-
When i do a reindex on my sqlserver2000 database . My datbase size is increasing. whether any one know the exact reason. pls do reply me.
-venkat
-
As pointed out in an earlier post, the process of defragmentation is a fully logged operation, irrespective of what recovery model the database in set to.
If performing many defrag operations and/or your tables are very fragmented, then you might consider increasing the frequency of log backups for a FULL or BULK LOGGED recovery models.
Paul
-
Paul thanks for the understanding.
Ldf file will get increased during reindexing but in my setup .mdf file also getting increased.
I like to know the reason why mdf file getting increased exactly.
-
OK. Are you performing DBREINDEX or INDEXDEFRAG? Have you modified the fillfactor? How much has it increased by?
Paul
-
Paul i am doing a dbreindex.
i am using the fillfactor 70
-
I assume you are defragmenting both clustered an non-clustered indexes. When rebuilding the clustered index which forms part of the actual table data, a new table is created and the data is imported from the old table to the new. This process in itself removes fragmentation and once complete the old table is dropped, however the free space remains in the data file from where the old table resided. Also, with all indexes (clustered and non-clustered) additional space can appear due to a low fill factor which will space out data to allow inserts and updates to occur without producing severe fragmentation in comparison to an index with a high fill factor which has little free space between data.
-
Fill factor is the amount of data filled up in the pages. if the table is static then a Fill factor of 100 is perfect, thereby all the pages are filled completely with data and that every read takes lesser IO.
Now the tables that are heavily hit with DML's need a fill factor like 70-80% reason being as below:
if a table is completely filled (100% fill factor) and you are trying to insert one more record:arrow: but that needs an extra page, there by it takes its toll on performance to create the new page for you :ermm: . This process is called Page Splits :w00t: . This also leads to fragmentation. Hence avoid 100% fill factor on heavily modified tables.
What is Page Splits? :crazy:
Hope this helps..:hehe:
Thanks!!
The_SQL_DBA
MCTS"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
-
venkataramanis (1/8/2008)
Paul thanks for the understanding.Ldf file will get increased during reindexing but in my setup .mdf file also getting increased.
I like to know the reason why mdf file getting increased exactly.
Your .Mdf file size can increase cause of many reasons:
1)If u havent specified SORT_IN_TEMPDB option during index rebuilt then your user database will be used for tasks like temporary storage, sorting and other operations used during index recreation. The size of space used is approx twice the size of ur index being built.
2)R u using SQL 2005: If yes R Indexes being rebuilt Online or Offline? If its Online then more resources r used during index recreation.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply