Index Fragmentation and Page count minimum

Question

Index Fragmentation and Page count minimum

Admingod

SSCertifiable

Points: 5997
More actions
August 1, 2013 at 3:21 pm

#297151

I started writing rebuild index script.However, I came across about "SET @page_count_minimum" value .Could you please help me understand what is page_count_minimum and its value to be set etc.
Thanks,

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 1

Hi,

There is page_count column from sys.dm_db_index_physical_stats() dynamic view which tells you how many pages the index has. You can use it for example to rebuild the indexes having more than certain number of pages (e.g. >1000).

Regards,

IgorMi

Igor Micev,My blog: www.igormicev.com

Admingod SSCertifiable Points: 5997 More actions · Answer 2

So the recommended values for reorganize index is page_count_num > 1000 and Index Frag > 30?

Thanks,

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 3

Hi,

The widely used thresholds are: Reorganize if fragmentation is between 10-30%. Rebuild if > 30%.

Indexes with small number of pages (<1000) will usually not loose fragmentation. Try your own experiment, it's funny 🙂

Regards,

IgorMi

Igor Micev,My blog: www.igormicev.com

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

For a reorg it is < 30% and > 10% fragmented.

Pagecount depends on your environment. A common myth was created due to people wanting a specific number so Paul Randal pulled a number out of the air - you can read about that here http://www.sqlskills.com/blogs/paul/where-do-the-books-online-index-fragmentation-thresholds-come-from/.

Each system will have different thresholds for page count and you should test to find what works in your environment the best.

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

IgorMi (8/1/2013)
Hi,
The widely used thresholds are: Reorganize if fragmentation is between 10-30%. Rebuild if > 30%.
Indexes with small number of pages (<1000) will usually not loose fragmentation. Try your own experiment, it's funny 🙂
Regards,
IgorMi

I have seen quite the contrary to that pagecount statement you made. Read my comment about the 1000 page count myth. I have seen quite regularly indexes with 10 pages defrag quite nicely.

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

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 6

SQLRNNR (8/1/2013)
IgorMi (8/1/2013)
Hi,
The widely used thresholds are: Reorganize if fragmentation is between 10-30%. Rebuild if > 30%.
Indexes with small number of pages (<1000) will usually not loose fragmentation. Try your own experiment, it's funny 🙂
Regards,
IgorMi
I have seen quite the contrary to that pagecount statement you made. Read my comment about the 1000 page count myth. I have seen quite regularly indexes with 10 pages defrag quite nicely.

I agree. When I usually rebuild all indexes, after I could still see indexes with 100 pages having fragmentation of 66.67% for e.g. And you can see Paul describes well"

"

if an index has less than 1000 pages and is in memory, don't bother removing fragmentation

if the index has:

less than 5% logical fragmentation, don't do anything

between 5% and 30% logical fragmentation, reorganize it (using DBCC INDEXDEFRAG or ALTER INDEX … REORGANIZE)

more than 30% logical fragmentation, rebuild it (using DBCC DBREINDEX or ALTER INDEX … REBUILD)

These numbers are made up. They can and will vary for you, but they're a good starting point to work from.

"

Thank you for the remark.

IgorMi

Igor Micev,My blog: www.igormicev.com

Admingod SSCertifiable Points: 5997 More actions · Answer 7

We don’t have down time window. Since I have to do online operations only, I cannot opt for Rebuild or Rebuild online(Since it takes more Log space where we have restriction log file size).However, having all these limitation, can I Just Reorganize all indexes having fragmentation >= 30 instead of Rebuild?

Thanks

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 8

I get worried when you say that you are a 24/7 shop and you have a restriction on log-file size. This does not bode well, and you will get hurt some day.

Yes, you can of course do REORGANIZE all day long if you like. Beware that you will need to run UPDATE STATISTICS separately, since reorg does not update statistics like a rebuild does.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

Admingod SSCertifiable Points: 5997 More actions · Answer 9

Admingod

SSCertifiable

Points: 5997

August 2, 2013 at 4:51 pm

#1638380

Thanks for the Information!

Admingod SSCertifiable Points: 5997 More actions · Answer 10

Do we have script where i can populate the Defrag results in a table and run defrag based on condition. For e.g. I dont have window where i can run maintenance plan over the weekend. I want to run for few tables and then following week for another set of tables dynamically based on the condition.

Thanks

Erland Sommarskog SSC-Insane Points: 24038 More actions · Answer 11

For instance

SELECT TOP 5 object_name(object_id)

FROM sys.dm_db_index_physical_stats(db_id(), NULL, NULL, NULL, NULL)

WHERE page_count > 1000

AND index_id = 1

ORDER BY avg_fragmentation_in_percent DESC

5 was just a number I grabbed. I added a filter on page_count, so that you only bother about table of some size. And to keep it simple, I only include clustered indexes.

[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]