Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

There's a storm brewing, or maybe I should say firestorm. A customer has recently implemented SQL Server 2012 Always On but that's not the most important bit.

A list of fires to be put out has been arrived at, and one of them is "incorrect fill factor". Someone in their infinite wisdom had decided to recreate a multi-billion-record table with over 10 indexes without specifying the FILLFACTOR for the indexes during the recreation (showing in sys.indexes as 0%).

I have always though that page splitting is bad, but in real terms, how bad is bad? Is it worse than not having the index at all? Is there a concrete argument for putting out this fire before the others? Note: it will take a significant amount of time for each index rebuild.

FYI - SQL Server 2012 Enterprise Edition

share|improve this question
2  
I'd say if the page splits keep nobody waiting, then they're not a real problem. I'd start by checking the wait statistics. –  Andomar Sep 30 '12 at 23:01
add comment

migrated from stackoverflow.com Oct 19 '12 at 13:23

This question came from our site for professional and enthusiast programmers.

1 Answer

This is only a partial answer to your question, because you don't provide enough information on how the database will be used. I also think this question may be more appropriate on the database administrators site.

For instance, if the data is loaded nightly and the database is only used during the day for querying, then you want the fill factor to be 100%. That will be the most efficient approach for querying, minimizing the number of pages.

Page splitting in indexes occurs when a new record lands on a page that is full. The page is split into two pages, which are then, roughly, 50% full. Once the page is split, there is more space for those values.

If the data is being updated with multiple updates or inserts per second, then page splitting is unnecessary overhead. However, on indexes, this will probably happen the first time a new record lands on an index page.

Note that page splitting is going to happen anyway, once pages are filled. This would happen anyway, regardless of the fill factor. The major question is "start-up" time, because initially basically all inserts/updates will result in page splits. But this should quickly dampen.

The overhead also depends on the characteristics of the implementation. If the indexes fit into memory, or if your disk system is solid-state disk, then the overhead might not be noticeable at all.

My guess is that this is not the most important problem, unless consistency of performance when the new database first goes online is paramount.

share|improve this answer
    
Does billions of records imply a "new database" to you? You may have missed the significance of the question, which is about the impact of page splitting, not about how it works which seems to be the focus of the answer. –  孔夫子 Sep 30 '12 at 23:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.