Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Index fregment doesn't come to 0 Expand / Collapse
Author
Message
Posted Friday, January 4, 2013 10:50 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 12:36 AM
Points: 65, Visits: 205
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
Posted Friday, January 4, 2013 11:28 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 6:03 PM
Points: 17,941, Visits: 15,930
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, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1403196
Posted Friday, January 4, 2013 11:44 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 12:36 AM
Points: 65, Visits: 205
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
Posted Friday, January 4, 2013 11:52 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 6:03 PM
Points: 17,941, Visits: 15,930
What is the query you are using to determine fragmentation?



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1403198
Posted Saturday, January 5, 2013 12:01 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 12:36 AM
Points: 65, Visits: 205
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
Posted Saturday, January 5, 2013 1:55 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:01 PM
Points: 40,390, Visits: 36,823
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
Posted Saturday, January 5, 2013 5:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:11 AM
Points: 6,603, Visits: 14,166
KcV (1/4/2013)
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.

can you post the fragmentation results please?


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

"Ya can't make an omelette without breaking just a few eggs"
Post #1403217
Posted Saturday, January 5, 2013 10:54 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 6:03 PM
Points: 17,941, Visits: 15,930
Perry Whittle (1/5/2013)
KcV (1/4/2013)
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.

can you post the fragmentation results please?


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




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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1403261
Posted Monday, January 7, 2013 1:50 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 12:36 AM
Points: 65, Visits: 205
there is no query to see fragmentation.

i used to see it in index --> rebuild or index -- > reorganize options.
Post #1403474
Posted Monday, January 7, 2013 2:08 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 12:36 AM
Points: 65, Visits: 205
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
Post #1403485
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse