October 2, 2009 at 7:39 am
Hello, i had posted this problem earlier and received one recommendation. Actually my SQL Server 2005 database has some fragmentation and i want to remove that fragmentation. so some one gave me below queries that would remove fragmentation. see below ---
dbcc indexdefrag(databasename,tablename) instead of DBReindex
DBCC UPDATEUSAGE(databasename)
exec sp_updatestats
but after running i could not remove frag, so any other solid idea friends?
October 2, 2009 at 7:46 am
espanolanthony (10/2/2009)
Hello, i had posted this problem earlier and received one recommendation. Actually my SQL Server 2005 database has some fragmentation and i want to remove that fragmentation. so some one gave me below queries that would remove fragmentation. see below ---dbcc indexdefrag(databasename,tablename) instead of DBReindex
DBCC UPDATEUSAGE(databasename)
exec sp_updatestats
but after running i could not remove frag, so any other solid idea friends?
How much fragmentation do you have. if you post the results
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 2, 2009 at 8:02 am
they are > 80%
October 2, 2009 at 8:20 am
espanolanthony (10/2/2009)
they are > 80%
The recommendation from microsoft is that you should rebuild your indexes if they are fragmented more than 30%.
Look at example D from this url. That script will automatically rebuild/reorganize based on fragmentation level.
http://msdn.microsoft.com/en-us/library/ms188917.aspx%5B/url%5D
October 2, 2009 at 8:32 am
How large is the index in question? How many pages?
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
October 2, 2009 at 10:14 am
more than 450 pages atleast
October 2, 2009 at 10:21 am
Please post results of this query for the table in question
select index_id, index_type_desc, avg_fragmentation_in_percent, page_count
from sys.dm_db_index_physical_stats(db_id(), object_id('Table Name'),null, null, 'limited')
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
October 2, 2009 at 12:56 pm
here it is ---
0HEAP 96.7741935483871144
2NONCLUSTERED INDEX85.71428571428577
3NONCLUSTERED INDEX83.33333333333336
0HEAP 94.736842105263277
2NONCLUSTERED INDEX92.307692307692313
6NONCLUSTERED INDEX87.58
17NONCLUSTERED INDEX66.66666666666673
18NONCLUSTERED INDEX754
33NONCLUSTERED INDEX66.66666666666673
0HEAP 66.666666666666716
2NONCLUSTERED INDEX00
3NONCLUSTERED INDEX00
0HEAP 00
2NONCLUSTERED INDEX00
0HEAP 15.40785498489432579
2NONCLUSTERED INDEX99.60317460317461512
3NONCLUSTERED INDEX99.7658079625293427
4NONCLUSTERED INDEX99.9224806201551290
October 2, 2009 at 1:51 pm
Well, that output is for multiple tables (5 to be specific), not just the one that you're talking about.
Can you get the output just for the table that you're interested in (the value in the Object_ID function will need to be changed to the table in question).
What have you run to remove the fragmentation?
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
October 2, 2009 at 2:11 pm
If you have above 100 pages, a non lcustered index(not a heap,casue u cant fix this unless you add a clustered index) frag is above %30 then rebuild that index. if its between 5-30% then a reorg will work. FYI...If you are on standard you cant rebuild on line....you need enterprise edition.
October 2, 2009 at 2:14 pm
0HEAP94.4444444444444178
2NONCLUSTERED INDEX87.58
3NONCLUSTERED INDEX87.58
4NONCLUSTERED INDEX83.33333333333336
5NONCLUSTERED INDEX85.71428571428577
6NONCLUSTERED INDEX85.71428571428577
7NONCLUSTERED INDEX87.58
8NONCLUSTERED INDEX9520
9NONCLUSTERED INDEX9520
10NONCLUSTERED INDEX94.736842105263219
11NONCLUSTERED INDEX94.117647058823517
12NONCLUSTERED INDEX93.7516
that's what i got it form a particular table.
October 2, 2009 at 2:17 pm
ok got it
October 2, 2009 at 2:20 pm
With the exception of the heap (which can't be rebuilt because it's not an index), not one of those is over 20 pages in size. Rebuilding very small indexes is pointless because fragmentation doesn't make a difference when the index size is very small.
The usual size mentioned is 1000 pages. Don't worry about fragmentation for indexes smaller than that. Rebuilding indexes with under 20 pages is completely pointless, because of the way the first few pages are allocated, indexes that small won't have fragmentation reduced by a rebuild.
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
October 4, 2009 at 6:42 am
on SQL 2005 you dont need to run DBCC UPDATEUSAGE - it should be used only on SQL 2000 or 1 time after migrating to the higher version of SQL (2005+)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply