15.06.2009

Why auto-shrink sucks

The question is simple, why auto-shrink sucks. Before backing up your database, you maybe chose auto-shrink property on, to shrink your database and to empty your transaction log fiile. Auto shrink sucks because to shrink database it damages your database indexes.  To Demonstrate create two tables,  take backup the database  which we create tables in  with auto-shrink property on then look indexes fragmentation percentage. 

Use BlogDb
go
if exists(select * from sys.tables where name = 'tbl_data')
drop table tbl_data
go
create table tbl_data(id int identity, data varchar(255))
go
declare @i int;
set @i = 1;
while (@i < 5000)
begin
insert into
tbl_data values (replicate('a', 255));
set @i = @i + 1;
end;
go

create index IX_dat on tbl_data(data)


alter index all on tbl_data rebuild
go
select avg_fragmentation_in_percent, fragment_count FROM sys.dm_db_index_physical_stats (
db_id ('BlogDb'), object_id ('tbl_data'), NULL, NULL, 'limited');

go




image


DBCC SHRINKDATABASE  (BlogDB)
go
select avg_fragmentation_in_percent, fragment_count FROM sys.dm_db_index_physical_stats (
db_id ('BlogDb'), object_id ('tbl_data'), NULL, NULL, 'limited');





Here is the result. I Think you don’t use auto-shrink after this demonstration.



image

Hiç yorum yok: