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 |
|
Kristen
Test
22859 Posts |
 Posted - 2004-11-28 : 05:01:44
|
I'm running INDEXDEFRAG on a logging table. The table has an IDENTITY PK.Its adding about 1,000,000 rows a day. It contains 7 days data - we delete the oldest day's data each day.Today I disabled the delete.So, yesterday morning we did a DELETE then an INDEXDEFRAG1,000,000(ish) rows were addedAnd now I have run an INDEXDEFRAGThe PK on the table is :ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [PK_MyPK] PRIMARY KEY CLUSTERED ( [MyIdentityColumn]) WITH FILLFACTOR = 90 ON [PRIMARY] It took 90 minutes to defrag, and seems to have shifted everything around, why?The report at the end was:Pages Scanned Pages Moved Pages Removed ------------- ----------- ------------- 509386 508457 0Have I got my fillfactor out of line with what INDEXDEFRAG is going to restuff them as? Why haven't only 1/7th of the pages been moved (I'm thinking yesterday's data at the end of the index/table could be repacked tighter). If they have been filled tighter why are ZERO pages removed?Each INSERT into the log is followed by an UPDATE (to store the elapsed time)And now I have an LDF bigger than the MDF :-(Kristen |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-28 : 14:37:02
|
| Why do you have a fillfactor of 90?If this is a logging table - i.e. insert only (+ delete to purge old data) then there is no need to have any free space as data will never be updated.Never tested it but I assume with this sort of data the rows are added in order of the clustered index so there should never be any data page splits so there will be no need to defrag anything to gain space.I implemented a call rating system in 6.5 where the calls were imported into a table like this and never had to do anything to the table.I'm assuming that your pages affected is due to the defrag having to create the free space on all the pages which wouldn't heve been left with the inserts - in fact I suspect your table would have been better without the defrag as it's probably taking up more space and slower to access now.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-28 : 17:24:47
|
| Just noticed that you did a defrag, delete, defrag.Maybe this left free space on the first page and it has moved everything up. It doesn't affect all pages as the fill factor eventually used up the space for new pages.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-28 : 22:20:30
|
| No idea why the FILLFACTOR = 90 - my best guess is that it was some sort of default on the original create. We don't normally specify it in a CREATE TABLE script.I think I read somewhere that INDEXDEFRAG doesn't reduce pages down to the FILLFACTOR (it only acts upon pages below the FILL FACTOR threshold), but I can't see anything to confirm/deny that in BoL.If I delete 10% of the table, from the lowest Identity keys, will the index pages [now empty] get removed/reused without a defrag?The PK is Clustered - is that a good idea for this type of add-at-end type table?Kristen |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-28 : 22:56:06
|
| It's clustered on a monotonic increasing column so that's a good idea.Given that you are'nt changing the length of any rows then you shouldn't leave any free space.Then as you are adding to the end and deleting from the begining you shouldn't need to defrag or reindex.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-28 : 23:35:24
|
| "Given that you are'nt changing the length of any rows then you shouldn't leave any free space"That may be the problem. Rows are updated to store an "End" date, and a Result code (which can include some varchar(1000) stuff)Perhaps I should log the "End" stuff to a different table, so as not to cause any updates to the "Start" table?Kristen |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-11-29 : 00:17:40
|
| That sounds good. Updates are always a bad thing.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-29 : 00:45:07
|
| I've done a REINDEX and set the FILL FACTOR to 0, we'll see if that improves anything ...Thanks for your helpKristen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-29 : 01:10:47
|
I did this:DBCC DBREINDEX ( 'MyDB.dbo.MyTable' , PK_MyPK , 0 -- fillfactor) which did its stuff, but when I Generate SQL Script I get:ALTER TABLE [dbo].[MyTable] WITH NOCHECK ADD CONSTRAINT [PK_MyPK] PRIMARY KEY CLUSTERED ( [MyIdentityColumn] ) WITH FILLFACTOR = 90 ON [PRIMARY] does ReIndex not change it permanently? or didn't it do the right thing?Kristen |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-29 : 01:13:59
|
BoL says:quote:
When fillfactor is 0, DBCC DBREINDEX uses the original fillfactor specified when the index was created
Darn it!Kristen |
|
|
|
|
|
|
|
|
INDEXDEFRAG working too hard