Index fregment doesn't come to 0

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Index fregment doesn't come to 0

Index fregment doesn't come to 0

Rate Topic

Display Mode

Topic Options

Author

Message

KcV

Posted Friday, January 04, 2013 10:50 PM

Valued Member

Group: General Forum Members
Last Login: Friday, May 31, 2013 6:25 AM
Points: 56, Visits: 152

Hi,

I have just shifted my database of sql 2000 to sql 2008 64 bit after running Upgrade adviser.
then i run DBCC DBREINDEX in all tables.

After that when i checked manually then i found that about 80 % tables are well fragmented having 0 logical fragmentation but rest 20 % tables ware having fragmentation of 40 %,50 %,80 %,99 %.

why it is so high ?

after doing manual re-indexing and re-organizing 2 to 5 times , it is not coming to 0 %.

why this is happening ?

pls help.

Post #1403194

SQLRNNR

Posted Friday, January 04, 2013 11:28 PM

SSCoach

Group: General Forum Members
Last Login: Today @ 12:32 AM
Points: 18,855, Visits: 12,439

What is the size of some of these tables? How many pages? Is there a Clustered Index on the tables in question?

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #1403196

KcV

Posted Friday, January 04, 2013 11:44 PM

Valued Member

Group: General Forum Members
Last Login: Friday, May 31, 2013 6:25 AM
Points: 56, Visits: 152

First of all thanks for reply.

What is the size of some of these tables?
- some of tables are having 100 to 2000000 rows

How many pages?
- i don't know about pages. actually i don't know how to see it. where i can find it ?

Is there a Clustered Index on the tables in question?
- Yes, there are clustered indexes and non clustered too.

Post #1403197

SQLRNNR

Posted Friday, January 04, 2013 11:52 PM

SSCoach

Group: General Forum Members
Last Login: Today @ 12:32 AM
Points: 18,855, Visits: 12,439

What is the query you are using to determine fragmentation?

Post #1403198

KcV

Posted Saturday, January 05, 2013 12:01 AM

Valued Member

Group: General Forum Members
Last Login: Friday, May 31, 2013 6:25 AM
Points: 56, Visits: 152

Here sp,

ALTER Procedure [dbo].[DBA_DBCC_DBREINDEX_UPDATESTAT]

As

BEGIN
---- Set Recovery Mode SIMPLE
Begin
ALTER DATABASE aaa
SET RECOVERY SIMPLE;
End

---- Re-Indexing of all tables of Database
Begin
declare @tabName sysname, @schname sysname
--declare cur_index cursor for select s.name, t.Name from Sys.Tables t inner join sys.schemas s on t.schema_id = s.schema_id order by t.Create_Date -- for 2005
declare cur_index cursor for SELECT sysusers.name,sysobjects.name FROM sysobjects INNER JOIN sysusers ON sysobjects.uid = sysusers.uid where sysusers.name='dbo' and xtype ='U' order by sysobjects.crdate -- for 2000

Open cur_index
fetch next from cur_index into @schname, @tabname
while @@Fetch_status = 0
begin
print ('DBCC DBREINDEX (['+ @schname + '.'+ @tabname +'])')
exec('DBCC DBREINDEX (['+ @schname + '.'+ @tabname +'])')
fetch next from cur_index into @schname,@tabname
end
Close cur_index
deallocate cur_index
EXEC sp_updatestats
End

---- Set Recovery Mode FULL

Begin
ALTER DATABASE aaa
SET RECOVERY FULL;
End
END

Post #1403199

GilaMonster

Posted Saturday, January 05, 2013 1:55 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:22 AM
Points: 38,076, Visits: 30,371

First problem

KcV (1/5/2013)

---- Set Recovery Mode SIMPLE
Begin
ALTER DATABASE aaa
SET RECOVERY SIMPLE;
End

Breaking the log chain, preventing any point in time restores until the DB is switched back to full and a full or diff backup is taken, plus means that log backups can't restore over this time. This is not a good idea

---- Re-Indexing of all tables of Database
<nip>

So where do you check fragmentation? Looks like a blanket rebuild of everything, whether it needs rebuilding or not.

May I suggest you replace that with one of these:
http://ola.hallengren.com/Versions.html
http://sqlfool.com/2011/06/index-defrag-script-v4-1/

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1403205

Perry Whittle

Posted Saturday, January 05, 2013 5:07 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:58 PM
Points: 5,242, Visits: 11,259

KcV (1/4/2013)

can you post the fragmentation results please?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" Wink

Post #1403217

SQLRNNR

Posted Saturday, January 05, 2013 10:54 AM

SSCoach

Group: General Forum Members
Last Login: Today @ 12:32 AM
Points: 18,855, Visits: 12,439

Perry Whittle (1/5/2013)

KcV (1/4/2013)

can you post the fragmentation results please?

I'd still like to see the query that is used to get the fragmentation.

Post #1403261

KcV

Posted Monday, January 07, 2013 1:50 AM

Valued Member

Group: General Forum Members
Last Login: Friday, May 31, 2013 6:25 AM
Points: 56, Visits: 152

there is no query to see fragmentation.

i used to see it in index --> rebuild or index -- > reorganize options.

Post #1403474

KcV

Posted Monday, January 07, 2013 2:08 AM

Valued Member

Group: General Forum Members
Last Login: Friday, May 31, 2013 6:25 AM
Points: 56, Visits: 152

thanks man, for giving me such a wonderful code.

I didn't try yet but i think this is the solution of my problem. (in working hours i can't run this sp)

i will let you know the result.

thanks again
Smile

Post #1403485

« Prev Topic | Next Topic »

Permissions