Rebuild Indexes - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

SQL Server Administration (2012)

Rebuild Indexes

Author

Topic

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-08-06 : 05:14:38

Hey,

I have a Maintanance plan that rebuilds indexes but one thing I notice is that when the rebuild is complete it doesn't lower the avg_fragmentation_in_percent as low as I thought, I see 99% dropping to say 80%...but the page counts have dropped dramatically from say a few thpousand to single digits which is good, can anyone advise on this, is it normal for the percent to be still up and the page counts to be lower and is this a good thing.

I plan to reorganise anything less than 30% and rebuild anything over 30%.

Thanks

Code to check indexes frag I'm using:

SELECT OBJECT_NAME(OBJECT_ID), index_id,index_type_desc,index_level,
avg_fragmentation_in_percent,avg_page_space_used_in_percent,page_count
FROM sys.dm_db_index_physical_stats
(DB_ID('MyDev'), NULL, NULL, NULL , NULL)
ORDER BY avg_fragmentation_in_percent DESC

We are the creators of our own reality!

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-08-06 : 06:19:28

indexes with less page count,their fragmentation level will hardly come down after index rebuild,and thats okay.

Javeed Ahmed

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-08-06 : 06:28:50

Thanks man

We are the creators of our own reality!

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-08-06 : 07:30:13

I've noticed after a rebuild when checking the table indexes directly under the db the percentage is 0 but when I run the script below I still get 99%?

Any ideas why its not coming back as 0%

select dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
from sys.dm_db_index_physical_stats (DB_ID('MyDev'), NULL, NULL, NULL, NULL) as indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes as dbindexes on dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
where indexstats.database_id = DB_ID('MyDev')
order by indexstats.avg_fragmentation_in_percent desc

We are the creators of our own reality!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-06 : 12:33:57

Where are you seeing 0%?

How many pages are in the index that you are referring to? Like mentioned above, ignore the indexes with very few pages, such as 1000.

Also, you may need to use DETAILED for sys.dm_db_index_physical_stats to get the best picture. Check this article for details on the scanning modes: http://msdn.microsoft.com/en-us/library/ms188917.aspx

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-08-06 : 14:40:55

so add detailed in place of the last NULL.

When I right click indexes under a table I can see they are set to 0 but the script above still showing some at 99% 2000 pages or so...will check article

thanks

We are the creators of our own reality!

Shanky
Yak Posting Veteran

84 Posts

Posted - 2014-08-06 : 16:24:57

Hi,

There is exactly no need to rebuild or reorganize any index which has page_count value <1000. You can get this page_count value from sys.dm_db_index_physical_stats. You query should be like

SELECT OBJECT_NAME(OBJECT_ID),
index_id,
index_type_desc,
index_level,
avg_fragmentation_in_percent,
avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats
(DB_ID('AdventureWorks2012'), NULL, NULL, NULL , NULL)
where page_count >1000
ORDER BY avg_fragmentation_in_percent DESC

When index is rebuild it is dropped an recreated for small indexes pages might not be allocated from uniform extent but are allocated from mixed extent this behavior can either cause fragmentation level to be same or sometimes increase. But since index is small it would not matter

Hope this helps

Regards
Shanky
http://social.technet.microsoft.com/wiki/contents/articles/24253.list-of-articles-by-shanky.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources