fragmentation vs page count

Question

fragmentation vs page count

newbieuser

SSCrazy Eights

Points: 9423
More actions
July 28, 2015 at 7:58 pm

#300892

Hi friends,
I created a thread 2 days back on a performance problem RE non-trusted check constraints and foreign key constraints. We are planning to make them trusted to see if it helps. On the other hand, we see about 50+ clustered/non-clustered indexes with >90% fragmentation but the page counts for all these indexes are in the range between 500-900. I'm reading in the whitepaper that index fragmentation with less than 1000 pages is not a concern. Please give your suggestions if this might cause performance issue as well....
THanks a lot for all your help

Viewing 8 posts - 1 through 8 (of 8 total)

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

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 1

Also would like to mention that I'm reading on user experiences that they have faced performance issues with as less than 100 pages and fragmentation over 70%. So I'm wondering if it is really a myth that small indexes with less than 1000 pages are not a concern or will it be different based on the database environment/activity?

Please give your thoughts/experiences.. THank you so much

WayneS SSC Guru Points: 95461 More actions · Answer 2

newbieuser (7/28/2015)
Hi friends,
I created a thread 2 days back on a performance problem RE non-trusted check constraints and foreign key constraints. We are planning to make them trusted to see if it helps. On the other hand, we see about 50+ clustered/non-clustered indexes with >90% fragmentation but the page counts for all these indexes are in the range between 500-900. I'm reading in the whitepaper that index fragmentation with less than 1000 pages is not a concern. Please give your suggestions if this might cause performance issue as well....
THanks a lot for all your help

This depends upon what your query is doing. If you need to scan all (or part) of the index, then fragmentation will cause more I/O. However, if the query is doing a singleton seek (returning one row), it really won't matter.

If the index is this small, what's the problem in doing a rebuild of the index? It would be done really fast, and then the fragmentation is gone.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

SQLRNNR SSC Guru Points: 281344 More actions · Answer 3

newbieuser (7/28/2015)
I'm reading in the whitepaper that index fragmentation with less than 1000 pages is not a concern.

FWIW - that number has no real meaning.

The person that said it made up the number.

You can read about that from his site - here[/url].

The key take away is to get to know your data and what is actually suitable for your databases.

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 4

Fragmentation affects large range scans from disk. Emphasis 'large' and 'disk'.

It has no effect on data which is already in memory. What fragmentation does is reduce the efficiency of the read-ahead scans, meaning it takes more IO operations to read data off disk. That's why there's a ~1000 page guideline.

As for the stories of fragmentation causing problems on small tables, the question is whether it really was fragmentation or was it just that a rebuild improved performance without the person considering the other things that a rebuild does (update statistics, invalidate all plans)

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 5

Thank you so much for all your inputs... We are looking into other reasons that is causing the slowness..

SQLRNNR SSC Guru Points: 281344 More actions · Answer 6

Most probably a code refactor in several places. 😀

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

ScottPletcher SSC Guru Points: 101200 More actions · Answer 7

A fragmented table that must be scanned will consume more total buffer pages. Several such tables could affect overall SQL performance even if queries on that specific table don't directly suffer that much. And 1,000 pages is definitely, absurdly too high to ignore if it's significantly fragmented.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".