January 4, 2013 at 10:50 pm
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.
January 4, 2013 at 11:28 pm
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[/url]
Learn Extended Events
January 4, 2013 at 11:44 pm
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.
January 4, 2013 at 11:52 pm
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[/url]
Learn Extended Events
January 5, 2013 at 12:01 am
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
January 5, 2013 at 1:55 am
First problem
KcV (1/5/2013)
---- Set Recovery Mode SIMPLEBegin
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 5, 2013 at 5:07 am
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" 😉
January 5, 2013 at 10:54 am
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[/url]
Learn Extended Events
January 7, 2013 at 1:50 am
there is no query to see fragmentation.
i used to see it in index --> rebuild or index -- > reorganize options.
January 7, 2013 at 2:08 am
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
🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply