May 31, 2002 at 12:19 pm
I have tried DBCC INDEXDEFRAG with no success. I have tried the suggested syntax that BOL suggests, but I am wondering if this is a command specific to SQL2000 as I am using SQL 7.0. Any help would be great!
EnoSonic
EnoSonic
May 31, 2002 at 12:47 pm
It is specific to SQL2K.
Andy
May 31, 2002 at 2:34 pm
What is the command for SQL 7.0 to defrag the indexes?
EnoSonic
EnoSonic
May 31, 2002 at 2:54 pm
Cant defrag, have to rebuild. DBCC reindex I think.
Andy
May 31, 2002 at 3:00 pm
DBCC DBREINDEX right? I tried that and then ran:
DECLARE @idINT
SELECT @id = object_id('tablename')
DBCC SHOWCONTIG(@id)
GO
But the DBCC DBREINDEX command does not defragment. The fragmentation still exists. Any ideas?
EnoSonic
EnoSonic
May 31, 2002 at 3:23 pm
Did you run DBCC DBREINDEX against all indexes of that table? SOrry I have code on my machine at work that iterates all the indexes and does this but not on hand. But I beleive you have must not have done all the indexes since DBCC SHOWCONTIG with an index parameter looks at all to do it's output.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 31, 2002 at 3:31 pm
I was sure I did, I ran:
DBCC DBREINDEX (<TABLENAME>, '', <FILL_FACTOR>)
It was my understanding that by putting the '' (Placeholder)into the spot where you specify the index, it would include all. Is this incorrect?
EnoSonic
EnoSonic
May 31, 2002 at 8:24 pm
Duh, I knew that, been so long since I wrote the code to do this weekly (thinking of something else sorry). What does your showcontig look like when you run (please post here)? You might also try DBCC UPDATEUSAGE, it might be sysindexes table reporting something incorrectly with regards to the indexes themselves, then try rebuild one more time (grasping here but I think there was a related issue and I have to remember where I saw it).
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 3, 2002 at 7:09 am
RESULTS:
DBCC SHOWCONTIG For table: access_log_item
DBCC SHOWCONTIG scanning 'access_log_item' table...
Table: 'access_log_item' (1746105261); index ID: 0, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 302
- Extents Scanned..............................: 65
- Extent Switches..............................: 64
- Avg. Pages per Extent........................: 4.6
- Scan Density [Best Count:Actual Count].......: 58.46% [38:65]
- Extent Scan Fragmentation ...................: 98.46%
- Avg. Bytes Free per Page.....................: 3421.1
- Avg. Page Density (full).....................: 57.73%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Thanks for your help. I will try DBCC UPDATEUSAGE...then post my results.
EnoSonic
EnoSonic
June 3, 2002 at 7:15 am
No Good...
Ran:
DECLARE @indid int
SELECT @indid = indid
FROM sysindexes
WHERE id = object_id('tablename')
AND name = 'indexname'
DBCC UPDATEUSAGE ('database', 'tablename', @indid)
GO
Results:
DBCC UPDATEUSAGE: sysindexes row updated for table 'access_log_item' (index ID 3):
USED pages: Changed from (73) to (72) pages.
RSVD pages: Changed from (80) to (81) pages.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Urg!
EnoSonic
EnoSonic
June 3, 2002 at 10:17 am
I cannot duplicate this issue. Try dropping and rebuilding each index then check, you may have a corrupt index.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 6, 2002 at 11:24 am
You need to have a Clustered Index on this table for it to do any good at defraging the table.
June 6, 2002 at 12:49 pm
Why do you say that Andrew?
Andy
June 8, 2002 at 2:42 pm
This is my understanding of how dbcc dbreindex works. dbcc dbreindex rebuilds indexes only. For a table with a clustered index, dbcc dbreindex will rebuild the datapages with the fillfactor specified. However, if the table is without a clustered index, only the index pages will be rebuilt not the data pages. Therefore, without clustered index, running dbcc dbreindex is not a true defrag.
I also recall that if the table has no clustered index, certain outputs from showcontig (such as page density?) does not apply.
June 9, 2002 at 12:22 pm
A table without a clustered index is in heap thus any data added is always added to the end and the data never fragments. A clustered index can potentially add records in any previous position thus if you alway insert new records at the end with an IDENTITY field you should also never see fragmentation as no page splits should occurr. If you insert early then the moment a page fills it will page split creating pages with lots of excess room (dead space). So if a page fills and splits with one record then you have increase the number of pages that have to be searched to get to the end. This is the fragmentation that occurrs.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply