November 6, 2001 at 2:25 am
Dear
I use SQL Server 2000.
I have index in a table and now i want to delete data in my database. Before i delete data, size of table is 10000k , size of index(price_idx1 = 1000k). When i delete data, size of table is change to 8000k and size of index is not change (1000k).
I want size of index become small, i have try
syntax : dbcc reindex(price) but size of index is change to 2000k.
Someone can help me ?
November 6, 2001 at 4:02 am
You could try this: run dbcc dbreindex with a specific fillfactor (eg 10%) before removing data. Update the statistics and then look at the size of the data and index. Do the same after the data removal and compare the two.
Paul Ibison
email: Paul.Ibison@btinternet.com
Paul Ibison
Paul.Ibison@replicationanswers.com
November 15, 2001 at 8:56 pm
Dear Paul
I've try your idea.
It's work, size of index is change to smaller.
I run dbcc dbreindex without fillfactor,
What different if i use fillfactor or not ??
Thx
Huil0n6
November 16, 2001 at 5:10 am
Have you looked in the bol?
A fill factor defines how much free space should be left in a page. The moer free space the more likely that new data can be added without splitting the page hence inserts/updates will be more efficient but the data will take up more space hence reads will be slower. Therefore a high fill factor is good for static tables but bad for dynamic.
The fill factor only affects the object when it is created - during operation pages are split in half.
Cursors never.
DTS - only when needed and never to control.
November 16, 2001 at 5:22 am
The difference of fillfactor or none depends on the initial create index statement. As your index increased in size (1MB -> 2MB) when you ran DBCC DBREINDEX - no fillfactor, I suspected that the create index statement had a large fillfactor which was being implemented without you realising, and therefore needed overriding.
BOL: "When fillfactor is 0, DBCC DBREINDEX uses the original fillfactor specified when the index was created."
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
November 16, 2001 at 6:52 pm
Thank's Paul and Nigelrivett.
Now i got new problem such as
in one table i use more than one index maybe 2 or 3. So if i want to delete data and than run dbcc dbreindex for each table.
Maybe Paul or Nigelrivett can give me a idea ? Because i use a small program with VB 6 to delete data for each table.
Best Regards
Huil0n6
November 17, 2001 at 7:55 pm
DBCC DBREINDEX(), if no index is specified, will reindex all the indexes for the given table.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
November 18, 2001 at 7:07 pm
Dear Brian
I mean, can i reindex for all table in one database with one syntax ?
If i use dbcc dbreindex(table name),
i must be looping for each table in database.
Best Regards
Huil0n6
November 19, 2001 at 2:43 am
Tthere is not an automatic method I know of to do the whole db, and even if there was a system sp, I guess it would still have to iterate through the tables. I use a (cursor) to loop on the user tables. If you want a simpler method, you could schedule a maintenance plan.
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
November 19, 2001 at 5:46 am
You can just run
select 'dbcc dbreindex(' + name + ')' from sysobjects where xtype = 'U'
(or something like that).
The run the output
Alternatively it's not difficult to write a script to do it.
You could use sp_msforeachtable - but I think it's easier to code that yourself rather than find out what the SP is doing.
something like
create table #a (s varchar(128)
insert #a select name from sysobjects wheer xtype = 'U'
declare @s-2 varchar(128), @cmd varchar(128)
select @s-2 = ''
while @s-2 < (select max(s) from #a)
begin
select @s-2 = min(s) from #a where s > @s-2
select @cmd = 'dbcc dbreindex(' + @s-2 + ')'
exec(@cmd)
end
You could even put this in an admin database and use sp_executesql to run the dbcc in the user database.
Cursors never.
DTS - only when needed and never to control.
November 19, 2001 at 10:10 am
November 19, 2001 at 7:07 pm
It's solve my problem.
Thank's for who has help me.
Best Regards
Huil0n6
November 19, 2001 at 7:50 pm
November 21, 2001 at 7:51 am
There is also a system stored procedure--sp_msforeachtable. The syntax for this is much easier than the code shown above. Another handy one is sp_msforeachdb. This will issue a command in each database, good for dbcc commands.
Let me know if you need more help.
Tom Goltl
November 21, 2001 at 7:07 pm
Dear Tom
Can you tell me more detail about sp_msforeachtable and sp_msforeachdb, and how to use that.
Because i'm a newest in SQL Server.
Best Regards
Huil0n6
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply